Unanswered: One or multiple databases for apps that share data?
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.