Complex Relational Database – Principles for Data Entry

insertms access

I'm new to Databases, so forgive me if this is a basic question.

I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex:

enter image description here

I understand how to insert data into a table, but what I don't understand is how one should insert data into multiple tables that rely on one another for foreign keys. To give a simplified example:

I've got three tables: Entry, Keyword, and Keyword_Priority. Every entry can have multiple keywords, so the Keyword table links back to Entry through a foreign key. And each keyword element can have multiple elements describing the priority of the keyword, so the Keyword_Priority table links back to Keyword via foreign key, as below:

enter image description here

In this simplified scenario, let's say I want to create a new entry using SQL. Where do I start? Do I:

  1. Insert into the Entry table first to create a new EntryID

  2. Then Insert into the Keyword table using the new EntryID as a Foreign Key

  3. Then Insert into the Keyword_Priority table using the Keyword's ID field as a Foreign Key

Or alternatively, should I do it the other way around …

  1. Insert into the Keyword_Priority table

  2. Insert into the Keyword table

  3. Insert into the Entry table

So to sum up, my question is:

Where should I start when inserting data into multiple tables at once? From the top-down, or from the bottom-up?

Best Answer

A foreign key relates to the primary key of another table. When the record with the foreign is entered, the corresponding record with that primary key must be present. Therefore you must always start by inserting in the tables on the 1-side of the relation (the master or parent or primary key table). Then only you can enter a record on the n-side of the relation (into the detail or child or foreign key table).

Order: 1. Entry, 2. Keyword, 3. Keyword_Priority.

If a table has several foreign keys, then the related records in all the other tables with the corresponding primary keys must be entered first. For instance, before entering a record into SenseRestricted..., the corresponding records in Sense and in Reading must have been inserted.

Related Question