• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Database Design - One Database or Multiple Databases

I'm designing a ASP .NET application where multiple companies will access it.  These companies need to see only thier data and not everyone elses.  Would it be best to create a new database for each company, or create one database with a COMPANYID field in each table?
0
valrog
Asked:
valrog
  • 3
  • 2
  • 2
  • +3
4 Solutions
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
it is difficult to maintain mutiple data base for different client.

so i think you should use one database with companyID .


0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:

if use use single database means you can share many master table which have static data for all client.
0
 
devsolnsCommented:
For this type of design I would not have a different database for each company.  This makes maintenance hard, and schema changes even harder to synchronize.  I would suggest you create a unique id or GUID for each company and reference all of data by that ID.

You could also create a new user (login) for each company and have all references to SUSER_SNAME(), which will return the current logged in user.

Hope this helps.

--DS
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
devsolnsCommented:
A link for the row level security is here:

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

--DS
0
 
ptjcbCommented:
Yes, we are moving from a multiple db setup where all of the database structures are alike to a single db with an id to separate the clients. Trust me, maintaining and trying to keep many databases in sync with exactly the same structure is a pain. Updates have to be applied in a certain order, based on the client, and then some clients will want flexibility. Periodic data updates to the common tables may take a day to do.

Move to one db.
0
 
RichardCorrieCommented:
I have done both;

In the long run having a different DB for each client is an "easier" sell. Clients tend not to want to have their data mixed up with other companies.  In multi-db sceanarios I have created a central DB that holds common tables and system values.

I has also allowed for easier "client" modifications.  It is v. strange fact that no 2 companies (event within the same industry) do their business the same way; they all require little tweaks and enhancements.

/Richard
0
 
valrogAuthor Commented:
RichardCorrie, If I go with your scenario, how many DB can SQL Server 2005 handle at once?
0
 
devsolnsCommented:
SQL Server max databases per instance is 32,767
0
 
Anthony PerkinsCommented:
If you do go with a database for each client, you should invest in a tool such as the following to keep the databases in sync (it will save you time, hair on your head and maybe even your job):
SQL Compare® Standard
Compares and synchronizes SQL Server database schemas
http://www.red-gate.com/products/sql_compare/index.htm

And optionally:
SQL Data Compare™ Standard
Compares and synchronizes SQL database contents
http://www.red-gate.com/products/SQL_Data_Compare/index.htm
0
 
valrogAuthor Commented:
Thanks for the software recommendation acperkins.  It would do me good if I still had much hair.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now