Representing a business in tables.
WHAT A DATA MODEL IS
A representation of entities, their attributes, and the relationships between them.
WHAT THE LEVELS ARE
- Conceptual: what things exist and how they relate
- Logical: tables, columns and keys, independent of any platform
- Physical: how it is actually implemented
WHAT NORMALISATION ACHIEVES
Each fact stored once, so it cannot become inconsistent.
WHAT THE FORMS REQUIRE, PRACTICALLY
Atomic values, not lists in a column Attributes depending on the whole key Attributes depending on nothing but the key
WHY OPERATIONAL SYSTEMS NORMALISE
Updates touch one place, and inconsistency is prevented structurally.
WHY ANALYTICAL SYSTEMS FREQUENTLY DO NOT
Reading requires joining many tables, which is slow and awkward for analysis.
WHAT DENORMALISATION MEANS
Deliberately duplicating data to make reading simpler and faster.
WHEN IT IS APPROPRIATE
Analytical models, where data is written once and read repeatedly.
WHAT A KEY IS
A value uniquely identifying a row.
WHAT A NATURAL KEY IS
An identifier from the business itself.
WHAT A SURROGATE KEY IS
A meaningless generated identifier.
WHAT TO PREFER IN A WAREHOUSE
Surrogate keys, since natural ones change.
