Relational Modelling

Relational modelling chooses tables, keys, and constraints so facts are stored once and relationships are enforced by the database. It is the integrity layer beneath SQL, data-warehouses, and many data-contracts.

Keys and constraints

The core contract is: primary keys identify rows, foreign keys enforce parent-child relationships, not null protects required fields, and unique prevents duplicate business identifiers. The example below creates a parent customers table, a child orders table, and then attempts two invalid writes:

PRAGMA foreign_keys = ON;
 
CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  email text NOT NULL UNIQUE
);
 
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(customer_id)
);
 
INSERT INTO customers VALUES (1, 'a@example.com');
INSERT INTO orders VALUES (100, 1);
 
-- Rejected: duplicate business identifier.
INSERT INTO customers VALUES (2, 'a@example.com');
 
-- Rejected: order points at a customer that does not exist.
INSERT INTO orders VALUES (101, 99);

Expected constraint failures:

IntegrityError UNIQUE constraint failed: customers.email
IntegrityError FOREIGN KEY constraint failed

The database rejects both a duplicate email and an orphan order. A downstream data-quality check can detect these problems after loading, but a relational model prevents them at write time.

Modelling choice

Operational schemas often normalize entities to reduce update anomalies: customer attributes live in customers, not repeated across every order. Analytical schemas may intentionally denormalize into dimensional-modelling stars for simpler queries. The design question is not “normalized or not” but which grain each table owns and which invariants the system can enforce.

Failure modes

Surrogate keys without unique natural-key constraints allow duplicate entities. Nullable foreign keys silently weaken relationships. Missing indexes on foreign-key columns can make deletes, updates, and joins expensive even when the model is logically correct.

References

• PostgreSQL documentation: Constraints
• SQLite documentation: Foreign Key Support

Nav

Section — Data Engineering

SQL →