Concepts / Designing Many-to-Many Relationships with Junction Tables

Designing Many-to-Many Relationships with Junction Tables

Many-to-many data loading requires parsing JSON and distributing the data across two entity tables and one junction table.

  • Programming
Interactive lab

Try it: Normalising into Lookup Tables

How normalisation removes repeated text: each distinct artist or album is stored once in a lookup table with an integer id (INSERT OR IGNORE, then SELECT id), the other table stores only that id as a foreign key, and a JOIN rebuilds the original rows. A roster preset links users and courses through a junction table.

How it works

  1. For each flat row, INSERT OR IGNORE the repeated text into its lookup table; the UNIQUE column makes a second copy be ignored.
  2. SELECT the text's id back — new or existing.
  3. Store that integer id (a foreign key) in the next table instead of the text.
  4. Many-to-many: a junction table stores (user_id, course_id) pairs plus the role; INSERT OR REPLACE keeps one row per pair.
  5. A JOIN that follows the ids rebuilds the same rows, while each distinct string is now stored once.

Default run (27 steps): A flat table: 6 rows × 3 text columns = 18 stored strings, many of them repeated. Split it into Artist, Album and Track. … Done: 18 stored strings became 12 (179 → 130 characters). The JOIN rebuilt 6 rows, the same rows as the flat table.

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Follows the Python for Everybody tracks and roster programs on at most 8 rows; the stored-string count ignores the (small) space integers take.

Educational simulation

Loading the simulation…

One Record, Three Destinations

A many-to-many data load is not a matter of placing one JSON record into one table. The input must be parsed and distributed across two entity tables and one junction table. The two entity tables hold the related kinds of records, while the junction table records which pair is connected.

JSON recordentity values andrelationshipEntity table Afirst primary keyEntity table Bsecond primary keyJunction tablepair of primary keysextract first entityextract second entityuse first primary keyuse second primary key
How does one JSON record move from parsed input into two entity rows and then into a linking row in the junction table?

The Three-Table Model

The design separates two responsibilities. Each entity table stores records of one related type. The junction table stores the relationship between one record from the first entity table and one record from the second entity table. A JSON record therefore supplies information for both entity tables and also supplies the connection that belongs in the junction table.

Definition

A junction table is the table used to represent the relationship between two entity tables by storing the primary keys that identify the related records.

Entity A recordprimary key A1Junction rowA1 plus B1Entity B recordprimary key B1Entity A recordprimary key A2Junction rowA1 plus B2Entity B recordprimary key B2Junction rowA2 plus B1participates inparticipates inparticipates inlinks tolinks tolinks to
How can one record in either entity table relate to many records in the other table through the junction table?

Resolving Entity Keys

The junction table needs primary keys, not merely the descriptive entity values extracted from JSON. For each entity, first ensure that its record exists with INSERT OR IGNORE. Then immediately use SELECT to retrieve that record's primary key. Repeat this for the second entity before inserting the relationship.

JSON inputentity A and entity BEntity table Aprimary key AEntity table Bprimary key BJunction tableA plus B1. INSERT OR IGNORE, then SELECT2. INSERT OR IGNORE, then SELECT3. provide primary key A4. provide primary key B
How are the two entities extracted from JSON, resolved to their primary keys, and connected as a pair in the junction table?

A Complete Loading Trace

Worked example

Connecting a Pair from Parsed JSON

A parsed JSON record contains one value for entity A, one value for entity B, and the fact that they are related. Describe the database-loading sequence.

  1. 1
    Parse

    Extract the two entity values and the relationship information from the JSON record.

  2. 2
    Ensure entity A

    Apply INSERT OR IGNORE for the first entity so that a new record can be added while an existing record is handled without an error.

  3. 3
    Retrieve key A

    Use SELECT immediately to obtain the primary key for the first entity.

  4. 4
    Ensure entity B

    Apply INSERT OR IGNORE for the second entity using the same new-or-existing handling.

  5. 5
    Retrieve key B

    Use SELECT immediately to obtain the primary key for the second entity.

  6. 6
    Create the link

    Insert the pair of retrieved primary keys into the junction table.

Result

The JSON record has been distributed into two entity records and one relationship record, with the junction row connecting the two retrieved primary keys.

The important transition is from values to identifiers. JSON supplies the entity information; the entity-table operations resolve that information to primary keys; the junction insertion uses those keys to represent the relationship.

Repeat-Safe Relationship Inserts

A relationship may be encountered again while loading data. The junction table should use a unique constraint for the pair of related primary keys, and the relationship should be inserted with INSERT OR REPLACE. This prevents duplicate relationship entries and maintains idempotency: repeating the same relationship operation does not create another duplicate entry.

A1 plus B1one junction rowA1 plus B1one junction rowINSERT OR REPLACE repeats same pair
What changes when the same pair of entity IDs is inserted into the junction table a second time?

Mistakes in the Loading Sequence

  • Common mistake

    Trying to insert the relationship before resolving both entity primary keys

    What it looks like

    The loader parses both entity values but does not complete the INSERT OR IGNORE and SELECT step for each entity.

    Why it fails

    The junction table is populated by linking primary keys from the two entity tables.

    Do this instead

    Ensure each entity exists, retrieve its primary key immediately, and only then create the junction entry.

  • Common mistake

    Treating INSERT OR IGNORE as the complete operation

    What it looks like

    The loader ensures an entity record exists but never follows with SELECT.

    Why it fails

    The primary key still needs to be retrieved before it can be used in subsequent operations.

    Do this instead

    Use the INSERT OR IGNORE and SELECT pattern as a pair.

  • Common mistake

    Inserting a junction relationship without duplicate protection

    What it looks like

    The same pair of entity primary keys is loaded more than once without a unique constraint and INSERT OR REPLACE.

    Why it fails

    Repeated relationship data can create duplicate junction entries.

    Do this instead

    Use a unique constraint for the pair and INSERT OR REPLACE for the junction insertion.

  • Common mistake

    Keeping the JSON structure as the database structure

    What it looks like

    The loader treats one JSON record as though it belongs in only one table.

    Why it fails

    Many-to-many loading requires distributing the parsed data across two entity tables and one junction table.

    Do this instead

    Separate entity insertion from relationship insertion.

Practice the Trace

MEDIUM

A JSON record contains two related entity values. Write the loading sequence in six ordered actions, beginning with parsing and ending with the junction-table insertion. Include the operation used for each entity and explain when each primary key is retrieved.

Hints
  • Separate the two entity operations from the relationship operation.
  • For each entity, pair INSERT OR IGNORE with SELECT.
  • The final junction operation uses the two retrieved primary keys and must prevent duplicate relationships.
  1. 1
    A reliable many-to-many loader follows a fixed path: parse the JSON, ensure the first entity exists, retrieve its primary key, ensure the second entity exists, retrieve its primary key, and insert the pair into the junction table. INSERT OR IGNORE handles new and existing entity records without errors, while INSERT OR REPLACE combined with a unique constraint prevents duplicate relationships.

Key Takeaways

  • Many-to-many JSON loading distributes one parsed record across two entity tables and one junction table.
  • Use INSERT OR IGNORE followed immediately by SELECT for each entity so its primary key is available.
  • Populate the junction table with the primary keys from both entity tables.
  • Use a unique constraint on the relationship pair and INSERT OR REPLACE to prevent duplicate junction entries.
  • Keep entity resolution and relationship insertion as distinct steps in the loading sequence.

More concepts starting with D