Re: When to use Composite Primary Key?
Posted by: Roland Bouman
Date: March 14, 2006 05:25PM

Hi,

there is actually quite some debate about that.

> think it's natural for each topic reply to be identified by its
> topic's id plus its own sub-id.

What I find very clarifying is to make a distinction between
-identifying an object
-requiring unicity

Let me explain. Of course, it's natural to see the subsequent replies to a particular topic as individual, unique members within the topic. As such, the topic_id together with the sequence number in which the replies appear within on topic should be unique. Unicity should be required for the column combination.
However that does not mean that that combination of columns is always convenient to actually identify the object. Lot's of people find it quite convenient to give each table it's own surrogate key - ususally an autoincrementing number. If that is done, then this surrogate key should be consistently used to implement relationships between different objects. When you decide to do that for some tables (you some to have done so for the user table and the topic table) I think you should try and stick to that pattern: either use surrogate keys - everyewhere - or don't use surrogate keys - use natural keys - always.


Maybe this blog entry - and the comments - can help you make up your mind: http://www.futhark.ch/mysql/131.html

Options: ReplyQuote


Subject
Written By
Posted
Re: When to use Composite Primary Key?
March 14, 2006 05:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.