Third normal form (3NF) is the practical stopping point for most relational schemas. Start with a design already brought to 1NF and 2NF, then check whether any attribute depends on another non-key attribute rather than directly on the primary key. That dependency is a warning that the attribute belongs in a separate entity. section 4.1.3
Use this check before declaring a schema normalized:
- Identify each entity's primary key and the attributes stored with it. section 4.1.3
- For every non-key attribute, ask whether it depends on the key or on another attribute. section 4.1.3
- Move attributes dependent on a non-key attribute into a new entity, then link it with a key. section 4.1.3
- Avoid continuing automatically past 3NF: excessive normalization can create other schema problems. section 4.1.3
Separating sales details
A sales order can retain its order number, dates, and foreign keys while salesperson, sales area, customer, and customer-contact details become separate entities. This prevents those descriptive details from being stored as if they were properties of every sales order. 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 decomposed sales design to see order records linked to Sales Person, Sales Areas, Customer, and Customer Contact through foreign keys. section 4.1.3
