The nice thing about SQL is you can test theories. I tried the foreign key constraint idea:

"Cross-database foreign key references are not supported. Foreign key 'BLAH.DBO.TEST_TABLE'."

So I'm guessing NO on the foreign key idea between databases 😉

The approach of applications having their own databases is probably the norm, unless the individual applications work on the same data, and in this case are they really different applications or just different modules in the same application or system?

2 cents!