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

Question

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.

Answer 1

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

Answer 2

The first practical reason I can think of is performance.

In a "traditional" model, you can have a unique index on Idemployee, Idstore or whatever the fields are, and get great performance on lookups. It is also easy to maintain for inserts. Unique indexes get you merge joins more frequently, which can make a lot of JOINs blazingly fast.

In your example model, to get decent performance you will need to have a single field index on every FK field in the table at a minimum, ideally a covering index on all the combinations that will be referenced, i.e.:

• Employee/Store
• Employee/Sale

I'm not sure what linktype is but if you reference it, it should probably be indexed.

These indexes will need to be maintained for every row in the table, whether or not the field is populated. You can add a filter but that will get tricky too with so many combinations.

It'll also complicate your logic. You will either need to do a lookup on the employeeid, find a row with an empty store value, and update; or, just insert a new row for every new link, which kind of defeats the purpose of consolidating the fields.

Basically you will be using MORE disk space, having MORE indexes to maintain, and complicating your logic for essentially no reason. The only "benefit" is it's fewer tables to deal with.

Answer 3

Putting multiple relationships into one table can be useful if those relationships have the same attributes and/or if you want to aggregate data over multiple relationships.

It is necessary if the types of relationships are defined by the user at runtime. However this is rarely really the case.

In your example the relationships don't share attributes, the relationships even referening two different tables. This makes it hard to enforce constraints and the design is also less intuitive.

I would only chose that design if creating tables literally costs money.