Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jade
Starting Member
1 Post |
 Posted - 2009-08-04 : 23:22:00
|
| I write a number of applications for my company and thus far what I have done is create a single database that has one Schema (Com) that holds common tables/procedures that almost all apps need. (Employee info, tables related to authorization/authentication, etc).Then I've created a schema for each application that houses tables and procedures specific to that app.I like this approach because I have no data duplication and strong referential Integrity. Many apps have tables that refer to the Employee table for instance and because they are in the same DB I can use Foreign Keys to ensure things are on the up and up. Prior to my arrival every app had its own database but therte was some duplication and refferential integrety was not inforced.But .... I'm having trouble deciding if my approach is really the best or if there is a hybrid way that may be better. My main concern w/ what I am doing now is scalability. The DB is small now (About 6 MB but is young will certainly grow) w/ a potential for ~100 users.What if the server it is on becomes too overworked and I need to split apps between servers. That's impossible w/ my design I think?Also if I only wanted to backup certain information to work w/ I can't. Or is there a way to select only certain Schema's to backup/restore?Anyway is there a better way? Should I make a separate DB for each app even though they will share the employee, security, and a couple other common tables? If so, how would you reccomend dealing w/ referential integrity. I'd greatly appreciate any ideas or experiences from people who have gone thru this.Thanks for any help. |
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-03 : 09:03:29
|
| Wow - what a kettle of fish :) In a simple answer, "Yes, you messed up and all of those fears/problems are spot on." My advice? Split it while you can. Once the database (or one particular app) becomes too big/popular, it will be hell. And everyone will know that it was your design that caused the company {overtime pay to fix | hiring consultants to fix | downtime due to migration}.I don't mean to sound harsh or come down on you - not my intent at all. You didn't do anything wrong knowingly at the time - you probably thought it was a clever approach, right? Nothing wrong with that at all - and nothing to be ashamed or embarrassed of. But now you've grown and you see the potential pitfalls with such an approach so let's get to fixing it:1) Cross database referential integrity in SQL Server is achieved via triggers and is implemented fairly easily (albeit tediously)2) Suggest you have one database of common stuff and use three-part qualification to address it:SELECT LIF_Utility.dbo.GetStartOfDay(c.AddDate) FROM Courses cIn the above, the utility database is "LIF_Utility" and I use it to store a scalar function used by many dbs.3) Should you have "one database per app"? No clue - don't have enough info. It's possible that having everything in one database is fine. Generally speaking, most databases have multiple apps that use them. 4) Size worries - what is a "big database" to you? You say you have a 6MB database now and it will grow. It likely has to grow to 100GB - 400GB on modern servers before it becomes "challenging" to work on.Hope this helps - sorry my reply was so late.========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx |
 |
|
|
|
|
|
|
|
One or Multiple databases per app w/ shared data