What principles should I follow when entering data into a complex relational database?

Question

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?

Answer 1

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.

• Lou: Thanks, both your explanation and @Lennart's were clear and insightful! I would love to be able to accept them both.

Answer 2

Some general advice. In the image, I guess Keyword_Priority is dependent on Keyword which is dependent on Entry. You, therefore, need to start with Entry, then Keyword, etc.

From a modelling perspective, something is something regardless of where in the model you use it. The entry looks pretty decent:

CREATE TABLE entry
( EntryId ... NOT NULL PRIMARY KEY
, ...
);

For keyword, something like:

CREATE TABLE keyword
( KeywordId ... NOT NULL PRIMARY KEY
, ...
, EntryId ... REFERENCES entry (EntryId)
)

I.e. don't use Id, it is too vague. The attribute is an id of something in particular. Also, note how that the attribute EntryId is named similar as in entry.

I won't delve into the question of surrogate keys since I believe there are limitations in ms-access (at least it used to be that way), that prevents composite foreign keys.

• Chessbrain: Access can actually have composite keys, he simply has to select both foreign keys and mark them as PK.