We sell to trade customers who each negotiate their own prices, and those prices change. Design the schema.

Establish identity first

Before any table exists, work out what identifies a customer in the business, because that determines the shape of everything downstream. A trade wholesaler almost always already has an account code, printed on paperwork and quoted on the phone. It may also hold a VAT or tax registration number. Both are candidate keys and both are worth asking about, in these terms: does the value ever change, is it ever reissued to a different party, is it controlled by someone outside your system, and is it something you might one day be obliged to erase.

Those four questions decide the surrogate-versus-natural argument on evidence rather than on preference. An account code that the sales team occasionally corrects after a typo fails the first test. A tax number fails the third, because the issuing authority controls its format and can change it. Anything containing personal data fails the fourth. When a candidate key fails any of them, a natural primary key means that change propagating as an update through every child table that references it, under lock, forever.

So the account code is not the primary key. It is still unique, which is the part that gets forgotten:

CREATE TABLE customer (
  customer_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_code text NOT NULL UNIQUE,   -- the business identifier, still enforced
  vat_number   text UNIQUE,            -- nullable: not every account has one
  legal_name   text NOT NULL
);

Where surrogate keys are the wrong instinct

A surrogate key is not a universal rule, and applying it everywhere signals that no thinking happened. An order line has no identity of its own: it exists only inside its order, it is never referenced from anywhere else, and its position within the order is meaningful to the business. The composite key is the honest model.

CREATE TABLE order_line (
  order_id   bigint NOT NULL REFERENCES sales_order,
  line_no    integer NOT NULL,
  product_id bigint NOT NULL REFERENCES product,
  quantity   numeric(12,3) NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL,   -- captured, not looked up later
  PRIMARY KEY (order_id, line_no)
);

The test is whether anything outside the aggregate needs to point at the row. If nothing does, an extra identity column buys you nothing and costs you an index.

Modelling a price that changes

The naive schema puts unit_price on a customer_product row and updates it when a price is renegotiated. That destroys the previous value, so the answer to "what was this customer's price in March" becomes unavailable the moment anyone changes it. A price is not an attribute of the relationship; it is an agreement that held over an interval. Model the interval:

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- lets = and && share one index

CREATE TABLE customer_price (
  customer_price_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customer,
  product_id   bigint NOT NULL REFERENCES product,
  unit_price   numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  valid_period daterange NOT NULL,
  -- Two overlapping agreements for the same pair cannot be inserted at all,
  -- so a concurrent renegotiation fails loudly instead of creating ambiguity.
  EXCLUDE USING gist (customer_id WITH =, product_id WITH =, valid_period WITH &&)
);

Retrieval becomes an as-of question rather than a lookup, and it is the same query whether you are quoting today or reprinting a document from two years ago:

SELECT unit_price
FROM   customer_price
WHERE  customer_id = $1
  AND  product_id  = $2
  AND  valid_period @> $3::date;   -- @> is "contains"

Use a half-open interval, [start, end), so consecutive agreements meet exactly without overlapping and without a one-day hole. daterange literals default to that form, which is one of the reasons to prefer a range type over two loose date columns: the semantics are in the type rather than in a convention nobody wrote down. An open-ended current price uses an unbounded upper bound rather than a sentinel date like 9999-12-31.

The exclusion constraint deserves emphasis because it is the difference between a schema that documents an intent and a schema that enforces one. Without it, two salespeople saving a renegotiation in the same minute produce two overlapping rows, the as-of query starts returning two prices for one date, and the bug surfaces weeks later in a customer's invoice.

The thing that has to be copied, not referenced

An order line stores unit_price as a plain column, and that looks like denormalisation until you name what the row is. Reference data records what is true now. A transaction records what was agreed then. If the order line only referenced customer_price_id, a later correction to that price agreement would retroactively change the value of orders already invoiced and paid. Copying the price at capture is not duplication, because the two values mean different things: one is the current agreement, the other is evidence of a completed negotiation. The same reasoning applies to the tax rate, the currency and the delivery address on a despatch note.

Where this falls apart in review

The recited answer is "always use a surrogate primary key", and the candidate who stops there almost always omits the UNIQUE on account_code. That is strictly worse than the natural key they were avoiding: the surrogate makes every row distinct by construction, so the database will now happily hold two customer rows with the same account code, and the duplicate is discovered by a finance team reconciling statements rather than by a constraint violation. A surrogate key relocates the uniqueness guarantee; it does not supply one. If you introduce one, you owe a unique constraint on whatever the business actually uses to tell two rows apart.

The two decisions in this schema are the same decision seen twice: identity belongs to the thing that is stable, and history belongs in the data rather than in an update you hope nobody makes.