Database per application VS One big database for all applications [closed]

Question

I'm designing a few applications that will share 2 or 3 database tables and all of the other tables will be independent of each app. The shared databases contain mostly user information, and there might occur the case where other tables need to be shared, but that's my instinct speaking.

I'm leaning over the one database for all applications solution because I want to have referential integrity, and I won't have to keep the same information up to date in each of the databases, but I'm probably going to end with a database of 100+ tables where only groups of ten tables will have related information.

The database per application approach helps me keep everything more organized, but I don't know a way to keep the related tables in all databases up to date.

So, the basic question is: which of both approaches do you recommend?

Thanks,

Jorge Vargas.

Edit 1:

When I talk about not being able to have referential integrity, it's because there's no way to have foreign keys in tables when those tables are in different databases, and at least one of the tables per application will need a foreign key to one of the shared tables.

Edit 2:

Links to related questions:

• SQL design around lack of cross-database foreign key references
• Keeping referential integrity across multiple databases
• How to salvage referential integrity with mutiple databases

Only the second one has an accepted answer. Still haven't decided what to do.

Answer:

I've decided to go with a database per application with cross-database references to a shared database, adding views to each database mimicking the tables in the shared database, and using NHibernate as my ORM. As the membership system I'll be using the asp.net one.

I'll also use triggers and logical deletes to try and keep to a minimum the number of ID's I'll have flying around livin' la vida loca without a parent. The development effort needed to keep databases synced is too much and the payoff is too little (as you all have pointed out). So, I'd rather fight my way through orphaned records.

Since using an ORM and Views was first suggested by svinto, he gets the correct answer.

Thanks to all for helping me out with this tough decision.

Answer 1

Neither way looks ideal

I think you should consider not making references in database layer for cross-application relations, and make them in application layer. That would allow you to split it to one database per app.

I'm working on one app with 100+ tables. I have them in one database, and are separated by prefixes - each table has prefix for module it belongs to. Then i have built a layer on top of database functions to use this custom groups. I'm also building data administrator, which takes advantage of this table groups and makes editing data very easy.