3.1 From Business Rules to ER Models and Relational Schema

Database design turns identity, relationship cardinality, and lifecycle rules into executable constraints. A diagram with table names and lines cannot yet answer whether a task may be claimed twice or deleted after completion. This lesson maps those rules to keys, foreign keys, and conditional updates. Neat boxes help the drawing; explicit rules help the database.

The SQL blocks are partial design examples, not a sequence of migrations for the previous chapter's database. Running them requires a separate database with the missing catalog_items and organization tables, foreign keys, and consistent type constraints.

Learning Objectives

• Identify entities, attributes, and relationships from business language;
• Distinguish between natural keys, surrogate keys, and foreign keys;
• Accurately express one-to-one, one-to-many, and many-to-many relationships;
• Map an ER model to a SQL schema;
• Record temporal, optional, and deletion semantics.

Start from Facts and Rules

Assume a task system has these rules:

1. Each customer has a stable ID, a display name, and a level;
2. Each task is claimed by one customer;
3. A task can require multiple item types, and the same item type can appear in multiple tasks;
4. Each task-item pair records a quantity;
5. Task status can change only through allowed state-machine transitions;
6. Deleting an item must be rejected while a task still references it;
7. After a task is completed, the historical record of its reward and item requirements must be preserved.

Rules 3 and 4 together indicate that task and item form a many-to-many relationship, with the relationship itself carrying a quantity attribute. Therefore, an associative entity is required, not a comma-separated list of item IDs stored in a single column.

Entity and Value Object

To determine whether a concept warrants its own entity, ask:

• Does it have a distinct identity?
• Does it have an independent lifecycle?
• Is it referenced by multiple objects?
• Does it require separate constraints, permissions, or history?

An "address" might simply be a set of value attributes belonging to a user, or it could be a reusable, verifiable, versioned shipping address entity. The answer lies in business rules, not in the name itself.

Avoid turning every string into a lookup table. Only when a value domain requires stable identity, metadata, referential integrity, or an independent lifecycle should a standalone table be justified.

The Three Roles of Keys

Candidate/Natural Key

A candidate key is a minimal set of attributes that uniquely identifies a row. A natural key is an identifier with business meaning, such as an external customer code within a tenant. These describe different properties. A natural candidate key needs uniqueness and non-null guarantees; if it can change, references need an update policy.

Surrogate Key

A surrogate key is system-generated and carries no business meaning, such as an integer ID or UUID. It keeps references independent of changes to business codes. Whether it is shorter depends on the actual types and lengths, and it does not automatically prevent business duplicates. If (tenant_id, external_code) must remain unique, a UNIQUE constraint should still be applied even when using a surrogate primary key.

Foreign Key

A foreign key expresses that a child value must reference an existing candidate key. It is not merely a convenience for joining tables; it is a formal contract for referential integrity.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    tenant_id     INTEGER NOT NULL,
    external_code TEXT NOT NULL,
    display_name  TEXT NOT NULL,
    level         INTEGER NOT NULL CHECK (level >= 1),
    UNIQUE (tenant_id, external_code)
);

Whether to use a surrogate key and whether to preserve natural uniqueness are two independent design decisions.

Cardinality and Participation

In an ER diagram, the connections must answer at least the following questions:

• How many B entities are associated with each A entity, at minimum and at maximum?
• Which side of the relationship is mandatory?
• Does the relationship have its own attributes?
• What are the validity period and deletion rules for the relationship?

Common mappings:

Relationship	Schema Mapping
one-to-many	A foreign key is typically placed on the "many" side
optional one-to-many	The foreign key can be NULL, or an independent relationship table may be used
one-to-one	A foreign key plus a UNIQUE constraint; placement depends on optionality and lifecycle
many-to-many	An associative table containing foreign keys from both sides

One-to-Many

CREATE TABLE tasks (
    task_id      INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status        TEXT NOT NULL,
    reward_cents  INTEGER NOT NULL CHECK (reward_cents >= 0),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT,
    CHECK (status IN ('active', 'completed', 'cancelled'))
);

NOT NULL expresses that every task must have a customer; the foreign key enforces that the customer must exist. These are distinct constraints.

Many-to-Many with Relationship Attribute

CREATE TABLE task_items (
    task_id INTEGER NOT NULL,
    item_id  INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (task_id, item_id),
    FOREIGN KEY (task_id)
        REFERENCES tasks(task_id)
        ON DELETE CASCADE,
    FOREIGN KEY (item_id)
        REFERENCES catalog_items(item_id)
        ON DELETE RESTRICT
);

