So this is more of a theoretical question. I am fairly new to databases, but here is my issue. I have a website which has users which will be divided up into different organizations. These organizations have nothing to do with each other, would I want to store them all in separate databases? different tables? or store them in the same databases and tables with columns to specify which organization they are a part of? Currently using SQLite but willing to move.
>Solution :
You basically have two options.
1#:
Hold everthing in one database, add additional fields to your tables and some logic to deceide who has access to what. Cons: That will be a mess, Pros: you will have less issues when transferring data from one organization to another.
2#:
Create a fresh new database for each organization. Cons: More data transfer needed when transferring data between organizations, Pros: Far less logic and data mess. 🙂
I would prefer the second option. But there is a lot of software out there which was build with the first option in mind.