ER, cardinality and keys

A many-to-many is not a design opinion. It is a fact you can measure, and it is only real if it holds in both directions. One product in many carts is half the claim; one cart holding many products is the other half. Run both.

SELECT p.product_id, p.product_name, m.merchant_id,
       COUNT(DISTINCT ci.cart_id) AS carts_holding_this_product
FROM cart_items ci
JOIN products  p ON p.product_id  = ci.product_id
JOIN merchants m ON m.merchant_id = p.merchant_id
GROUP BY p.product_id, p.product_name, m.merchant_id
ORDER BY carts_holding_this_product DESC
LIMIT 5;

SELECT ci.cart_id, COUNT(*) AS products_in_this_cart,
       GROUP_CONCAT(ci.product_id) AS product_ids
FROM cart_items ci
GROUP BY ci.cart_id
HAVING COUNT(*) = 3
ORDER BY ci.cart_id
LIMIT 5;

The Nimbus Audio products sit at the top of the first result because M07 is Bazaar's biggest seller for most of the quarter - a fact sessions b8 and b9 spend their time on. For now the only thing that matters is the shape: a product appears in many carts, a cart holds many products, so neither table can own the relationship.

Participation is the half of cardinality people skip, and it is the half that decides whether a column may be empty. Two sentences from Bazaar, one word apart:

• "An order must have a shopper." Mandatory. orders.user_id is required, and no order can exist without one. Making that column optional would let an order arrive with nobody to ship to, invoice, or apologise to.
• "A cart may become an order." Optional. Most carts never do - 985 orders against 2,229 carts. So orders.cart_id is allowed to be empty, because an order can also be placed by a route that never created a cart row, and a cart with no order is the normal case rather than an error.

Write both sentences down before you write the column. "Must" becomes a required column; "may" becomes an optional one. When somebody later asks why one is nullable and the other is not, the answer is a business rule, not a preference - and that is exactly the difference between a model and a schema that grew.

When you inherit a schema, the cardinality is already decided and usually undocumented. You can recover it in three queries per relationship, and it is worth doing before you trust any join:

• Is the child side really "many"? Group the child table by the foreign key and look at the maximum count. If it is always 1, somebody modelled a one-to-one as a one-to-many, and the join is safe but the design is misleading.
• Is participation mandatory? Count rows where the foreign key is empty. If the count is zero but the column is nullable, you have a rule the application enforces and the database does not - which lasts until the first backfill script.
• Is the parent side really "one"? This is the one people miss. If two parent rows can match one child through the join column, your "one-to-many" is a many-to-many in disguise and every aggregate over that join is double-counting.

That third check is the source of an enormous share of "the numbers do not match" incidents. A join that fans out is not a broken query; it is an unmodelled many-to-many being discovered at report time.

The argument for surrogate keys is usually made in the abstract. Here it is as a query: change a shopper's email address, which is the natural candidate key everyone reaches for first, and count what is still attached afterwards.

SELECT m.merchant_id, m.merchant_name, p.sku, p.product_name
FROM products p
JOIN merchants m ON m.merchant_id = p.merchant_id
WHERE m.merchant_id = 'M07'
ORDER BY p.product_id;

-- the shopper changes their email address, which happens constantly
UPDATE users SET email = 'newaddress001@example.com' WHERE user_id = 1;

SELECT u.user_id, u.email,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) AS orders_still_attached,
       (SELECT COUNT(*) FROM carts  c WHERE c.user_id = u.user_id) AS carts_still_attached
FROM users u
WHERE u.user_id = 1;

Nothing broke, and nothing had to be updated anywhere else. Now imagine email had been the primary key: that one address change becomes a cascading update across carts, orders and every downstream table that ever copied it, and any row a cascade misses becomes an orphan. Email is still UNIQUE in Bazaar, because two accounts must not share one - a uniqueness rule and a primary key are different jobs, and confusing them is what produces primary keys made of people's contact details.

The first result set shows the other side of the coin. M07 travels inside every SKU (M07-1031), and it appears on invoices and in support conversations. That is a natural key genuinely doing work, so Bazaar keeps it as the primary key of merchants rather than hiding it behind an integer nobody can read.

Session b1 asked you to write the grain of every table as a sentence. A composite primary key is that sentence, in a form the database enforces:

