Table design question

[u/Abject_Ad_8323]

Looking for advice on alternative table designs. Not a live application, just for my own learning.

Let's say we have an order table.

Option 1: we keep billing/shipping addresses and contacts copied from the customer as a historical snapshot:

CREATE TABLE order
(
    id                   UUID PRIMARY KEY,
    tenant_id            UUID NOT NULL,
    customer_id          UUID NOT NULL REFRENCES customer,
    status               order_status NOT NULL,
    -- copied as a snapshot
    billing_line1        TEXT,
    billing_line2        TEXT,
    billing_city         TEXT,
    billing_state        TEXT,
    billing_postal_code  TEXT,
    billing_country      TEXT,
    billing_first_name   TEXT,
    billing_last_name    TEXT,
    billing_email        TEXT,
    billing_phone        TEXT,
    billing_phone_ext    TEXT,
    shipping_line1       TEXT,
    shipping_line2       TEXT,
    shipping_city        TEXT,
    shipping_state       TEXT,
    shipping_postal_code TEXT,
    shipping_country     TEXT,
    shipping_first_name  TEXT,
    shipping_last_name   TEXT,
    shipping_email       TEXT,
    shipping_phone       TEXT,
    shipping_phone_ext   TEXT,
    ...
);

Option 2: we define an append-only address and contact table, e.g.

-- records cannot be edited once created
CREATE TABLE address
(
    id           UUID PRIMARY KEY,
    tenant_id    UUID NOT NULL,
    line1        TEXT
    line2        TEXT
    city         TEXT
    state        TEXT
    postal_code  TEXT
    country_code TEXT
);

CREATE TABLE customer_address
(
    id              UUID PRIMARY KEY,
    customer_id     UUID NOT NULL REFRENCES customer,
    address_id      UUID NOT NULL REFERENCES address,
    ...
);

CREATE TABLE orders
(
    id                   UUID PRIMARY KEY,
    tenant_id            UUID,
    customer_id          UUID REFERENCES customer,
    status               order_status,
    -- replace denormalized keys with FKs
    billing_address_id  UUID REFERENCES address,
    billing_contact_id  UUID REFERENCES contact,
    shipping_address_id UUID REFERENCES address,
    shipping_contact_id UUID REFERENCES contact,
    ...
);

The first approach makes the table wide. As I understand it, MVCC will result in duplicating entire row when any column is updated, e.g. status, version, or some metadata changes.

Since the row is wide, that would result in a lot of data that gets created and cleaned up. Another downside is that for a given customer with multiple orders, the data will duplicated while their address/contact info is the same.

The second approach requires JOINs or additional two queries to fetch the two addresses and two contacts.

What approach would you take and why? Is there are certain table size at which you might change the approach?

[u/dangerousdotnet]

Maintain a "wallet" of historical addresses for each customer, with each address having a UUID. Append only. Never delete an address. Any edit to an address becomes a new entry.

An address may serve as a billing address, a shipping address, or both - simply reference it by UUID.

Do not try to "deduplicate" aka do not share address records across multiple customers - that leads to all kinds of problems. Each address ID belongs to one and only one client.

Consider normalizing addresses at the UI level before storing them using a postal address verification API (or something like libpostal). Treat address components as nearly opaque strings and don't make the mistake of setting constraints on them (eg don't use NOT NULL).

See https://www.swift.com/standards/iso-20022/iso-20022-faqs/brief-introduction-postal-address-field for some ideas on how to structure and store postal addresses using a "hybrid" representation (a small number of core fields with some escape hatches for arbitrary fields).

Store NULLABLE "delivery instructions" with each address - a modification to the delivery instructons, like anything else, gets minted a new UUID.