Unanswered: What's the best way to design same Database for different client
We are implementing a web-based application for a company which owns 3 other company. At the first phase we suppose to deliver the software to just one of the sub company.
Now, I was wondering what is the best way to design the database?
Create just one database and keep all those 3 different company information in same place
Create an instance of the database for each company (in this case 3 different instance of database)
Also, we may need to make reports based on information we have for all those companies.
Note: Each company could have different kind of information than others
I'd say it is not a single "best" solution, as it depends on a few factors.
Does the companies' security policies allow you to store the information in a single table with row based security? In separate schemas in the same database? In separate databases in the same instance? In separate instances only.
As for reporting, the first solution would be the most effective, as it involves less joins (and unions). The least effective would be separate instances. From a security perspective, the first would be considered the least secure, and the security will increase with the later options.
It is more complex to have row based security, than the other solutions.
So, basically it is a trade off between security, performance and complexity.
Thanks for your reply.
we don't know anything about the companies' security policies and assume they didn't know about it either then in this case what's your suggestion?
create one database which keep information about all other companies in same table(es) and just filter based on company name or create a separate database for each company.
Also ,these companies has no relation to each other but in head office they just want to make a report based on those 3 company (e.g. they want to know the total number of a specific asset in all companies)
which method reduce database overload and increase performance?