Integrity and normalisation

A database is only as good as the data in it. Constraints let the database refuse bad data, and normalisation designs tables so that each fact is stored once.

Constraints

A foreign key can also say what happens when the referenced row is deleted: refuse (the default), ON DELETE CASCADE (delete the dependent rows too) or ON DELETE SET NULL.

Normalisation

Store each fact in one place, so it cannot disagree with itself. The first three normal forms are the ones used in practice:

• First normal form: one value per column. Not a list of coverages in a text column, but one row per coverage.
• Second normal form: every column depends on the whole key. In policy_coverages, keyed by policy and coverage, a coverage's name depends on the coverage alone, so it belongs in coverage_types.
• Third normal form: no column depends on another non-key column. A customer's country depends on their region, so customers stores region_id and regions stores the country.

Reporting tables and views are often denormalised on purpose, repeating data to make queries simple and fast; the normalised tables stay the source of truth.

Indexes

An index is a separate, sorted structure that points to rows. PostgreSQL's default, the B-tree, finds a value in a few steps however large the table: a tree three or four levels deep covers millions of rows. Primary keys and unique constraints create one automatically; foreign keys do not, so a column used in joins, such as claims.policy_id, often needs one.

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'claims';