• cart_items (cart_id, product_id) says "one row per product per cart". Adding the same product to the same cart twice is not two rows - it is one row with a bigger quantity. The key makes that a rule instead of a convention.
• order_items (order_id, line_no) says "one row per line on an order". Note what it does not say: the same product may legitimately appear on two different lines of one order, at two different prices, because that is how a promotion or a partial substitution looks. Keying on (order_id, product_id) would forbid a real business event.

Those two keys are different on purpose, and the difference is a business decision rather than a stylistic one. Cart lines merge; order lines do not, because an order line is a historical record of what was charged and merging two of them would rewrite history.

Bazaar writes INTEGER PRIMARY KEY because SQLite is what runs in your browser tab. Two honest notes for when you ship something real:

• On Postgres, use GENERATED ALWAYS AS IDENTITY. It is the ANSI SQL way to say "the database owns this number", and unlike the older SERIAL pattern it refuses inserts that try to supply the value by hand. That refusal is the point: a surrogate key nobody outside the database may set is a surrogate key that cannot drift.
• With more than one writer, use UUIDv7. A single auto-increment counter needs a single source of truth, which is exactly what you do not have across shards, regions or offline clients. Random UUIDv4 solves collisions but destroys locality: inserts scatter across the index and reads that want "the newest orders" have to look everywhere. UUIDv7 puts a timestamp in the high bits, so identifiers stay roughly sortable by creation time and both problems go away at once.

What does not change with scale is the reasoning. The key still has to be stable, meaningless to the business unless the business genuinely owns it, and never something a human types twice. Bigger systems change the mechanism, not the question.

Take the fullest cart in Bazaar and look at what cart_items stores that neither carts nor products could. Then look at the quantity distribution across all 3,246 cart lines.

SELECT ci.cart_id, ci.product_id, p.product_name, ci.quantity, ci.added_ts
FROM cart_items ci
JOIN products p ON p.product_id = ci.product_id
WHERE ci.cart_id = (SELECT cart_id FROM cart_items
                    GROUP BY cart_id
                    ORDER BY COUNT(*) DESC, cart_id
                    LIMIT 1)
ORDER BY ci.product_id;

SELECT quantity, COUNT(*) AS cart_lines
FROM cart_items
GROUP BY quantity
ORDER BY quantity;

Try the thought experiment the second result makes concrete: to store those quantities without an associative table you would need a column per product on the cart, sixty columns wide, all empty except two or three - and a schema change every time a merchant launches something. That is what "resolve the many-to-many" is buying you.

The header carries what is true of the whole order: who placed it, when, where it ships, in what currency. The lines carry what is true of one product on it. Find orders where the quantities differ line by line and the argument makes itself.

SELECT oi.order_id, oi.line_no, oi.product_id, oi.quantity, oi.unit_price
FROM order_items oi
WHERE oi.order_id IN (SELECT order_id FROM order_items
                      GROUP BY order_id
                      HAVING COUNT(*) > 1 AND COUNT(DISTINCT quantity) > 1
                      ORDER BY order_id
                      LIMIT 3)
ORDER BY oi.order_id, oi.line_no;

SELECT COUNT(DISTINCT order_id) AS orders,
       COUNT(*)                 AS order_lines,
       ROUND(1.0 * COUNT(*) / COUNT(DISTINCT order_id), 2) AS avg_lines_per_order,
       MAX(line_no)             AS most_lines_on_one_order
FROM order_items;

985 orders, 1,434 lines, about 1.5 lines per order. That ratio is small enough to tempt somebody into flattening the two tables together "because most orders only have one line" - and it is exactly the temptation that produces the dashboard from session b1's quiz, where 1,434 lines get reported as 1,434 orders. The header and the line are two grains. Two grains, two tables.

No SQL. Pick one relationship you work with daily and write it out in four lines, exactly as Bazaar's table above does it:

• The sentence the business says, using the business's own words for both nouns.
• The cardinality: one-to-many, many-to-many, or one-to-one.
• Whether each side is optional or mandatory, and therefore which column may be empty.
• What identifies a row on each side, and whether that identifier belongs to the business or to the database.

Then check one thing in the actual data: does the join ever fan out more than you expect? If a relationship you believe is one-to-many turns out to have two matching parents for some child, you have found an unmodelled many-to-many, and every aggregate anyone has ever run across that join has been double-counting. Bring the case to b4, where the constraints that would have prevented it get declared and enforced.