Solved

design question. (multiple companies)

Posted on 2004-03-24
6
295 Views
Last Modified: 2013-12-03
I have a database that represents a Company. (tables include inventory, employees etc...)
its MSDE and ASP.net

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

0
Comment
Question by:tubelight
6 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 10670033
I would not do option 2, but do option 1 if you do modify the DB to alow multiple hosps.  I would create a company table, with a smallint ID, and use that smallint id on the other tables.  You will also want to only add it to the other tables where required.  Depending on the DB's design, you may not need it on all tables, but just those required to identify the data in related tables.

While option 3 is doable, your going to spend the same amount of time developing means to make it look as if all the data was in 1 database.  Also, the performance should not be that much of an issue, because it sounds as if these where all going to reside on the same server anyway.
0
 
LVL 2

Expert Comment

by:joefunsmith
ID: 10671986
I like a variation on option 1 - add the company_ID (you wouldn't necessarily need it to be part of the primary key) then add views for each table.  lets say you have an ORDERS table which has an ORDER_ID PK column and an ORDER_DESC column.  

Add a view with

select      ORDER_ID, ORDER_DESC
from         ORDERS
where     (COMPANY_ID in (select COMPANY_ID from USERCOMPANY where USER_ID = system_user))

as the select statement.  The USERCOMPANY table would contain just user id's and company id's.  This way, if you wanted someone to have access to all companies or just 2 companies, you could manage the one table for permissions.  There are many different variations on this scheme.  This is just one way.  If you then only give the users access to views and not the tables directly, you strengthen security.  Of course this means changing all your code to look at the view instead of the table, but it might be worth the initial work.
0
 
LVL 2

Expert Comment

by:dhenson
ID: 10672736
Just in case you need more reinforcement.....

option 1 has success written all over it.

Our current student records system works this way to allow one central office the ability to manage multiple districts.  It is a trivial thing to modify your queries to work in this environment.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:tubelight
ID: 10673134
thx guys

i see opt 1 is opted. I have seen it used in past too.
adwiseman - true i dont have to have it in all the tables.
joefunsmith - true i wont make it a pkey and views is good idea.

but ... :)
i can not stop wondering what are downfalls of opt 2.
It reduces one column and one join that otherwise seeps in almost all the database tables. only prob i see is that it prevents you from using numeric pkey. perhaps that hurts.

everyone using one database is going to be more intensive on sql engine compared to same number accessing different databases. isn;t that correct ?
curious: how many dbs can a sql server have ?

dhenson - i wish someone had enorsed that 2 or 3 is better :)

i understand you all are speaking off experience. i sometimes indulge myself in questions like this.
regds
0
 
LVL 2

Accepted Solution

by:
joefunsmith earned 125 total points
ID: 10679907
The biggest problem I see with option 2 is implementation... You no doubt have numeric or int as your PK data type right now with identity on.  In order to add non-numeric data, you need to change your datatype then add your first company's prefix to the existing records which would mean you would first have to remove your relationship constraints and possible other constraints and/or triggers while you go about modifying your data, then add the whatever objects your removed, back into the database.  Of less impact, you would have to create a function to create your primary key instead of relying on the built-in functionality of the identity feature.  

If you wanted to create flexible views as I describe above, I think it would be harder to do with the company being only a component of the value in the column.  If you restricted your results to one company or all companies I don't see a problem because you could add a LIKE clause to the view (like 'SMAC%') but I can't off the top of my head see how you could see maybe 2 out of 5 companies only.  I think it would make reporting harder too since you couldn't use the built-in SQL functions to do statistical comparisons as easily.  For instance, in order to do a GROUP BY, you would have to create a view first or rely on additional logic.

Certainly option 2  is better than option 3 in my opinion.  With 3 you would have to perform every update x times where x is the number of companies plus getting consolidated queries would be more trouble.  Add to that x times the backups.  If you had separate databases, how would you handle coding the front-end connection string?  There are ways, but why go thru the hassle?  In addition to the usability, you save server resources.
0
 

Author Comment

by:tubelight
ID: 10683959
thx joe for being patient n helpful, its yours.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server stored proc 2 13
Sql Join Problem 2 33
sql query questions 2 25
Adding Subtracting values in sql server 5 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now