The composite primary key allows at most one row per task/item pair. If an item can appear in several roles within a task, include the role in the key or use a separate relationship ID with an appropriate uniqueness rule.

One-to-one Is Not Common

CREATE TABLE customer_profiles (
    customer_id INTEGER PRIMARY KEY,
    biography     TEXT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

A shared primary key ensures that a customer has at most one profile. Normalizing tables might be driven by optional large columns, security boundaries, or lifecycle considerations, not because "every concept should have its own table." If both entities are always created and read together, with consistent constraints and permissions, keeping them in a single table may actually be simpler.

Optionality Should Not Rely on Sentinel Values

Use NULL for a missing date rather than a sentinel such as 0000-00-00. Likewise, represent the absence of an organization affiliation with a nullable foreign key or a separate membership-history record, not organization_id = 0, unless zero is an explicitly defined valid reference.

Arbitrary sentinel values complicate the meaning of a column and its filters. An enforced foreign key still rejects a nonexistent 0. Creating a special “no organization” parent row to make it pass introduces a new obligation: distinguish that placeholder from real organizations.

Multi-valued attribute requires a relationship

Incorrect design:

tasks.required_item_ids = '1,3,5'

This approach leads to several issues: it cannot enforce a foreign key constraint on each individual element, search operations depend on string parsing, updates risk duplication and ordering problems, and there's no natural way to track quantity.

The correct design is task_items. While JSON/array columns have legitimate use cases, they should be carefully evaluated whenever internal elements require querying, constraints, associations, or independent updates. A separate relation may make those operations and constraints easier to express.

A State Machine Is Not Enough with a Single CHECK

CHECK (status IN (...)) restricts the current value but cannot reject an illegal transition such as completed -> active. A complete state machine can be implemented using:

• application service validation within a transaction;
• transition table combined with a stored procedure;
• database triggers;
• an append-only event/history model;
• database-specific policies.

Regardless of the approach chosen, concurrent update scenarios must be handled, never relying on "select first, then update" without a version or lock predicate.

For example, optimistic update:

UPDATE tasks
SET status = 'completed'
WHERE task_id = :task_id
  AND status = 'active';

The application must verify that exactly one row is affected; a zero result may indicate either the record does not exist or its state has already changed.

Time Changes the Model

If only the current state matters, a nullable organization_id may suffice for “customer belongs to organization.” If queries must reconstruct past membership, use a membership interval:

CREATE TABLE organization_memberships (
    customer_id INTEGER NOT NULL,
    organization_id      INTEGER NOT NULL,
    valid_from    TEXT NOT NULL,
    valid_to      TEXT,
    PRIMARY KEY (customer_id, valid_from),
    CHECK (valid_to IS NULL OR valid_to > valid_from)
);

Text comparison here requires one lexically sortable date or timestamp format and timezone convention; the fragment omits complete date validation and foreign keys. This CHECK still cannot prevent overlapping intervals for the same customer. Whether to use an exclusion constraint, a trigger, or an application transaction depends on the database's capabilities.

Choose Deletion Semantics for Each Relationship

• RESTRICT/NO ACTION: Both constrain dangling references, but their timing can differ. SQLite rejects RESTRICT actions immediately; NO ACTION follows the immediate or deferred constraint-check timing;
• CASCADE: Parent row deletion propagates to dependent rows;
• SET NULL: Relationship becomes unknown/absent, but the child column must allow NULL;
• Soft delete: Retains the row with a lifecycle flag, without automatically resolving uniqueness, foreign key constraints, or query filtering.

CASCADE is appropriate only for purely dependent rows and should not be used for data with independent audit or financial significance. Before deletion, map the dependency graph and validate the scope of impact. Rule 7 also requires preserving completed tasks, so their deletion must be prohibited or a protected historical snapshot retained. CASCADE on task_items alone does not meet that rule.

See SQLite foreign key actions for action and deferred-check details.

From Model to Migration

Design deliverables should include:

1. Business terminology and invariants;
2. Entity relationships, cardinalities, and optionality;
3. Initial drafts of tables, keys, constraints, and indexes;
4. Deletion and history policies;
5. Migration, backfill, and rollback strategies;
6. Representative queries and expected cardinality;
7. Rules that cannot be directly enforced by the database, along with their owners.

Lesson Summary

The core of an ER model is not the diagram itself, but rather identity, cardinality, participation, and lifecycle. When mapped to a schema, primary keys, UNIQUE constraints, foreign keys, CHECK constraints, and transactional behavior should carry as many enforceable rules as possible. In the next lesson, we'll use functional dependencies to verify whether a relation contains multiple distinct facts.