Hypothetical:



You are designing a product from the ground up. In the end you will have data for 20 different companies in said product. Do you have one database and add a customer ID to each table, or do you have 20 different databases?



I can see arguements for each scenario....



Opinions?




Keith Chisarik

Thanks for the input. In my situation, company A has nothing to do with company B, no data will ever need to be shared. They are in no way related. The databases will all (one or many) reside on our application server and will only be accessed via an ASP.NET application. There will be no components off the centralized server.



The reasons I like the idea of having a DB for each customer:

1) security will be easier

2) the db will be smaller as will all queries and load times

3) customizations will be easier (if needed down the road)

4) "one database to rule them all scares me". I would rather have one pissed off customer if something goes wrong



I like the idea of a single database because:

1) schema update will be A LOT easier, unless there is an easy way I don't know to apply a DDT schema against X # of databases. Can this be scripted in some way?

2) We plan to have about 20 company DB's, but looking ahead I can see that if I have the need for more then administration become an issue (nice problem to have I guess = too many customers )





I appreciate all your input, this is a great community. Happy 4th !!!

Keith Chisarik

Well, it looks like you have 4 reasons for many databases, and 2 for having only 1.

In our medical software we have the ability to support multiple instances which is ultimately a different database for each.  Starting with 1.6, the DDT had the ability to take a single profile and allow you to deploy to a different database name using the same profile package.  This is how we support multiple "comapnies" on the same server.  If you have the need to allow reports, etc. to have all of the information included for all companies, then you may want to use the same database...which we too have this need for some things and in these cases we have a company PK field in every table which is a separation....but this complicates every query you write because you ALWAYS have to take this into account.

In my opinion, the multiple databases is the cleanest and easiest to maintain.  But as some of the other guys have mentioned as well, it really depends on your needs.

One thing to keep in mind is that it is always easier to merge databases versus break them apart.  So I think the least number of risks are with the multiple databases.