Primary Key / Clustered key for Junction Tables

Question

Let's say we have a Product table, and Order table and a (junction table) ProductOrder.

ProductOrder will have an ProductID and an OrderID.
In most of our systems these tables also have an autonumber column called ID.

What is the best practice for placing the primary key (and therefor clustered key)?

• Should I keep the primary key of the ID field and create a non-clustered index for the foreign key pair (ProductID and OrderID)

• Or should I put the primary key of the foreign key pair (ProductID and OrderID) and put a non-clustered index on the ID column (if even necessary)

• Or ... (smart remark by one of you :))

Answer 1

This appears to be for a dynamic system where many orders will be added. The clustered index should therefore be on your autonumbered column.

You can make index the primary key and put another unique index on the pair of columns. Or, you can make the pair of columns the primary (but non-clustered) key.

The choice of using the primary key or a unique index key is up to you. But I would make sure that the one that is clustered is for your autonumber column.

Answer 2

I know these words might make you cringe, but "it depends."

1) First answer why you are using the surrogate key ID in the first place; that might help add context to the problem. I assume you are using the surrogate key because you are using some framework that works well with single column keys. If there is no specific design reason, then for a join table, I'd simplify the problem and just remove the autonumber ID, because it brings no other benefit. The primary key becomes the (ProductID, OrderID). If not, you need to at least make sure your index on the (ProductID, OrderID) tuple is unique to preserve data integrity.

2) Clustered indexes are good for sequential scans/joins when the query needs the results in the same order that the index is ordered. So, look at your access patterns, figure out by which key(s) you will be doing multi-row selects / scans, and by why key you'll be doing individual record access, and create the clustered index on the key you'll scan most, and the non-clustered key on the id you'll randomly access. You have to choose one or the other, since you cannot cluster both.

Answer 3

My preference has always been to create an autonumber for Primary Keys. Then I create a unique index on the two Foreign keys so that they are not duplicated.

The reason I do this is because the more I normalize my data, the more keys I have to use in joins. I have ended up with designs going six to seven levels deep, and if I use keys flowing from one level to another, I could potentially end up with a n^2 keys in the join.

Try convincing my SQL Developers to use all of that for a single query, and they will really like me.

I keep it simple.

Answer 4

The correct and only answer is:

• Primary key is ('orderid' , 'productid')
• Another index on ('productid' , 'orderid')
• Either can be clustered, but PK is by default

Because:

• You don't need an index on orderid or productid alone: the optimiser will use one of the indexes
• You'll most likely use the table "both" ways
• You don't need a surrogate key because you already have them on the linked tables. So a 3rd columns wastes space.