Many-Many with no primary key.

Question

In this presentation: http://docs.google.com/present/view?id=ddqht6x_1559tbtcgg Slide 6. The presenter's snippets_tags table has a primary key, why? isn't that just wasting space? I usually use the other two cols. and not bother with a primary key.

Answer 1

If you want to link other data to the relationship.

For example you might want to add a logtable with a link to when the relationship was created or something like that.

If you need to add this kind of metadata, you don't want that in the relationship table, since that need to be as compact as possible to make joins efficient, so you add a primary key and link that to another table with all the metadata.

Answer 2

I have to agree with you. I would make snippet_id and tag_id the primary key, that would also guarantee that there are no unnecessary duplicate entrys.

Answer 3

It is not useful in that particular case. If, however, that table was involved in a one-to-many relationship (which would be an indication the design is horribly wrong), then it'd be needed.

Answer 4

Yes, the id column is unnecessary, and the PK should be against the other two columns.

But please, let's all be clear. ID column != Primary Key.

Answer 5

That kind of schemas are very common. IMO, I would have used a composite pk, in this case snippet_id and tag_id. I don't see a "true" advantage over the composite model, maybe you can save disk space.

The advantage of a composite model is that you can avoid doing joins and just query the foreign key.

Answer 6

Just a matter of taste. Both practices are common.