How to handle tables that are needed in 2 or 3 databases?

Question

My situation is as follows

I have a sql-server running, which holds a few databases.
The physical size of the largest database is about 2 GB, it has 343 tables, 129 views and some stored procs and functions

Now we have started development of a new application, that uses it's own database on that same server, but a few tables should be "shared" by both databases.

In a few months the development of yet another new application will start, and that will also use these few "shared" tables.

What is the best method to do this,

1. do I keep the "shared" tables in the original database and let the
   other applications (and databases) just use them ?
2. do I make a copy of the "shared" tables on the other databases, and somehow keep them in sync ?
3. do I make another database that holds only the "shared" tables ?

The "shared" tables can off course have foreign keys to other tables, and they can be referenced by other tables in their foreign keys.
This only makes things more complicated
Also our applications/databases are very dynamic, I get to make changes in tables at least 1 or 2 times a month

I would like some advise in this matter

Option 1 looks like the most easy method, but it makes the applications/database not stand-alone. At this time this is not a problem, but I don't know what the future brings off course.

Option 2 seems like a nice solution, but I am afraid that keeping the tables in sync will be not so easy. And off course any changes to one table can be more difficult

Option 3 seems difficult because of all the relations of the tables, would this mean that all tables that are referred must also be in this shared database, even if only one application/database needs them ?

What is the preferred method for doing this ?
What are my options and what are the advantages/disadvantages of each option ?
Is there an option 4 ?

EDIT

After reading the answer and the comment, and discussing it with the development team, we think we are going for option 1 for the following reasons

• The application has to connect with 2 databases, but that is also to the case in option 3.
• FK relations and other referential integrity will not be a problem, as opposed to option 3
• We don't have a problem with "vertical slicing", adding columns to a table specific for one application is not a problem for us

If I am wrong with this, or if there are other things to consider that I have not seen I will welcome all comments and opinions.

EDIT 2

As requested in the comments, all the applications will be able to read and write in the shared tables.
And it is possible that an application can start a transaction, update more than one table including a shared table, and then commit/rollback

EDIT 3

To make things clear and to respond on question in answers/comments.

The idea is to have 2 or 3 application use the same shared tables.

For example, in application_1 I have a table called tblRelation, which holds customers, loadingplaces, fuell stations, etc...

In application_2 I have a different database, but it needs the exact same data in table tblRelation

So, table tblRelation is for me a "shared table", I have now 2 applications that can read and write into this table.

Both applications are Corporate wide Enterprise Applications which handle different actions, have mostly different data, but do share the corporate data, like tblRelation

Answer 1

You haven't told us whether the shared data should be read-only for the other databases or read/write for all. This point is fundamental if it is necessary to write in this shared database, are the updates in this database related to explicit transactions or not ???

Indeed, the most suitable solution will depend on your answer!

In other terms, are the data from a target and the shared database dependant or not, at only the read or also the write level ?

@GuidoG replies : All databases/applications will be able to read/write

But you do not answer to the second part : do you need explicit transactions between a target database and the shared one, like :

BEGIN TRANSACTION
INSERT INTO shared.schema_name.object_name ...
UPDATE target.schema_name.object_name ...
COMMIT

And are the writes into the shared database be visible by any traget database ?

---

Some more answer

YES it is possible to manage FOREIGN KEY between different database but not by the declarative way, but by the bias of triggers... SQL Server have special features to manage this coding with some instance level options like "nested triggers". But one very important thing to know is that it is impossible to garantee that the integrity of cross databases will be effective when restoring due to the fact that BACKUP process keep the database at the state of data that the tables will have to the end of the backup process. And because, there is no possibility to have all the database backups finishing at the same moment, when restoring you will face a dilemna that is :

• either keeping strict integrity across all database by restoring with the same value for STOPAT (PITR recovery) forall databases and then loose all the data inserted after
• or restoring all the databases till the end, without any garantee that the cross data references are kept

So I would prefer another way :

Having a shared database for INSERT/UPDATE/DELETE only with a replication in all other database as soon as possible (I suggest to use Service Broker for that purpose) made into a special SQL schema, so the databases will have a great autonomous... and backup restore process won't be hurt and simplifies !

Answer 2

This is all opinion/preference. Here's mine:

Option 3 would be my preference IF your business logic in the application can handle maintaining the FK relationships. Your shared tables/data will live in one place/database, then there's no duplication of data nor extra traffic from replication of data that might change. It decreases chances of something going wrong. You just need to make sure that the code is solid and will handle proper creation/deletion of data in the shared database as well as the non-shared databases. You could also take your tables and put them in a different platform in the future this way, instead of staying in SQL Server.

Option 2 would be more work to keep everything in sync and more error prone. I have been in that situation and it will fail at some point and cause you to have to resync them. I would only go this method if you HAVE to for business, technical or geographic concerns.