Separating weakly linked database schemas

Question

I've been tasked with revisiting a database schema we designed and use internally for various ticketing and reporting systems. Currently there exists about 40 tables in one Oracle database schema supporting perhaps six webapps.

However, there's one unifying relationship amongst them all: a rooms table describing the room. Room name, purpose and other data are thrown into a shared table for each app. My initial idea was to pull each of these applications into a separate database, and perform joins between a given database and the room database. But I've discovered this solution prevents foreign key constraints in SQL Server 2005. It seems silly to duplicate one table for each app and keep those multiple copies synchronized.

Should I just leave everything in one large DB, or is there something else I can do separate the tables without losing FK constraints?

Answer 1

The only way to achieve built-in referential integrity is to have the table inside the database in which it is referenced. You might be able to achieve the equivalent of referential integrity using triggers but it would likely be deathly slow.

Answer 2

You might be able to use SQL Server replication, in it's "Transactional replication" mode/form. http://msdn.microsoft.com/en-us/library/ms151176.aspx

Answer 3

if all the apps truly use and depend on the rooms - then keep them all in one DB. you can still set privilege on the tables properly, and manage the data sets in the non overlapping areas normally - is there any task you imagine you will not be able to perform when things are together?