By Keith Chisarik - 7/3/2007
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?
|
By Greg McGuffey - 7/3/2007
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.
|
By Ben Hayat - 7/3/2007
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...
|
By Keith Chisarik - 7/3/2007
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 !!!
|
By Ivan George Borges - 7/3/2007
Well, it looks like you have 4 reasons for many databases, and 2 for having only 1.
|
By Trent L. Taylor - 7/3/2007
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.
|
By Keith Chisarik - 7/3/2007
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.
|
By Greg McGuffey - 7/5/2007
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.
|
By Greg McGuffey - 7/5/2007
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
|
By Keith Chisarik - 7/5/2007
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.
|
By Trent L. Taylor - 7/5/2007
You will just do this at deployment time through the DatabaseMigrator class. You can see a sample of this in the sample titled "Database Installer" through the sample console. Below is some code showing how to use a different database name while using the same package file. Be sure that your data file in your file group uses the $DbName$ tag within the DDT itself so it can create a proper file name for each database on the server....otherwise you will get an error because the file name for the file group is already in use.Dim loDatabase As New DatabaseTargetName("MyProfileDBName")
loDatabase.NameOnServer = "MyProfileDBName_One"
_DatabaseSetup.DeployMetaData(PackageFilePathandFileName, New DatabaseTargetName() {loDatabase})
loDatabase.NameOnServer = "MyProfileDBName_Two"
_DatabaseSetup.DeployMetaData(PackageFilePathandFileName, New DatabaseTargetName() {loDatabase})
loDatabase.NameOnServer = "MyProfileDBName_Three"
_DatabaseSetup.DeployMetaData(PackageFilePathandFileName, New DatabaseTargetName() {loDatabase})
|
By Greg McGuffey - 7/5/2007
So what might be cool is to do this in a console app, and pass in the db name
' Main sub of a deployment console app named 'DeployDb'
Sub Main(args() As String)
'-- First argument is package path and second is database name
Dim loPackageFile As String = args(0)
Dim loDataBaseName As String = args(1)
Dim loDatabase As New DatabaseTargetName(loDataBaseName )
loDatabase.NameOnServer = "MyProfileDBName_One"
_DatabaseSetup.DeployMetaData(loPackageFile , New DatabaseTargetName() {loDatabase})
End Sub
Then you could create a batch file to update all the databases easily, even build it into the build script in VS.
:: In batch file
DeployDb "c:\data\myPackage.pkg","companyA_DbName"
DeployDb "c:\data\myPackage.pkg","companyB_DbName"
DeployDb "c:\data\myPackage.pkg","companyC_DbName"
|
By Ben Hayat - 7/5/2007
Greg McGuffey (07/05/2007)
So what might be cool is to do this in a console app, and pass in the db name
' Main sub of a deployment console app named 'DeployDb'
Sub Main(args() As String)
'-- First argument is package path and second is database name
Dim loPackageFile As String = args(0)
Dim loDataBaseName As String = args(1)
Dim loDatabase As New DatabaseTargetName(loDataBaseName )
loDatabase.NameOnServer = "MyProfileDBName_One"
_DatabaseSetup.DeployMetaData(loPackageFile , New DatabaseTargetName() {loDatabase})
End Sub
Then you could create a batch file to update all the databases easily, even build it into the build script in VS.
:: In batch file
DeployDb "c:\data\myPackage.pkg","companyA_DbName"
DeployDb "c:\data\myPackage.pkg","companyB_DbName"
DeployDb "c:\data\myPackage.pkg","companyC_DbName"
Greg, I think you just earned your "Advance SF User" title! 
|
By Keith Chisarik - 7/6/2007
where would you tell it what SQL server/security information to use?
|
By Keith Chisarik - 7/6/2007
found it.....
|
By Trent L. Taylor - 7/6/2007
When you create the DatabaseMigrator class you provide the SQL server and authentication information._DatabaseSetup = New DatabaseMigrator(_SQLServer, False, Me, _SQLUserName, _SQLPassword)
|
By Trent L. Taylor - 7/6/2007
Keith....I'm guessing you saw Transformers Great movie!!!
|