One-to-many and many-to-many, explained with real tables

Every relationship in a working database is one of two shapes. Learn to spot which one you're looking at and the design work is mostly done — the rest is clicking the fields into place.

A customer has many orders

The first shape, with the scene that teaches it. Nour orders a monstera in March and two pothos in June. Two orders, one customer. No order was placed by two customers.

That's one-to-many: one customer, many orders — and the asymmetry decides where the link lives. Ask, for each side, how many of the other could this row point at?

• An order's answer is one. "Which customer placed this?" has a single answer, so the order can carry it as a value: one cell, holding one customer.
• The customer's answer is many, and worse, it's open-ended. March's answer is one order; June's is three. A value that keeps growing isn't a fact you store on the row — it's a question you ask of the other table.

So the link lives on the many side. Each order points at its one customer, the way each booking points at its one guest. The "one" side stores nothing at all.

The mirror

Then how does Nour's record list her orders? By reading the same links backwards. Every order that points at Nour is her order list — collecting them needs no second copy of anything.

Tools in this category show that reading as a field: open the customer and there's Orders, holding every order that points here. It looks like data and it's worth being precise about what it is: one relationship, seen from its other end. Link an order to Nour and she has it instantly; there is no second bookkeeping step, and nothing that can disagree. The mirror also can't be edited into contradiction, because it and the link are the same fact.

Once the relationship exists, derived fields can ride it — the customer's email shown on each order, the sum of order totals shown on the customer. That's lookup vs rollup territory; the relationship underneath is what this page is about.

An order holds many products

Now the second shape. Order ORD-1041 holds a monstera and a golden pothos. The golden pothos also appears in thirty other orders. Run the question from both sides: an order's answer is many products; a product's answer is many orders.

Nobody is "the one", so the trick that placed the link on the many side has no side to choose. This is many-to-many, and in a no-code database you build it the obvious way: a link field that allows several, on either table. The order lists its plants; each plant mirrors back the orders it appears in. For tagging-shaped relationships — plants to care tags, articles to topics, people to committees — that is the whole design.

When the connection itself has facts

One more step separates a tidy many-to-many from a cramped one. Say the order needs three pothos, and the price each was sold at. Where do those facts live? Not on the product — 3 isn't a fact about pothos in general. Not on the order — it has a quantity per product. The quantity is a fact about the pairing: this product, in this order.

When the connection carries facts of its own, promote it to a table. Call it Order lines: each row links one order and one product, and carries quantity, sold-at price, a line note. The order's contents are its lines; a product's sales history is its lines. Nothing is crowded, and the sold-at price can differ from the current price — which an invoice eventually requires.

Database people call this a junction table. The name matters less than the reflex: a relationship that knows things becomes a thing. You'll recognize when you need it, because you'll catch yourself trying to write a quantity into a link.

Which shape is it

The whole page as three questions about any two tables A and B:

1. Can an A point at many Bs, and a B at many As? → many-to-many.
2. Otherwise, whichever side answers "many of the other" is where the link lives — that's one-to-many, link on the many side.
3. Does the connection itself carry facts? → give it a table, whatever shape it was.

How this works in Tabla

A linked record field is one relationship with both ends built in:

• Creating the link creates its mirror. Add a Customer link on Orders and Customers grows an Orders field in the same motion — the same relationship, readable from both tables, never maintained separately.
• Allow many is the shape switch. Off, a cell links one record — an order's customer. On, it links several — an order's plants. The mirror always allows many, because even single links pile up when read from the other end: fifty orders, one customer each, is fifty on her mirror.
• Single means single, enforced. Link an order to a new customer and it leaves the old one's mirror — the record can't be in two places when its own side links one. The picker says so before it happens.
• A junction table is nothing special — a plain table with two single links on each row, exactly as above. Lookups and rollups ride any of these links, which is where the sums and the borrowed emails come from.

The customers–orders–plants examples above are the demo shop these guides share, built exactly this way — links both directions, sums riding them. Tables, not tabs covers the design step before this one.