troubleshooting Question

design question. (multiple companies)

Avatar of tubelight
tubelight asked on
Microsoft SQL ServerFonts Typography
6 Comments1 Solution350 ViewsLast Modified:
I have a database that represents a Company. (tables include inventory, employees etc...)
its MSDE and

I want to modify it so that it should be able to handle multiple companies. Max 15 companies. One client (user of this product) can have multiple companies. There will be multiple clients.
I would move to SQL server

So i am exploring options for modifying the (database) design.

Here is what i came up with.

Option 1
Add a column named CompanyID to each table and make this column a part of the primary Key.
Thus Inevntory table presently has pKey=StockID. In this option1, I would change it to pKey={StockID, CompanyID}.
This means all the joins on all the tables will get one additional join due to this additional pKey.
One database for each client.
comments welcome.

Option 2
Make the primary Key composed of the CompanyID thus the pKey will be SMAC-0001 where first 4 charactes 'SMAC' represent the company. All the tables will have such a pKey. Whenever required we can seperate the first 4 chars and get to the companyID. This means all the pKeys have to be char based and can not use numbers which might have been faster. but we are reducing on extra column and one extra join so i would think its better than option1.
One database for each client.
comments welcome.

Option 3
Don't change the existing db design.
Instead, create a seperate db for each company.
Then have a master db that will hold info about each company.
This might sound not so good option but if i think about it i seem to like it more. One can manage the client information (which will include client's companies info) in the master db. When the cilent connects to the db using the app, he will first query the master db which will hold pointers to the databases that he is assigned to.
It will be more efficiant since number of hits on each database will be reduced.
Drawback - too many databases. if i am using sql server or mysql how wild does this option sound.
Also it won't be easy to compare information across two or more companies. i.e. run a report that will find total inventory items in all the companies.
comments welcome.

Even if you suggest option 3 is better, please comment which one of opt1 and opt2 you find better.

Any other options ?

thx n regards

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros