Relational Data Modeling

An entity-relationship model identifies entity types, their attributes, and relationships before choosing tables. A relational schema then represents each fact once at a defined grain. For example, order_line represents one product on one order, whereas order represents the order header.

Entities, Keys, and Relationships #

Every table needs a candidate key that uniquely identifies its rows. Choose a primary key that is stable, non-null, and suitable for joins. A surrogate key does not remove the need for unique constraints over natural business identities, such as an account’s external identifier.

Map relationship cardinality as follows:

1. For a one-to-one relationship, put a UNIQUE foreign key on the optional side, or merge the tables when their lifecycle and access controls match.
2. For one-to-many, put the foreign key in the many-side table.
3. For many-to-many, create an associative table containing foreign keys to both entities and attributes belonging to the relationship.

NOT NULL expresses required participation. A nullable foreign key means the relationship is optional for that row. A foreign-key constraint prevents orphaned references, while ON DELETE behavior must reflect the business rule: restrict, cascade, set null, or handle deletion in an explicit workflow.

Normalization #

Normalization starts from functional dependencies: if a value of X determines one value of Y, write X -> Y. First normal form requires one value from the declared domain per column and no repeating group. Second normal form removes non-key attributes that depend on only part of a composite candidate key. Third normal form removes transitive dependencies in which a non-key attribute determines another non-key attribute. These rules prevent insertion, update, and deletion anomalies because each fact has one authoritative location.

Denormalize only after measuring a read cost and defining how the duplicated value remains correct. Record whether a transaction, trigger, generated column, change-data-capture consumer, or rebuild job maintains the copy; then monitor staleness and reconciliation. An index, summary table, or query rewrite can solve the read path without creating an uncontrolled copy of business data.

CREATE TABLE order_line (
  order_id BIGINT NOT NULL,
  line_number SMALLINT UNSIGNED NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (order_id, line_number),
  CONSTRAINT fk_order_line_order
    FOREIGN KEY (order_id) REFERENCES orders (id),
  CONSTRAINT fk_order_line_product
    FOREIGN KEY (product_id) REFERENCES product (id)
) ENGINE = InnoDB;

The composite primary key makes the table’s grain explicit: one line number exists once within an order. InnoDB requires an index whose leading columns cover each foreign key and may create one automatically; inspect SHOW CREATE TABLE and query plans before adding redundant indexes.

References #

• InnoDB Foreign-Key Constraints
• MySQL Constraints