Common data modeling questions
- Design a schema for an e-commerce store, a library or a ride-sharing app.
- Explain normalisation up to third normal form, and when you'd denormalise.
- How do you model a many-to-many relationship?
- What's the difference between a star schema and a snowflake schema?
- What is a slowly changing dimension, and how do you implement type 2?
- How would you model this in a document or key-value store?
- Which indexes would you add for these queries, and what do they cost?
Normalisation in one paragraph
First normal form: atomic values, no repeating groups. Second: no partial dependency on part of a composite key. Third: no transitive dependency — non-key columns depend only on the key. Normalising removes duplication and update anomalies. Denormalising — copying a customer's name onto orders, or keeping a running total — speeds reads at the cost of keeping copies consistent. In interviews, normalise first, then denormalise deliberately for named query patterns.
Keys and relationships
| Concept | Interview-ready summary |
|---|---|
| Primary key | Uniquely identifies a row; surrogate IDs are stable, natural keys can change |
| Foreign key | Enforces that a reference points at an existing row |
| One-to-many | Foreign key on the many side (order.customer_id) |
| Many-to-many | A join table with two foreign keys (enrolment: student_id, course_id) |
| Composite key | Multiple columns together are unique, common in join tables |
| Unique constraint | Enforces business rules, such as one account per email |
Dimensional modelling for analytics
Analytical schemas centre on fact tables — events or measurements such as sales lines, with numeric measures and foreign keys — surrounded by dimension tables describing who, what, where and when. A star schema keeps dimensions denormalised and one join away; a snowflake schema normalises dimensions further, saving space at the cost of more joins. Declare the grain of each fact table explicitly (“one row per order line”), because most analytics bugs come from mixing grains.
Slowly changing dimensions
When a dimension attribute changes — a customer moves city — type 1 overwrites it, losing history; type 2 adds a new row with effective-from and effective-to dates and a current flag, preserving history so old sales still report against the old city; type 3 keeps a previous-value column for limited history. Type 2 is the classic interview answer, with a surrogate key so facts point at the version that was true at the time.
NoSQL modelling
Document and key-value stores flip the process: start from access patterns, not entities. List the queries, then shape documents or keys so each common query reads one item or one partition. Embed data that is read together and bounded in size; reference data that is shared or unbounded. In wide-column and key-value stores, the partition key decides scalability, so avoid keys that concentrate traffic, such as the current date.
Worked example: a ride-sharing app
Core tables: riders, drivers, vehicles (driver_id), trips (rider_id, driver_id, vehicle_id, status, requested_at, pickup and drop-off locations, fare), payments (trip_id, amount, method, status) and ratings (trip_id, rater, score). Status as an enumerated column with a history table of status changes for auditing. Index trips by rider_id and requested_at for trip history, and by driver_id for driver earnings. For analytics, a trips fact table at one row per trip with dimensions for date, city, rider and driver. Discuss storing fare components separately so pricing changes don't rewrite history. See SQL interview questions.
Practising
Model three scenarios aloud — a transactional app, an analytics mart and a NoSQL access-pattern design — in about 30 minutes each, and write the three most important queries against each schema. A free AI interview assistant such as CoPilot Interview can play the interviewer and change requirements mid-design. See also DBMS interview questions.
Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly. Everything else on this page applies whichever tool you use, or none.
Frequently asked questions
What questions are asked in a data modeling interview?
Expect to design a schema for a scenario, explain normalisation and denormalisation, model many-to-many relationships, compare star and snowflake schemas, implement slowly changing dimensions and choose indexes.
What is the difference between a star and snowflake schema?
A star schema keeps dimension tables denormalised, one join from the fact table. A snowflake schema normalises dimensions into further tables, saving space but adding joins.
What is a type 2 slowly changing dimension?
A technique that preserves history by adding a new dimension row when an attribute changes, with effective dates and a current flag, so past facts still point to the version valid at the time.
How is NoSQL data modeling different?
You start from access patterns and shape documents or keys so common queries hit a single item or partition, embedding data read together and choosing partition keys that spread load.
Practise on real interview questions
Run 145 real interview questions across 10 tracks, answer out loud and see a structured answer to compare with yours. Free, no signup.
Try a practice question