Another many-to-many relationship question

Question

My database includes a Customer and a Subcategories table.
Customers can belong to one-or-more Subcategories (and of course there are one-or-more Customers for any Subcategory).
I wonder which is the best solution to link these tables that share a many-to-many relationship:

• A "standard" junction CustomerSubcategory table that includes just two fields: CustomerID (PK, FK) and SubcategoryID (PK, FK), or
• A CustomerSubcategoryDetail table that would also include a CustomerSubcategoryDetailID (PK), as well as the SubcategoryID (PK, FK) and CustomerID (FK) fields.

Any advice?

Cheers, Corbex

Answer 1

Go with your first choice. Make sure that you create a key that includes both columns and enforces uniqueness. That way you'll never need a separate primary key to distinguish between otherwise identical rows.

There is a additional benefit in having both columns in the unique key. Queries that need to perform a lookup by the first column can use the index, but they never need to read the data rows because the index already contains the second column.

Answer 2

I don't see the need for a third column with a customersubcategoryid; therefore, I would go with the first option. The extra column is not going to give you any advantage in any of your joins. You won't even use it.

Answer 3

I don't think the additional PK on the junction table adds any value. Will you ever have to look up the value(s) in that table without being able to identify them by Customer and/or SubCategoryID? Will the CustomerSubCategoryDetailID value be used anywhere else?

An interesting discussion ensued on my blog when I complained about folks knee-jerking an IDENTITY column onto every single table. Some folks made some good arguments for having an OrderDetailID column on the OrderDetails table. Do any of these situations apply to you?