Best way to create relationship between multiple tables

Question

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?

Answer 1

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.

Answer 2

"reside in the same state" is a relationship. Whether it's a meaningful relationship or not depends on the subject under discussion. If it's a database about political affiliations, it might be very meaningful. For most applications, it would be completely meaningless.

Inferring meaning from the table design is a little like "data forensics" or reverse engineering. Sometimes it works, sometimes it doesn't. You need a model that is about meaning, in addition to a model that is about table design and table linkages.

Your table design should be driven by what facilitates your intended use of the data, including likely future uses.

Answer 3

Your relationships should be based on the data itself, not necessarily what a diagram would look like. In your example of states, if you have a relationship (foreign key) to the states table, that would limit the values in the state column to the values in the states table. It would be perfectly okay to have a value in the state column of 'PA' for Pennsylvania (assuming it exists in the states table), but totally inappropriate if the value was put in as 'AP', since that value is not a valid state and wouldn't be in the states table. The foreign key relationship preserves data integrity. Once you lose that data integrity, your data is worthless.