Relationships between multiple databases

Question

Relationships between multiple databases

c00ler01

Ten Centuries

Points: 1180
More actions
December 3, 2009 at 6:38 am

#206894

Hi,
I'm wondering why is it a bad practice to have table relationships between 2 or more databases?
Would really appreciate any ideas or academic publications...
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

that's easy.

there is no way to ENFORCE a relationship between two databases; Foreign keys,constraints,etc can only exist within a database. if you cannot use the built in fuinctionality of the server to enforce relationships and prevent them from being broken, that would be a bad practice.

you may *know* an entity in databaseA is related to a businessfunction in databaseB, but that would not prevent the deletion of the entity and the orphaning of it's related functions, for example.

if you need to establish relationships, you want to make sure all the related objects are together inside a database to assure data integrity.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

c00ler01 Ten Centuries Points: 1180 More actions · Answer 2

Thanks a lot, didn't realise that I can't have relationships between tables in different databases!

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

Adding to what Lowell has said, if at all you need to test the application and need a copy of these databases, your application will need all of the related databases.

I witnessed this pain and believe me we had a set of databases (16 or 17), which was very tough. Backup and restoring (at the test system) one of those database would cripple the application on test, since all DB's were not in sync.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

GSquared SSC Guru Points: 260824 More actions · Answer 4

You can kind of enforce relations between tables in separate databases by use of triggers. I say "kind of" because it's tricky to get them to really encompass the whole concept.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

And Bru is right on track with the difficulties associated with setting up test/dev systems when the DB's all need to be in sync. I had to deal with that in a previous job. I had to build scripts to sync them all back up which would sometime require backing out some data. All in all it was a pain.

CEWII

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

And what happens when database A or B become terribly problematic due to size, contention, number of users, what have, and you need to move them to another server? Linked servers and all the performance issues, recoding, retesting, etc. that is implied there. Also, what happens when database A or B has to get upgraded to a new version of 2008 and starts having different data types... It just gets messy.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

c00ler01 Ten Centuries Points: 1180 More actions · Answer 7

The worst case scenarios described the problem rather well!! Thanks!