Concepts / Designing Relational Database Tables

Designing Relational Database Tables

Loading relational data is not always a matter of inserting one complete source record into one 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…

Why One Record Needs Several Steps

Loading relational data is not always a matter of inserting one complete source record into one table. A source record may contribute data to multiple linked tables. When a later table needs a foreign key, the loader must first ensure that the referenced parent row exists and then retrieve that row's ID. The INSERT OR IGNORE and SELECT pattern provides these two operations in sequence.

Key point

The central idea is: insert the parent record or ignore it if it already exists, select its ID, and use that ID when inserting the related child record.

What do you think happens?

If a parent record already exists, what should the loader do before inserting a child record that refers to it?

  • Insert a duplicate parent record

  • Skip the parent step entirely and guess the foreign key

  • Ignore the duplicate insertion, then select the existing row's ID

  • Insert the child record before checking the parent

Reveal answer
Answer:

Ignore the duplicate insertion, then select the existing row's ID

INSERT OR IGNORE skips a duplicate value rather than creating another row. The following SELECT retrieves the ID of the row that can be used as the foreign key in the next insertion.

The Insert-and-Select Sequence

LoaderDatabaseParent IDChild insertionINSERT OR IGNOREduplicate skipped or row retainedSELECTreturn IDuse as foreign key
What happens in sequence when a record is inserted if it already exists, and how is its ID retrieved for the next insertion?
  1. 1
    Attempt to insert the parent record with INSERT OR IGNORE.
  2. 2
    If the record is a duplicate, the duplicate insertion is skipped instead of creating another row.
  3. 3
    Run SELECT to retrieve the ID of the relevant row, whether it was newly inserted or already existed.
  4. 4
    Use the retrieved ID as the foreign key when inserting the related child record.
  5. 5
    When the SELECT result is returned as a tuple, use cur.fetchone()[0] to extract the actual ID value.

INSERT OR IGNORE is important because a source load can encounter the same parent value more than once. The operation handles the duplicate gracefully by skipping the duplicate insertion. The SELECT that follows is still necessary: the child insertion needs the ID of the existing parent row, not merely confirmation that a duplicate was found.

Following Foreign Key Dependencies

Parent tableprovides an IDChild tablestores a foreign keyLater child tablemay depend on an earlierrowforeign key dependencyforeign key dependency
Which table must be populated first, and how do foreign key dependencies determine the order of subsequent insertions?

Insertion order follows the direction of the foreign key dependency. A parent table must be populated before a child table that references it. The parent row's ID is retrieved first, then supplied to the child insertion. If another table depends on that child, the same reasoning continues: establish the referenced row before inserting the row that points to it.

Best practice

Before writing a load operation, identify which tables provide IDs and which tables store those IDs as foreign keys. Build the load order from those dependencies: parent tables first, followed by the child tables that reference them.

Tracing a Source Record

Consider this generated scenario: a source record contains information that belongs in a parent table and also information that belongs in a child table. The child table stores a foreign key pointing to the parent. The loader cannot safely complete the child insertion until it has obtained the parent's ID.

Source recordnatural key and relateddataParent insertionINSERT OR IGNORESelected parent IDcur.fetchone()[0]Child insertionforeign key uses parent IDLinked rowsrelated tables populatedparent valuesSELECT IDforeign key valuerelated insertion
How does one source record move through the parent and child table insertions, and where is each generated ID used next?
Worked example

A Parent ID Used by a Child

A generated source record refers to one parent value and one related child value. Describe the load sequence without creating duplicate parent rows.

  1. 1
    Establish the parent

    Use INSERT OR IGNORE for the parent value. If the value is new, its row is inserted. If it already exists, the duplicate insertion is skipped.

  2. 2
    Find the parent ID

    Use SELECT to retrieve the ID of the parent row. Because cur.fetchone() returns a tuple, use cur.fetchone()[0] to obtain the ID itself.

  3. 3
    Insert the child

    Pass the retrieved parent ID into the child insertion as the foreign key. The child is inserted only after its referenced parent row has been established.

  4. 4
    Repeat for further dependencies

    If another related table depends on the child, retrieve the needed ID and continue in dependency order.

Result

The source record is loaded across linked tables without duplicating the parent value and with the child foreign key pointing to an actual parent row.

Natural keysource record valueParent rowselected recordParent IDcur.fetchone()[0]Child foreign keysame ID valueSELECT matches recordreturn IDstore as reference
How does a natural key or selected record map to the numeric ID that a related table stores as its foreign key?

Mistakes That Break the Load

  • Common mistake

    Inserting the child before the parent

    What it looks like

    The loader attempts to insert a child row before the referenced parent row has been inserted or found.

    Why it fails

    The child depends on the parent's ID through a foreign key.

    Do this instead

    Insert or ignore the parent first, retrieve its ID, and then insert the child.

  • Common mistake

    Treating INSERT OR IGNORE as the complete operation

    What it looks like

    The loader ignores a duplicate parent insertion but does not run SELECT afterward.

    Why it fails

    The child insertion still needs the existing parent's ID.

    Do this instead

    Always follow the insert-or-ignore step with a SELECT that retrieves the required ID.

  • Common mistake

    Using the entire fetch result instead of the ID

    What it looks like

    The loader passes cur.fetchone() directly as the foreign key value.

    Why it fails

    cur.fetchone() returns a tuple, while the actual ID is the first value in that tuple.

    Do this instead
    Use cur.fetchone()[0] to extract the actual ID.
  • Common mistake

    Creating duplicate parent rows for repeated source values

    What it looks like

    The loader inserts every occurrence of a parent value without duplicate handling.

    Why it fails

    Repeated source data can produce duplicate values instead of reusing the existing row.

    Do this instead

    Use INSERT OR IGNORE so duplicate values are skipped, then retrieve the existing row's ID.

Practice the Dependency Trace

MEDIUM

A source file contains repeated values that belong to a parent table and related values that belong to a child table. Describe the exact order of operations for one source record. Include what happens when the parent value already exists, how the parent ID is extracted, and where that ID is used.

Hints
  • Start with the table that provides the ID.
  • Place INSERT OR IGNORE before SELECT.
  • Remember that cur.fetchone()[0] extracts the ID from the returned tuple.
  • The child insertion uses the retrieved ID as its foreign key.
  1. 1
    Identify the parent table and the child table that references it.
  2. 2
    Place the parent insertion before the child insertion.
  3. 3
    Use INSERT OR IGNORE for the parent value.
  4. 4
    Use SELECT to retrieve the parent row's ID.
  5. 5
    Extract the ID with cur.fetchone()[0].
  6. 6
    Use that ID as the child table's foreign key.

Reliable Multi-Table Loading

  1. 1
    INSERT OR IGNORE skips duplicate values instead of creating duplicate rows.
  2. 2
    SELECT immediately after the insertion attempt retrieves the ID of the new or existing parent row.
  3. 3
    cur.fetchone()[0] extracts the actual ID from the tuple returned by SELECT.
  4. 4
    Parent tables must be populated before child tables that reference them through foreign keys.
  5. 5
    The retrieved parent ID is the bridge that carries one source record into the next related table.

Key Takeaways

  • The INSERT OR IGNORE and SELECT pattern handles duplicates and retrieves the ID needed for related insertions.
  • A parent row must be inserted or found before a child row can use its ID as a foreign key.
  • Use cur.fetchone()[0] to extract the actual ID from the tuple returned by SELECT.
  • Tracing parent insertion, ID retrieval, and child insertion makes the flow of a multi-table load understandable.

Related Topics

Related concepts

More concepts starting with D