Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 FKs allow null a surrogate key is the only option.

share|improve this question
1  
It's like OTLT or EAV but worse because it proliferates columns rather than rows! –  onedaywhen Feb 24 '12 at 15:44
1  
add comment

2 Answers

up vote 8 down vote accepted

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

share|improve this answer
4  
+1 for I can't find any examples on the internet: that shows how stupid this is –  JNK Feb 24 '12 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 –  Tomasz Pluskiewicz Feb 24 '12 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 '12 at 14:53
add comment

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.

share|improve this answer
    
LinkType column is something of a discriminator. Just telling which pair a row actually relates. Just adds to the contraption if you ask me. –  Tomasz Pluskiewicz Feb 24 '12 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 '12 at 14:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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