ER Modeling & Relational Mapping

It becomes a third table holding one row per pair — student key and course key — with a foreign key to each parent and a primary key over the pair. Neither parent can hold the link because each side has many partners, and a column stores only one value.

It becomes a child table holding the owner's key plus one value per row, with a foreign key to the owner and a key over (owner key, value). Repeated columns cap the count and scatter one fact; a delimited string loses typing, constraints and lookups.

Give the table a parent_id column that is a foreign key back to its own primary key. Roots have NULL parent_id. One row per node stores one edge. To read a whole subtree you walk the parent_id chain repeatedly, which in standard SQL means a recursive query.

Use a third table holding one foreign key to each side, keyed on the pair of them. A single foreign key column stores one value per row, so it cannot record many links from the same row.

The foreign key goes on the many side: orders holds customer_id referencing customers. If every child must have a parent, declare it NOT NULL; if it may stand alone, allow NULL. Index that column.

Cardinality is the maximum: how many instances on the other side one instance may relate to (1:1, 1:N, M:N). Optionality is the minimum: whether an instance must participate at all (zero or at least one). Together they read as a (min, max) pair per side.

Each strong entity becomes a table keyed by its own key. Composite attributes become their component columns. Multivalued attributes become child tables. Weak entities become tables keyed by owner key plus discriminator. One-to-many relationships become a foreign key on the many side; many-to-many becomes its own table.

Three options. Single table: one table with all columns plus a type discriminator; subtype columns must be nullable. Class table: a parent table for shared columns and one child table per subtype sharing the parent's primary key; reads join. Concrete table per subtype: one independent table per subtype with the shared columns repeated; no join, but no single place holding all payments.

Simple: one atomic value (birth date). Composite: a named group of simpler parts (full name = first + last). Multivalued: several values at once for one instance (phone numbers). Derived: computable from other stored data (age from birth date).

A weak entity cannot be identified by its own attributes: it is identified only together with its owner, through an identifying relationship, using a partial key that is unique only within that owner. It also depends on the owner for existence.

The attribute follows wherever the relationship landed. For one-to-many it becomes a column on the many-side table, next to the foreign key. For many-to-many it becomes a column in the junction table. For one-to-one it goes on whichever side holds the foreign key.

It becomes its own table whose primary key is the owner's primary key plus the weak entity's discriminator (for example invoice_id plus line_no). The owner-key columns are also a foreign key to the owner, declared not null because the dependency is mandatory.

A closure table is a separate table holding one row for every ancestor-descendant pair, including each node paired with itself, usually with the distance between them. Any ancestor or descendant query becomes one indexed lookup. Writes cost many rows: inserting a node adds one row per ancestor, and moving a subtree rewrites all pairs crossing the moved boundary.

Composite primary key on both foreign keys is the default: it blocks duplicate pairs and indexes the pair. Add a second index on the reversed order for the other direction. Use a surrogate id only when the link itself is referenced or may repeat.

Either share the primary key — the child's primary key is also a foreign key to the parent — or put a unique foreign key on one side. Split only for optional, bulky, rarely read, or separately secured attributes.

Use a ternary relationship when a fact is only meaningful as a triple — supplier supplies part to project — and cannot be reconstructed from the three pairs. Use separate binary relationships when each pair is an independent fact. Ternary asserts more; binaries assert less.

Parent pointers: cheapest writes, traversal needs recursion. Materialized path: stores the ancestor chain in a string, fast downward prefix reads, rewrites the whole subtree on a move. Nested sets: left/right numbers make subtree reads one range scan but almost any insert renumbers much of the table. Pairs table: fast both directions, expensive writes and O(n×depth) storage. Read-heavy, edit-rarely favors path or pairs.

Measure first: which queries are polymorphic, which are subtype-specific, how sparse the columns are, and how often subtypes are added. Restructure to a parent plus child tables when subtype columns dominate and integrity matters. Migrate by backfilling child tables from the wide table, dual-writing, cutting reads over, then dropping columns. Polymorphic reads gain a join; subtype reads get narrower and better-indexed.

They belong on the junction row, because they depend on both sides and on neither alone. Once the same pair can recur over time, add validity dates to the key or a surrogate key, and the link becomes an entity with its own lifecycle.

It becomes one table holding all three participants' primary keys as foreign keys, plus the relationship's own attributes. The default primary key is all three key columns together; cardinality constraints can narrow it to a subset, and repetition over time adds a component.