Implementing Multi-tenant Database Architecture

More
2 years 11 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 to join the conversation.

More
2 years 11 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 to join the conversation.

More
2 years 11 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 to join the conversation.

More
2 years 11 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 to join the conversation.

More
2 years 11 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 to join the conversation.

More
2 years 11 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 to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!