One Database for Applications w/ shared data or multiple?

Question

One Database for Applications w/ shared data or multiple?

Bob Willy

SSC Rookie

Points: 37
More actions
August 4, 2009 at 8:41 pm

#97852

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.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184397 More actions · Answer 1

That's a pretty common dilemma when you have several applications sharing data. I've always like separate DB's per application. Storage is fairly cheap so I have synced common data between applications (using one app updates the common data so I get it from that source).

As far as backups, if you had a different filegroup for each schema then you could do filegroup backups and restores.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Bob Willy SSC Rookie Points: 37 More actions · Answer 2

Are you saying you somehow duplicate the common data into each apps database or that you have a system in place where each app accesses the common data from a single common source?

mohammadabuali Grasshopper Points: 20 More actions · Answer 3

Hi All,

I'm having the same situation, but I wonder if it's possible to make a relation between two tables in two different database schemas? If it's possible, please tell me how to implement it in Sql Server 2005.

Thanks in advance,

Bob Willy SSC Rookie Points: 37 More actions · Answer 4

It's deffinately possible to create relationships between tables in different schemas so long as both tables exist in the same database

This adds a foreign key constraint on the Sales.Order table so that its ModifiedByID field refers to the EmployeeID field of the Com.Employee table:

ALTER TABLE Sales.Order ADD CONSTRAINT FK_Sales_Order_ModifiedByID FOREIGN KEY (ModifiedByID) REFERENCES Com.Employee(EmployeeID)

What you can't do is ask relationships (such as Foreign Keys) to be inforced between tables that exist in different databases. If you could...I'd create different databases all day long, since you can't...I have this dilema 🙁

mohammadabuali Grasshopper Points: 20 More actions · Answer 5

Thanks Bob Willy,

I tried the sample code that you posted but it didn't work.

I created two databases and created one table in each of them. and then I tried the code but it game me the following error:

Msg 4902, Level 16, State 1, Line 2

Cannot find the object "Testing.Actions" because it does not exist or you do not have permissions.

Did I miss something?

I'd also like to ask about the connection string that I should make in this case. I mean, since I have 2 databases now, which of them should I refer to in my connection string?

Thanks a lot

Bob Willy SSC Rookie Points: 37 More actions · Answer 6

You can't create foreign keys accross tables that are not in the same database.

All I was saying is that you could create two Schema's within the same database and then create a foreign key between tables from one schema and another.

Bob Willy SSC Rookie Points: 37 More actions · Answer 7

You can't create foreign keys accross tables that are not in the same database.

All I was saying is that you could create two Schema's within the same database and then create a foreign key between tables on those 2 schemas.