Parent Child Tables

In the Introduction to Keys blog we talked about foreign keys. Foreign keys are keys that reference a primary key in another table. Foreign keys are what allows us to store data over multiple tables without having incorrect data. Let’s look at an example to see how a foreign key works.

User ID Name
2032Caleb
2312Bill
1220Clayor
Sale IDBuyer ID Item
3242032A new bicycle

Here you can see that the Buyer ID attribute is connected to the User ID attribute. With referential integrity, we can make it so that we can not have a buyer who is not also in the users table! This connection is known as a foreign key constraint. A foreign keyreferences the primary key of another table! In this case, the primary key is the user ID and the foreign key is the Buyer ID.

I have shown this table multiple times throughout this blog series, but now that we are learning about foreign keys we can study things in more depth.

From these tables you can learn that Buyer ID is a foreign key that points back to the primary key of another table, specifically the User Id column.

Using foreign key constraints, we can prevent our database from having incorrect data. Let’s say for some some reason we used these tables instead:

User ID Name
2032Caleb D Curry
2312Bill
1220Clayor
Sale IDBuyer Item
324Caleb D CurryA new bicycle

In a perfect world, these tables would work. But this is a fallen world and we must go through the efforts to protect our database structure. Using this table as an example, if Caleb D Curry changed his name to Cleatus, we would have this:

User ID Name
2032Cleatus
2312Bill
1220Clayor
Sale IDBuyer Item
324Caleb D CurryA new bicycle

Now we have it so that Cleatus is the name of the user ID, but in the table with the sale we have it as Caleb D Curry. This is incorrect data and brings the integrity of our database into question. With a foreign key, the name stays correct.

We can still get the same table structure of Sale ID, Buyer, and Item using views and joins:

Here you can see that the foreign key keeps eveything connected. Although having multiple tables is more complex, it simplifies the data that we are required to store and maintain. We can generate simplified views (the bottom table).

With this foreign key connection, whenever a table is updated all in the generated view stays correct. We generate this view taking the name from the Users table rather than the Sales table. In the above picture, if somebody changed their name it would still be correct because the name column is dynamically generated within the view coming from the Users table, not the Sales table.

If we looked at the sales table by itself, we can figure out that the Buyer ID references a user from the Users table. We can then figure out his or her name, address, phone number, or whatever else.

What does this have to do with Parents and Children?

When we have a foreign key, it becomes what is known as a child of a parent. A parent is the table that stores the primary key, A child is any table that references the parent with a foreign key. We name these tables parents and children because the child inherits values from the parent (just like children in real life).

This concept is important to understand in order to properly design a database. Go by this rule:

If updating a child table ever requires you to update the parent as well, then we have improper design.

In an earlier example, we stored a name within the Sales table without using a foreign key or anything:

Users:

User ID Name
2032Caleb D CUrry
2312Bill
1220Clayor

Sales:

Sale IDBuyer Item
324Caleb D CurryA new bicycle

The table for sales is supposed to reference the Users table. That would mean that the Users table is the parent and the Sales is the child. Unfortunately, we did not use foreign keys (which is incorrect…I’m just showing you for illustration). We designed our child table to store the name of the buyer, and then if for some reason we have to update that row, we have a design error. Changing the Buyers name in the sales table would require us to change it within the Users table, as well.

Another example of a design flaw would be trying to store all of the items a person bought within the Users table. This is because the items in themselves are their own entity and any table referencing them becomes a parent. This means that if a product is updated and we have an improper parent-child design, we must manually update all of the tables that store information about a product. If we did things correctly and used foreign keys instead, everything would stay correct.

Although it is important to have this primary key-foreign key (or parent-child) connection, there are (of course) exceptions to everything. For example, if I wanted to have a historical record of the name of the person who bought something, I would not have foreign keys. This is useful because even if the user ends up changing his or her name, the sale record stays unchanged. Another common use of this method is for addresses; many people want to have the address at the time of the order, not currently. Another method is to have a table solely for addresses and have a foreign key in the sales table pointing to the address table. This way, even if the user updates his or her address, the foreign key points to an existing address.

Conclusion

  • Primary keys are the parent.
  • Foreign keys are the child.
  • there should never be a time that requires you to manually update information due to a change in another table…Foreign keys and dynamically created views should prevent nearly all manual data integrity protection.

Further Study

Leave a Reply

Your email address will not be published. Required fields are marked *