Normalization reduces redundant facts and update anomalies. Denormalization can accelerate reads but creates maintenance and consistency obligations.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Store a customer’s address once, unless an order must preserve the historical shipping address. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Identify each fact’s meaning
Current customer address and historical shipping address are distinct facts.
Step 2: Normalize shared current data
Avoid accidental duplicates that must always be updated together.
Step 3: Preserve deliberate snapshots
Store order-time address when changing the customer later must not rewrite history.
Worked scenario
Store a customer’s address once, unless an order must preserve the historical shipping address.
A customer moves after receiving an order. Joining the order only to the current address makes its old shipping record appear to change. An order-time snapshot preserves the business event, while a current-address reference remains appropriate for a new checkout. Repetition here has deliberate meaning.
Common mistake
Calling every repeated field a design flaw ignores snapshot requirements.
Verify the behavior
Change customer details and verify historical orders retain the intended values.
Interview exercise
Choose an order address model.
Answer and reasoning
Distinguish a reference to current customer data from a historical business snapshot that must not change retroactively.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.