I agree that it is best to put all related tables in the same database. That assumes however, that it is always possible to move all related tables into one database. Many organizations (for multiple reasons) have multiple databases. Of course it is possible to copy an entire table and maintain the data in multiple locations, but in my experience this causes much bigger problems than the downsides of triggers that you mentioned above.

You can further minimize the risks of triggers/udfs by maintaining triggers/udfs on both databases. One to check for parental constraint, the other for checking child constraints. This is still not ideal, but it is useful when maintaining cross-database RI is required.

I don’t agree with this idea of using a single database for everything.

For example, I’m working on an ERP project with 7 modules, and we need to consider

- Security issue

- Performance issue

It is easier for me to design separated database for HQ, Payroll, Purchase order, and create user for each database. Users of payroll don’t need to access Purchase Order database and so on.

With different databases, we have different log files, and this makes the database faster

If I’m working on payroll, I make a backup of payroll only; I don’t need to make backup for all the databases,

@dgm

Maurice,

We can grant permissions to schemas or have even finer level of granularity. If users do not have permissions for a schema or an object, they just do not see it. There is no need to have separate databases to enforce security. Similarly, in Enterprise Edition there are built in ways to partition and scale up without splitting a database.

On the other hand, if you ever have to restore two or more databases with parent-child relationships across database boundaries, you will have to take care of orphan rows. You will not want to do it again once you have experienced it for yourself.