Normalisation and deliberate denormalisation, keys, constraints, and schema evolution under load.
3 questions in Databases & SQL.
Soft deletion is a modelling decision rather than a convenience: it silently breaks uniqueness, leaves foreign keys pointing at rows that are logically gone, and adds a predicate every future query has to remember. Model the lifecycle explicitly instead.
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.
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.