Normalisation is the schema design process that turns a flat, repetitive list into a set of related tables. A schema is simply the design of the tables, their columns and the links between them.
Normalisation minimises data duplication and enforces data integrity. Database professionals define it through a series of formal levels called normal forms, but for practical purposes it comes down to four rules:
- Separate each entity into its own table.
- Separate each discrete attribute into its own column.
- Uniquely identify each entity instance (row) with a primary key.
- Use foreign key columns to link related entities.
Consider a sales spreadsheet where every line repeats the customer’s name and full postal address and the product’s name and price, and where a single cell holds both the street and the city. Normalising it produces separate customer, product, order and line item tables, each attribute in its own column. Figure 3.1 shows the result, with primary and foreign keys linking the tables.
The benefits follow directly from the rules:
- No duplication. Each customer is stored as one row in the customer table, so the address is stored once. Changing a customer’s address means changing a single row, not hunting down every order line that repeated it.
- Correct data types. Splitting attributes into their own columns lets each one be constrained to an appropriate type: prices must be decimals, quantities must be integers.
- Useful granularity for queries. With the city in its own column rather than buried in an address string, it is easy to filter customers who live in a particular city.
- Integrity. Orders refer to customers by key, and the database can enforce referential integrity so that no order points to a missing customer.
| Characteristic | Unnormalised list (one wide table) | Normalised schema |
|---|---|---|
| Customer address stored | Once per order line | Once, in the customer row |
| Changing an address | Edit every repeated copy | Edit one row |
| Mixed values in one field | Common (street and city together) | Avoided: one attribute per column |
| Data type control | Weak | Each column has its own type |
| Linking related data | Repetition | Primary and foreign keys |
Denormalisation for analytics
Normalisation suits transactional systems, where many small writes must keep data consistent. Analytical stores often take the opposite approach on purpose. In dimensional models used for data warehouses, denormalisation means storing precomputed, redundant data; dimension tables are almost always denormalised (a product dimension might hold the product, its subcategory and its category in one table) because the improved query performance and usability outweigh the cost of the extra storage. Data warehouse design is covered in Chapter 7, Large-scale analytics: ingestion, analytical stores, Microsoft Fabric and Azure Databricks.
Common trap: Believing normalisation exists mainly to make queries faster - Microsoft Learn defines it as a design process that minimises duplication and enforces integrity; analytical models frequently denormalise precisely to make queries faster and simpler.