Database structure for handling multiple accounts (Multi-Tenant Data Architecture)?

Question

It's always easy to write software that handles a single entity. However, designing software to handle multiple accounts/companies requires due diligence.

I'm currently doing initial research into database schemas that handle multiple accounts/companies, and I am asking if you can share a web page or open source software that demonstrates proper schema?

Edit: I was able to find the appropriate terminology for this, which is Multi-Tenant Data Architecture. A helpful Microsoft article describing the approach: http://msdn.microsoft.com/en-us/library/aa479086.aspx

Thanks.

Answer 1

This is only slightly more complicated than a "single entity". Let's assume the worst case - multiple companies that each have multiple accounts. One table for companies. One table for accounts with a foreign key of the Company ID. Then each transaction has the foreign key of the Account ID. For all other data you need to decide if it belongs to one Company, one Account, or some other. I.e. employees can only work for one Company (generally speaking), so the Employee has the Company ID as foreign key. A Division would be in one company, same relationship.

It's all about understanding what belongs to who and establishing the relationship. That may sound awfully simplistic, but that's really about it.

Looking to use some established schema may save you a lot of effort, but that effort is what insures a good result. A good database design comes from asking ALL the questions - "Can an employee work for more than one division?" "Are purchases always charged to one department or can the cost be distributed?" "Do we need to keep track of more than one approval for an employee reassigment?" It goes on forever, but if you don't do it your system will be constantly disappointing the users and require endless modification.