General - One or multiple databases for multiple 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.

Thanks for any help.

It sounds like your database is not getting hit very hard, so you probably don't need to worry about it too much. You can always scale the hardware if you need to.

If it does get to the point that a single server (or cluster of servers) can't handle the load, You can do something like this:

1. Separate the distinct application databases to their own servers

2. create a single server for the common structures.

3. Use replication or SSIS to push changes to the common database out to the application databases.

4. Any changes to the common database should occur in the main one, not the copies--that way there is only one source for the data and the rest can be wiped and replaced if they get out of sync.

Fairly complex, but if you can't get acceptable performance any other way, it may be worth it.