Implementing Multi-tenant Database Architecture

More
3 years 6 months ago #116040 by bkane
bkane created the topic: Implementing Multi-tenant Database Architecture
We are considering using Lime Survey for a project with a very challenging set of conditions.

Hypothetical Use Case:

- Dealership council of an auto manufacturer with over 1,000 dealerships wishes to conduct 10 surveys per dealer on distinct topics: Topic 1, Topic 2, etc.
- Out of security concerns, the council wants each dealer's data to be stored in a separate table (multi-tenant database architecture).

Requirements:

- 10 separate surveys rather than combining all of them into one.
- The argument about Lime Survey using tokens (row level database security) has been proposed and initially rejected.

Design Challenge:

- Is there any way to deploy a multi-tenant architecture in Lime Survey without creating 10,000 surveys? (10 surveys/dealer x 1000 dealers)
- If 10000 surveys must be created, how can the surveys on each topic (e.g. Topic 1) be dynamically managed, where a revision to a question would need to be propagated across 1000 individual surveys?

Please Log in or Create an account to join the conversation.

More
3 years 6 months ago #116049 by holch
holch replied the topic: Implementing Multi-tenant Database Architecture
I don't see how this should work without creating 10.000 surveys, especially as they want the surveys be separated. But let's see what the developers have to say about this.

I also don't see why the security should be higher, just because each dealers data is stored in a separate table (and also for each survey a separate table will be created).

I know it might be a lot of work, but probably with the API you can automate this: Once you have created a survey, you just need to copy it for the others. Just a few ideas.

Nice project!

I'm not a LimeSurvey GmbH member. I answer at the LimeSurvey forum in my spare time. No support via private message.
Some helpful links: Manual (EN) | Question Types | Workarounds

Please Log in or Create an account to join the conversation.

More
3 years 6 months ago #116058 by bkane
bkane replied the topic: Implementing Multi-tenant Database Architecture
Thanks holch for your comments. I will definitely explore the API for automation. Good advice. Hopefully the developers will have some thoughts about this situation.

Please Log in or Create an account to join the conversation.

More
3 years 6 months ago #116067 by tpartner
tpartner replied the topic: Implementing Multi-tenant Database Architecture
I'm with holch - I don't see how security is improved with separate tables unless you intend to allow the dealers access to the data.

Also, post-survey analysis for a given survey would be far easier if all data was in a single table.

Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.

Please Log in or Create an account to join the conversation.

More
3 years 6 months ago #116069 by bkane
bkane replied the topic: Implementing Multi-tenant Database Architecture
Should have been explicit about providing access by dealers to their own data. Sorry for the omission. Your point about post-survey analysis is well taken.

The more I think about this, the less I like the idea of separating tables.

Please Log in or Create an account to join the conversation.

More
3 years 6 months ago #116078 by Mazi
Mazi replied the topic: Implementing Multi-tenant Database Architecture
Creating 10000 surveys doesn't sound like a very good idea :-)

Using less surveys depends on how admin users should be able to view results and export data.


Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)survey-consulting.com'"

Please Log in or Create an account to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!