HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Why shouldn't I have one table for multiple relationships?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whytableshouldnoneformultiplerelationshipshave

Problem

Assuming I have multiple relations in my database, for example Store, Employee and Sale, and I want to connect pairs with a simple binary relationship. Personally I would create tables named Employee_Store and Employee_Sale with a natural key composed of the foreign keys.

Now, my colleague insists on creating one table for multiple relationships. For the above example there could be a table called EmployeeLinks:

EmployeeLinks(
    IdLink int PK, 
    IdEmployee int FK null,
    IdStore int FK null,
    IdSale int FK null,
    LinkType int not null
)


Please help me with good reasons why this is not a good idea. I have arguments of my own but I would like to keep them private and hear your unbiased opinions.

EDIT:

Initially the table above would have no primary key (!). Because the foreign keys allow null a surrogate key is the only option.

Solution

What does your colleague propose as the primary key for this link table?

Primary key columns can not be NULL of course: the table above has nullable.

There isn't any natural row identifier (which is what a PK is) in the example above (a IDENTITY column is not a primary key), therefore it fails in any modelling process. Don't even think about creating tables without some model (ERD, ORM, IDEF1X, whatever)

You'd also need CHECK constraints to ensure you don't have 3 way links.

Finally, you're straying into 4th and 5th normal form territory but for the wrong reasons.

I can't find any examples on the internet: that shows how stupid this is

Context

StackExchange Database Administrators Q#13859, answer score: 14

Revisions (0)

No revisions yet.