Best way to create relationship between multiple tables

database-designdenormalizationnormalization

I have a question about how to best handle a table that would be shared among multiple relationships, but shouldn't really be joined with all these tables. For example, suppose I have a table that has US States in it. Then, in this example, I have maybe three or four other tables that have a relationship with the States table, like: a Users table (where the user is from), a Car registration table, an Email table (maybe on this system we track sent and received emails and where they originate from) and some other table. If I joined all of these tables to the State table, it kind of makes it look like all of these table should have some sort of relationship with one-another. Maybe this is so for a few tables (Like User and Car registration) but not necessarily between all the tables (User and Email table for example). Or another example might be that we have two tables, Employees and Work_Office. Each has there own state. And each is joined to one-another, creating a loop (Work_Office has many Employees. Employee belongs to State and Work_Office belongs to State). Now we have a loop.

So, is it better to have multiple "State" tables? It doesn't seem like it would be. But at the same time, I don't want to create relationships that might not really exist? Or is it better to just have the tables but not actually create any sort of relationship?

Best Answer

No, you should not have multiple State tables. Having a single table with relationships to the others is the right way to approach this. The perception that this somehow implies that the other tables are linked, other than they share a State, is purely in your head - such an interpretation would not be common practice.

You should distinguish between your logical data model and your physical database design. I would recommend adding the relationships to State into the logical model. You may choose to omit the foreign key constraints from your database for performance or other reasons.

There's a post on Dell's Toad site which I can't find just now. It's about a 200 table DB, every one of which has a UpdateUserId column and corresponding FK relationship to the User table. The plan for a DELETE User.. statement is a thing of beauty - a perfect left-deep triangle of 200 joins! They would have been better without the constraint.

Edit: here's the link. I mis-remembered the details but the principle's the same.

Related Solutions

Merging tables with a 1 to 1 relationship in a database

If they are small reference tables then I would be more inclined to merge them than if they are not. If reference tables, then they are likely separated due to over the top normalisation and merging them makes séance from query performance perspective.

If they were large fact tables I would be less inclined to wade in before checking ramifications, as it could be that they were separated onto separate file groups with files on different spindles for parallel operations. If this is the case however, I would still be inclined to merge them, and consider table partitioning.

Either way, be sure to thoroughly check for Database and client application dependencies before Changing the table structure.

How to relate 2 tables to a third when the first 2 tables have a relationship

Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)

We remove footnote_num from:

Table:      a_ref         -- was named:  bridge_a_reference
     a_id, 
     ref_id
Primary key: 
     (a_id, ref_id)
Foreign keys:
     a_id  -> a
     ref_id -> reference

We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:

Table:      a_ref_with_footnote 
    a_id, 
    ref_id,
    footnote_num
Primary key: 
    (a_id, ref_id)
Unique key:
    (a_id, footnote_num)
Foreign keys: 
    (a_id, ref_id)  -> a_ref

And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):

 Table:     b_ref         -- was named: bridge_b_reference:
     a_id, 
     b_id, 
     ref_id,
 Primary key: 
     (b_id, ref_id)
 Foreign keys: 
     (a_id, b_id) -> b
     (a_id, ref_id) -> a_ref_with_footnote

Best Answer

Related Solutions

Merging tables with a 1 to 1 relationship in a database

How to relate 2 tables to a third when the first 2 tables have a relationship

Related Question