A candidate key is the smallest set of columns that uniquely identifies each row. The common mistake is choosing a column that looks descriptive without checking whether duplicates exist. A super key identifies every row; it becomes a candidate key only when removing any column breaks that uniqueness. section 4.1.3
Check a proposed key before relying on it:
- Confirm that its values distinguish every row. section 4.1.3
- Remove each column in turn; if uniqueness remains, the original set was not minimal. section 4.1.3
- Use one column when it suffices; otherwise use a composite key. section 4.1.3
Product names need a second attribute
Sales Order Number and Purchase Order Number each identify their respective rows alone. Product Name does not: supplier parts can share names with products sold to customers. Product Name plus Product Type Name therefore forms the candidate key. section 4.1.3
Sales Sales Order Date Sales Order Number Sales Person Name Sales Person Email Sales Area Name Sales Area Manager Customer Company Name Customer Contact Name Customer Contact Email Customer Invoice Address Customer Delivery Addresses Sales Order Delivery Due Date Sales Order Delivery Actual Date Sales Order Item Sales Order Quantities Currier Used for Delivery Purchasing Supplier Name Supplier Contact Name Supplier Contact Email Supplier Address Purchase Order Date Purchase Order Number Purchase Order Items Purchase Order Quantities Products Product Name Product Stock Level Product Next Manufacture Date Product Next Manufacture Quantity Back Order Manufacturing ID Product Type Name Product Type Description Product Category Name Product Category Description Product Subcategory Name Product Subcategory Description
Inspect the original Sales, Purchasing, and Products attributes to see why Product Name alone is unsafe and why Product Type Name is needed. section 4.1.3
