hardDesignConceptMidSeniorStaff
We sell to trade customers who each negotiate their own prices, and those prices change. Design the schema.
5 min readUpdated 2026-08-15
Answer
Short answer
Give every entity a surrogate primary key but keep a unique constraint on the business identifier, and model a negotiated price as a row with a validity period rather than a mutable column - then copy the agreed price onto the order line so history cannot be rewritten.
Was this answer helpful?
Likely follow-ups
- How would you answer "what did this customer pay for this product on 3 March last year" against your schema?
- What breaks if two people negotiate overlapping price agreements at the same time?
- Where do you put the discount if the negotiation is a percentage off list rather than a fixed price?
- How would you migrate a live system that currently has price as a single column on the product row?
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 constraints5 min
- Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?hardAlso on data-modelling and schema-design7 min
- How do you design the tools an agent calls?hardAlso on schema-design5 min
- How does the reserve figure your systems produce end up changing next year's rates, and where does that loop break?hardAlso on data-modelling6 min