Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?
Answer
Short answer
Decide by which attributes are filtered or constrained, not by how many exist. Anything a query filters, sorts or validates on becomes a real typed column; the open-ended remainder goes in a JSONB document with a GIN index, and entity-attribute-value is the fallback that costs you every guarantee.
Keep data modelling explicit in the answer because that is the concept the interviewer is actually trying to test. A good data modelling explanation names the trade-off, the failure mode, and the evidence you would use before choosing. Use data modelling once more at the decision point so the answer reads as judgement rather than a detached example.
Keep data modelling explicit in the answer because that is the concept the interviewer is actually trying to test. A good data modelling explanation names the trade-off, the failure mode, and the evidence you would use before choosing.
Was this answer helpful?
Likely follow-ups
- A merchant needs the same attribute in three languages and two unit systems. What changes?
- How would you enforce that every product in the Batteries category has a voltage?
- Why does the planner estimate rows badly for a jsonb containment predicate, and what can you do about it?
- When is a separate table per category the right answer after all?
Related questions
- Every table in this schema soft-deletes with a deleted_at column. A year in, what has that cost you?mediumAlso on data-modelling and schema-design5 min
- We sell to trade customers who each negotiate their own prices, and those prices change. Design the schema.hardAlso on data-modelling and schema-design5 min
- A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?hardAlso on postgresql5 min
- Three queries hit the same table with different filters. What composite indexes do you build, and how do you order the columns?hardAlso on postgresql6 min