Boyce-Codd normal form (BCNF) is a practical target for organizing a relational database into smaller tables that each represent one entity or concept. It minimizes redundancy and dependency while strengthening data integrity and consistency. A design whose tables are in BCNF can be treated as fully normalized. section 6.2 section 6.17
Use BCNF as a checklist. Each table needs a primary key, no multivalued columns, and nonkey columns that depend on a key rather than on some other nonkey column. This also rules out transitive dependencies: nonkey columns should be directly dependent on a key. section 6.17 section 6.2
Check and revise a table in this order:
- Identify a primary key before proceeding. A primary key gives each row an identity. section 6.3
- Look for columns holding multiple values per row. Whether a value is indivisible depends on user requirements; redesign such a column to hold one value, often in a new table. section 6.4
- Test functional dependencies. If one column value always identifies one corresponding value in another, the first functionally determines the second; the relationship is directional. section 6.5
- Move attributes whose dependency requires a different key into a table with that key. This prevents redundant data and insertion difficulties. section 6.6
Separating purchase details
A purchase row can be identified by purchase_id, which determines receipt-level facts such as total_price and purchase_time. But product_quantity and product_price depend on the combination of purchase_id and product code: one purchase can contain several products, and a product can have different quantities or prices in different purchases. Put those product-specific values in a purchase_product junction table keyed by purchase_id and code, while keeping receipt-level values in purchase. section 6.6
BCNF problems commonly show up as update, deletion, querying, redundancy, or consistency problems. Constraints support the resulting design: use primary and foreign keys, NOT NULL where values are required, UNIQUE for nonkey uniqueness, and CHECK when validation needs more complex business rules. section 6.4 section 6.17
