The join table is a real table

An earlier post was about seeing across a relationship — turning a foreign key into a name, a count, a set of chips. This one is about the other side of the join: the table in the middle. Because in SchemaStack a many-to-many isn't a hidden convenience. It's a real table, and treating it as one is what makes the hard cases work.

What a many-to-many actually creates

Link books and tags and SchemaStack runs the DDL you'd write by hand: a CREATE TABLE with a composite primary key over the two foreign keys, and two foreign-key constraints, both ON DELETE CASCADE, each wrapped in an existence check so re-running is safe. The table's name is derived alphabetically — books and tags give books_tags whichever side you started from — so adding the relationship from either direction lands on the same table rather than making two.

And if the join table already exists — you imported a schema that had one — SchemaStack uses it in place, reading the real foreign-key column names off the junction rather than inventing new ones. No migration, no duplicate.

The junction that carries its own data

The interesting case is the join table that isn't pure. A book_authors_link with role, royalty_percent, contribution_date on it is an association entity — the relationship has attributes. Plenty of tools flatten that into a hidden link and lose the extra columns. SchemaStack keeps it as a first-class table you can open, and lets you drill through it: from books, through the link, to the author's name on the far side. The path is assembled for you, and the extra columns on the link stay yours to query.

Three ways to edit the far side

A relationship cell isn't one behaviour. It's three, and which you get depends on how the relationship is owned:

• Reference — a picker that changes the foreign key. You select a different related row; the key is repointed.
• Owned — an inline editor for the related row's fields. The key stays put; you edit the values on the row it points at. This is only offered when every hop of the path is owned, because editing through a relationship you don't own would be editing someone else's table by accident.
• Association — a picker that can also create a new related row on the spot.

A many-to-many edits as a checklist of chips, and each toggle saves on its own — there's no separate apply step, because a link either exists or it doesn't.

What it doesn't do (yet)

• Self-referential relationships now work — with two honest edges. A table that points at itself — categories with a parent category, an org chart's manager column — can now be created from the picker (the view's own table is offered first, labelled "this table"), and multi-hop lookups like parent.parent.name resolve: the cycle guard that refused them turned out never to be protecting anything, since the walk consumes a stored finite path and the depth cap is the real bound. The edges: a self many-to-many is still not offered (the join table would derive two identical column names), and drill-down in the picker stops at five hops — deeper chains exist in your data, the picker just won't build a column that far.
• Deleting a many-to-many column leaves the join table behind. The relationship column is virtual, so removing it removes the link from your view, not the table from your database. Dropping the table is a separate, deliberate act.
• A join table's cascade rule isn't yours to set. The two foreign keys are created ON DELETE CASCADE and stay that way; the per-column delete/update actions you can configure on an ordinary foreign key aren't offered for the junction's own keys.
• Many-to-many keys can now be UUIDs — on PostgreSQL. This used to be a trap: a UUID-keyed table showed its chips fine and then threw the moment you opened the picker or toggled one, because every id went through an integer parser. Ids are now coerced to whatever the key actually is — number, UUID, or text — on both sides of the join, with tests running real UUID-keyed tables through the picker and the writes. The honest remainder: on MySQL a UUID stored as CHAR(36) hasn't been exercised and may still refuse the bind, and each chip toggle is still one round-trip per id rather than a batch.
• The REST API expands relationships but won't write through them. You can ask for related rows inline, up to a depth of three by default; you set a relationship by writing the foreign key, not by nesting a new object inside the payload.
• A search across a collection needs something searchable on the far side. Filtering through a to-many relationship searches the target's text columns; if it has none, the filter is refused with a 400 naming the column. It used to be dropped instead, which left a filter written to narrow the result quietly returning every row. Asking whether the collection is empty at all works either way.

The relationships guide covers the picker and the edit modes; the same relationships surface as expand in the REST API you didn't write, so the joins you set up in the grid are reachable without rebuilding them by hand.