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:

    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.


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


3 Answers 3


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

  • 4
    +1 for I can't find any examples on the internet: that shows how stupid this is
    – JNK
    Feb 24, 2012 at 14:16
  • I made it clearer about the primary key. Also, apparently my colleague has actually come across such design before or so I'm told Feb 24, 2012 at 14:49
  • @Tomasz Pluskiewicz: A surrogate key is not the primary key! It is chosen for complement the natural key at implementation time. See dba.stackexchange.com/a/13779/630 Also, your colleague should show us an authoritative article that demonstrates this technique. I've seen complete piles of rubbish in my time, but I don't repeat them...
    – gbn
    Feb 24, 2012 at 14:53

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.

  • LinkType column is something of a discriminator. Just telling which pair a row actually relates. Just adds to the contraption if you ask me. Feb 24, 2012 at 14:52
  • @TomaszPluskiewicz I think the best way to show him why it sucks is to build a sample dataset with both kinds of tables in it and run some queries. His model will be much slower than a traditional model
    – JNK
    Feb 24, 2012 at 14:54

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.