A composite key identifies one row through a combination of multiple columns. It is often a natural key: each column alone may repeat, but their values together are unique. For example, a product name is not enough when the same name appears in different product types; Product Name plus Product Type Name can identify the product. section 4.1.1 section 4.1.3
Mistake #1: treating a composite key as automatically wrong. A multi-column key is appropriate when the columns form the minimal identifier: removing any one column would no longer uniquely identify a row. Sales Order Number and Purchase Order Number, by contrast, already identify their respective rows alone, so adding columns only makes the key needlessly wider. section 4.1.3
A product and an order detail
In a normalized product design, Product Name and Product Type Name jointly identify a product. Sales Order Details carries both values as foreign-key columns, along with Sales Order Number; the listed primary-key/foreign-key markings make the multi-column relationship explicit. Use every component when relating an order detail to its product. section 4.1.3 section 4.1.3
Mistake #2: making a wide composite natural key the clustered primary key without considering index cost. In SQL Server, a clustered key is carried into nonclustered indexes; for nonunique nonclustered indexes, it is stored at every index level. Wider keys consume more disk and memory, fit fewer entries per 8,000-byte page, and can require more page reads. section 4.2
When a composite natural identifier is wide, use this safer pattern:
- Create a narrow artificial primary key, populated with IDENTITY. section 4.2
- Preserve the business rule with a unique nonclustered index on the natural key columns. section 4.2
- Keep the composite columns where relationships require them, but distinguish enforcing uniqueness from choosing the clustered key. section 4.1.3 section 4.2
Sales
Sales Order Date
PK Sales Order Number
FK Sales Person Email
FK Sales Area Name
FK Customer Company Name
Sales Order Delivery Due Date
Sales Order Delivery Actual Date
Currier Used for Delivery
Sales Person
Sales Person First Name
Sales Person Last Name
PK Sales Person Email
Sales Areas
PK Sales Area Name
Sales Area Manager First Name
Sales Area Manager Last Name
Customer
PK Customer Company Name
FK Customer Contact Email
FK Invoice Address Street
FK Invoice Address Zip Code
FK Delivery Address Street
FK Delivery Address Zip Code
Customer Contact
Customer Contact First Name
Customer Contact Last Name
PK Customer Contact Email
Sales Order Details
PK FK Product Name
PK FK Product Type Name
Quantity
PK FK Sales Order Number
Address
PK Street
Area
City
PK Zip Code
Purchasing
FK Supplier Name
Purchase Order Date
PK Purchase Order Number
Suppliers
PK Supplier Name
FK Supplier Contact Email
FK Supplier Address Street
FK Supplier Address Zip Code
Supplier Contact
Supplier Contact First Name
Supplier Contact Last Name
PK Supplier Contact Email
Purchase Order Details
PK FK Product Name
PK FK Product Type Name
Quantity
PK FK Purchase Order Number
Products
PK Product Name
Product Stock Level
FK Back Order Manufacturing ID
PK FK Product Type Name
FK Product Subcategory Name
Product Types
PK Product Type Name
Product Type Description
Product SubCategories
FK Product Category Name
PK Product Subcategory Name
Product Subcategory Description
Product Categories
PK Product Category Name
Product Category Description
BackOrders
ProductNextManufactureDate
ProductNextManufactureQuantity
PK BackOrderManufacturingID
Inspect the normalized relations to see a composite product identifier reused as foreign-key columns in Sales Order Details. section 4.1.3
