Normalization and analytics schemas

Store a value once, or copy it everywhere it is used?

Normalize with IDs

Normalized: a city name lives once in a cities table, and each user row stores a city_id. Denormalized: each user row stores the text "Mumbai". An ID means nothing to people, so it never has to change. If the city is renamed, you fix one row, not millions. The cost: showing a user's city needs a lookup, which in SQL is a join.

Faster writes or faster reads

• Normalized: one copy, so writes are cheap; reads need joins.
• Denormalized: reads skip joins; writes must update every copy and use more disk.

A denormalized copy is derived data. Something must keep it in step, and a crash halfway can leave copies that disagree. Normalized is usually best for app databases at small and medium scale.

Hydrating IDs at scale

A big social network's cached timeline holds only post IDs and poster IDs, not post text. At read time, the app looks up the post and the profile by ID. This is hydrating the IDs: a join done in app code. Like counts and photos change all the time, so copies would go stale and cost storage. The lookups run in parallel, and their cost does not grow with follower counts.

Star and snowflake schemas

Warehouses often use a star schema. The middle is a fact table: one row per event, like one sale. Each fact row points to dimension tables that say who, what, where, and when. A snowflake schema splits dimensions further, such as a separate brands table. Analysts often prefer the simpler star.

One big table

One big table folds the dimensions into the fact table. It uses more storage and can make queries faster. This is safe in analytics because past events rarely change.

Remember

• Normalized: write once, join to read.
• Denormalized: fast reads, many copies to keep in sync.
• Hydration is a join in app code, and it scales.
• Fact rows are events; dimensions describe them.