| Author |
Topic |
|
slacker
Posting Yak Master
115 Posts |
 Posted - 2003-03-28 : 17:31:09
|
| My boss and I are having a debate on how to setup the database. He wants to setup every application we have or ever will offer into one database... Does anybody have any experience with this. If you had say 15 completely different web applications would you cram all the tables into one database or seperate each application into its own database. His argument is like "well thats how quickbooks did it". We are an asp so our clients will share the data for each application.. and the data layer of my application uses the client id to determine which records to return. Its bad enough we are dealing with multiple clients I think we should at least seperate an application into its own database. The problems I see arising are naming conflicts and application stored procedure conflicts. all being in the same database.. I guess i could prefix the stored proc names with the name of the application but this just gives me a rash. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-28 : 17:41:34
|
| You need to be adamant about this. Every application (for the most part at least - related applications could be in one database) should have its own database. You should not design your system based upon how quickbooks did it. Since he said well thats how quickbooks did it, I'm going to go out on a limb and say that he's not a DBA. Whoever the DBA is needs to make this decision and be firm about it. You are going to wind up with too many objects in one database if you use it for every application. And besides, that's just not the way to do things. You could use this as an example (no matter how lame of an example it is):You don't put all of your files directly off the C drive, you put them in folders that are named accordingly. Who could manage the file system if everything was stored directly off the root? I couldn't, that's for sure.Tara |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-28 : 17:42:48
|
| This thread should probably be moved to the Administration forum.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-28 : 21:01:51
|
| depends if the applications use related data.If you put linked data into different databases then if you need to restore a database you will have to have some method of re-synchronising the data or always restore from the backups taken when the system is off-line.If you put unrelated application data in the same database then if you have a problem with one application (e.g. need a restore due to an application problem) then it will also affect the other application.Splitting data across databases will also cause permission issues.I worked on a system where each application had it's own database but all the data was in a central database and accessed by views.The excuse given was that they could change databases by changing the views - fortunately this was workable although an administrative nightmare, unlike a lot of other things they tried to implement.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-28 : 21:35:20
|
| If you are an ASP offering the same application to 15 different clients, I would put them in the one database. Makes maintainance MUCH easier.If they are 15 totally separate applications, then splitting them out makes more sense.Damian |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-03-29 : 00:14:41
|
| Yeah merkin thats pretty much the situation. We have some domain logic that is common to all apps. Such as a login process. This is stored in the main database. He wants to somehow combine the entities of the applications. For instance if a shopping cart required an addresses table and so did another. If you had a well defined address table both applications could share it by both using a many to many table. I think its a great idea.. But unrealistic from my standpoint as the developer. I know that without careful planning of the whole database up front... additions and modifications will end up requiring the entities requirements to change. What I want to do is try to seperate the logic as much as possible for each application. So that if the requirements for one application changes it wont effect the other. |
|
|
|
Earthlinker
Starting Member
3 Posts |
Posted - 2003-03-29 : 01:35:43
|
quote:
My boss and I are having a debate on how to setup the database. He wants to setup every application we have or ever will offer into one database... Does anybody have any experience with this. If you had say 15 completely different web applications would you cram all the tables into one database or seperate each application into its own database. His argument is like "well thats how quickbooks did it". We are an asp so our clients will share the data for each application.. and the data layer of my application uses the client id to determine which records to return. Its bad enough we are dealing with multiple clients I think we should at least seperate an application into its own database. The problems I see arising are naming conflicts and application stored procedure conflicts. all being in the same database.. I guess i could prefix the stored proc names with the name of the application but this just gives me a rash.
JJS |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-29 : 10:18:55
|
quote:
His argument is like "well thats how quickbooks did it".
Maybe you should tell him that your customers should just use Quickbooks instead of your product. That might shut him up really quickly.  Seriously though, I'm not saying to use multiple DB's as a marketing angle, but the point is that you're providing something that Quickbooks DOESN'T (I would hope), so it makes no sense simply to duplicate what Quickbooks does anyway.Here is, IMHO, a REALLY DUMB idea, but yet somehow has some possible appeal, and that is to keep all the common data in one DB but have separate DB's for app-specific data like codes, and all the views and sprocs too. The permissions issue is somewhat moot since everyone would have access to the main DB anyway, but you can wall off certain apps if they don't use them. Referential integrity is no more of a bitch than it would be with multiple DB's with data split amongst them, except that you can now limit how much data you need to spread out. If you need to have flexibility in backups and restores, as Nigel mentioned, you can split up the app tables onto different filegroups within the one common DB and do filegroup backups/restores. This can also be a huge timesaver as you get more clients and more data. |
|
|
|
|
Annoyed.