A natural key identifies an entity with data that already has business meaning. SocialSecurityNumber is a natural-key example; an arbitrary incrementing EmployeeID is an artificial key. Choose deliberately: a meaningful attribute can identify a row, but not every entity has an obvious candidate. section 4.1.1
Natural keys participate in relationships just like other primary keys. An Offices primary key can be referenced from Employees as a foreign key, restricting references to existing offices and enforcing referential integrity. section 4.1.1
Keep these design checks in view:
- Recognize that a natural key can contain multiple columns; such a key is a composite key. section 4.1.1
- Minimize composite keys where possible, since they make key structure more involved. section 4.1.1
- Use an artificial key when no good natural-key candidate exists, as with the Products and Orders entities. section 4.1.1
- When normalizing a relation with a composite key, check whether every attribute depends on the whole key before treating it as 2NF. section 4.1.3
Meaningful attributes as keys
A normalized sales design uses business attributes such as Sales Person Email, Sales Area Name, Customer Company Name, and Product Name/Product Type Name as identifying or referenced values. The resulting relationships make dependencies visible, while the Sales Order Number identifies a sale. section 4.1.3
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 business-named primary keys and foreign keys connecting Sales, Sales Person, Sales Areas, Customer, and Sales Order Details. section 4.1.3
