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

It would depend on a lot of factors I would think. Here are some things I'd like to know:



1. Will there be analysis across companies? (argument for one db or one reporting db)

2. Will uses be granted access based on company or some set of companies? (more on this in a bit)

3. Is there need to share data between companies (i.e. while doing a task in company A, a lookup into company B is needed). If so, argument for one db, if not many would be OK

4. Will uses be spread out geographically, yet grouped geographically by company? I.e. users who access company A are geographically close, while those using company B are far from company A users but close to each other. This would suggest many dbs, physically located near users.

5. Will each company want control of there own data? I.e. could they want to host there own data. (argument for many dbs)



I'll say more on #2. If the users are going to access one db, but need access to some limited number of companies, you'll have to do some work with the RBS to get it to work to secure data at the row level based on the company they are working with. (See my posts, there a lot of them related to this issue). It can be done and it isn't too bad. If you are going to have many dbs, but allow users to access different companies, you'll just switch the DataSource. You might want to consider another database that would hold the mapping of users to company dbs.



Those are my first thoughts in any case.

Good question and good answers!



I'm going through the same debate right now. I'd much prefer to do multiple DB, however, when users do search for specific product or phrases, the program needs have one generated report with sums and breakdown. I find it to be a lot faster to search in one database than, switching through multiple databases. The real problem, will be, more and more databases will be created as time goes. However, multiple DBs give nice freedom to each company.



I hope others with experience in this area share their thoughts too...

..ßen

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.

Excellent!



I feel very confident moving forward with multiples. Especially since one DDT package can deploy to different databases. That is GREAT.



Thank you guys.

Keith Chisarik

Yep, it seems in your situation, multiple would be the better approach. If I were you however, I'd spend some time automating some of the admin, such that 500 dbs will be only a slightly more work to update than 20



I'd suggest that you have a rule that any customization only ADDS to the database, so any updates to the standard schema are always valid in all cases. You could of course ignore existing columns/tables for a customer, but you don't want to get into the situation where you can't update a customer because the standard schema has been customized...that would get ugly very fast.

It just occurred to me that as part of the automation you could write a command line DDT package installer. That way you could use batch/command files to update all dbs at once. You could have the db name as one of the parametersw (package file would likely be a parameter as well). Then you could easily update gagillions of dbs with one script

Trent said: the DDT has the ability to take a single profile and allow you to deploy to a different database name using the same profile package



how might one do this please? and then automate it....



Would I create a command line project as suggested above, how do I change the database name to deploy to?



Thanks.

Keith Chisarik