Quick answer: When two schemas reference each other in a way that implies a repeated relationship, the modeling layer infers a many-to-many link table that holds the two foreign keys. It stays in the Inferred layer rather than being treated as a confirmed table, and when the cardinality is ambiguous it raises an open question instead of guessing.

A one-to-many relationship fits naturally on one side, with a foreign key pointing back. A many-to-many relationship does not, because each side can relate to many rows on the other, and forcing the relationship into either table would require storing a list of keys rather than a clean relational design.

A link table resolves that. It carries one foreign key to each side, and each pair becomes a row, which keeps the design relational and easy to query.

What the inference looks like

Element Role
Left foreign key Points to the first entity
Right foreign key Points to the second entity
Composite key Enforces the unique pair
Indexes Support the queries from both sides

The link table is proposed from the repeated references in the schemas rather than a hand-named table, and it is kept separate from the observed tables.

How the inference is made

  1. Read the schemas and identify the entities that reference each other.
  2. Check whether the relationship repeats on both sides.
  3. Propose a link table with the two foreign keys.
  4. Add the composite key and the indexes.
  5. Keep it labeled as inferred and raise an open question if the cardinality is unclear.

The composite key matters because it stops the same pair from being recorded twice, and the indexes keep the lookups from either entity efficient.

The classic example is students and courses. A student enrolls in many courses, and a course has many students, so neither table can hold a single foreign key to the other. The inferred link table carries one key to the student and one to the course, and the composite key keeps the same enrollment from appearing twice. A query from either side stays simple: follow the link to read the related rows. If the real rule is closer to one-to-many, such as a course that belongs to only one department, the open question catches it before the join table is built, and the reviewer can confirm the cardinality rather than inherit a wrong join.

Why the inference stays labeled

An inferred link table is lower confidence than a table observed in the live database. The schemas may imply a join that the real design handles differently, or the cardinality may be closer to one-to-many than many-to-many, and promoting the inference would hide that uncertainty.

Keeping it in the Inferred layer is the honest move. The reviewer can confirm the relationship against the business logic, resolve the open question, and only then move the table into the proposed plan. This is the same boundary that keeps a static signal from being written as a guarantee.

The ER diagram reflects the node state, and the generated DDL for the link table is still never executed by the modeling package. The deployment script stays idempotent and forward-only.

Powerduck keeps the inferred link tables and their open questions visible rather than quietly emitting them as confirmed facts.

Frequently asked questions

How is a link table different from a foreign key? A foreign key fits a one-to-many relationship on one side, while a link table carries two foreign keys to model many-to-many.

What stops a duplicate pair? A composite key on the two foreign keys enforces the unique pair.

Is an inferred link table ready to deploy? Not by itself. It stays inferred until the relationship is confirmed and the open question is resolved.

The bottom line

A many-to-many relationship is best modeled by a join table with two foreign keys and a composite key, inferred from the repeated references rather than forced into one entity. It stays in the Inferred layer, and ambiguous cardinality raises an open question rather than a guess. Confirm the relationship before the table enters the plan, and remember the generated DDL is never executed for you. The inference is a proposal, not a confirmed table. Read more in the API data model guide.