Jan Mikoláš
Glossary
Database normalization
Normalization reduces inconsistencies in data, not the number of columns at any cost. The aim is to give every important fact a clear source of truth until a measured need calls for a controlled copy.
Short definition
One fact should have one meaningful home.
Normalization identifies repeated data and separates it according to its actual meaning. If a manufacturer’s current name is repeated across hundreds of product records, every change risks an inconsistency. A separate manufacturer record referenced by products then keeps the current catalogue fact in one place.
It is not an instruction to split everything into as many tables as possible. A historical price, product name, or manufacturer in an order item can be a legitimate snapshot because it represents a different fact in time than the current catalogue. Denormalization for search, reporting, or caching can also be appropriate when it has a source, an update process, and stale-data monitoring.
The problem it solves
Repeated data and anomalies when data changes
A normalized model is particularly useful for transactional data that changes frequently and must remain consistent.
- an order with repeating line items instead of a list in one field
- customers, products, manufacturers, and categories as separate facts
- one manufacturer name change instead of updating hundreds of rows
- preventing the last order deletion from also removing the only product record
- a derived search index or cache with deliberate, controlled duplication
Practical model
From repetition to clear facts
An unstructured row might look like “1001 | Jana Nováková | Shoes, socks | Acme, Acme”. A normalized model separates the customer, order, items, product, manufacturer, and category into their own records. Keys then express relationships instead of a text list.
The resulting report can still join the data with an SQL query. A search index can also hold a denormalized product document, but it must be synchronised from the primary data.
How it works
From a mixed export to related tables
Text alternative: an unstructured order row is split into a customer, order, items, product, and relationships expressed through references.
- Find repetition The model identifies products stored as a list in text or a manufacturer repeated in every item.
- Describe the facts A customer, order, item, product, manufacturer, and category each have their own meaning.
- Separate repeating groups Multiple items belong in order_items, not in one comma-separated column.
- Connect identities Primary and foreign keys express relationships, while constraints protect their validity.
- Copy deliberately A derived copy for reporting, full-text search, or caching is created only with clear ownership and an update process.
Important concepts
Normal forms are a practical guide.
The following abbreviations help identify common design errors without an academic treatment.
Insertion anomaly
A new product or manufacturer cannot be stored sensibly without creating an unrelated order.
Update and deletion anomalies
A single fact must be corrected in many places, or deleting the last row unintentionally removes other important information.
Functional dependency
In simple terms, once we know a product’s identity, certain properties follow from it unambiguously. This helps distinguish a product attribute from an order item attribute.
First to third normal forms
1NF requires atomic values and does not hide repeating groups in a single value; splitting them into rows or tables is a common design outcome. 2NF requires dependence on the whole composite key, while 3NF limits unnecessary dependence of non-key data on other non-key data.
Denormalization
A deliberate copy or summary for a specific read. It should have a reason, source of truth, update strategy, and consistency checks.
Benefits and limitations
Consistency and performance should be designed together.
Benefits
- fewer places where the same fact can diverge
- easier protection of integrity through keys and constraints
- more predictable changes to a customer, product, or category
- a better foundation for a transactional system and downstream derived models
Common mistakes
- dogmatically splitting every value into a separate table
- premature denormalization without a synchronisation strategy
- confusing normalization with a database index or object-oriented design
- overwriting a historical snapshot with current catalogue data
- treating a cache or Elasticsearch as a persistent source of truth
Practical example
Splitting up an order export
A poorly designed export may store customer_name, customer_email, product_names, manufacturer_name, and several items as text in orders. When a manufacturer or customer changes, the same information diverges across multiple places, and individual items cannot be filtered or joined accurately.
A more practical model has customers, orders, order_items, products, manufacturers, and categories. order_items.unit_price can still be the correct historical snapshot because the price at the time of purchase should not disappear when the catalogue changes.
What to keep in mind
Understand the data first, then optimise.
Performance problems should be measured; copying data must be a deliberate design decision.
- distinguish a current fact from a historical snapshot
- find repeating groups and dependencies among data
- use keys and constraints for relationships owned by the database
- give denormalized copies a source, update process, and monitoring
- do not address performance by adding duplicates without measuring queries and indexes
Common questions
Normalization in practice
Does every database have to be in third normal form?
No. Normal forms are a guide for designing transactional relational data, not a mandatory ladder. The right choice depends on the meaning of the data, query types, and cost of consistency.
Is denormalization a mistake?
No, provided it is a deliberate copy for a specific read, report, or search and has a clear source for updates. Uncontrolled duplication without an owner is the mistake.
Is JSONB an alternative to normalization?
Not automatically. It helps with flexible payloads, but key relationships and rules often lose direct integrity and targeted querying.
Why store the price in the order item as well?
The order price is a historical fact about a specific transaction. The current product price can be a different value that changes later.
How I apply this principle in practice
I divide data models according to facts, not a single screen.
In e-commerce and integration solutions, I combine a relational source of truth with pragmatic derived models for search, caching, and reporting.