3 – Databases – linked tables and keys

3 – Databases – linked tables and keys

We use databases to store much of the data
in our world. Flat-file databases are simple databases that store information about uncomplicated
things: the contacts in your phone, the birthdays of your friends, the list computer games that
you own. This is a Bob and he runs a sweet shop. His
customers can choose confectionery from a large menu that is displayed in his shop.
On his menu are five different categories of sweets and there are several sweets in
each category. For example, Caramel is a category of sweets and sweets this category are Milk
Caramels, Orange Toffee, Tablet and English Toffee. Marshmallow is also a category and
sweets in this category are Flumps, Coconut Mallows, Marshmallow Mushrooms and Mallow
Cables. So, Bob could store all the sweets in a database.
If he did this in a flat file database it would look like this. As you can see, Bob’s
information about sweets is laid out in a table. In fact, another name for a group of
records about one particular thing is a table. To make using the table easier, Bob has added
a key field. A key is the name given to a special column
in a database where the values in the column can be used to identify particular records.
Sometimes we can use a field that is already in our database as the key but at other times
we have to add a new column to store the key field. Bob has added a column called “SweetID”
to his database. In this case, the value of “SweetID” is a different number for every
record in the database. This type of key, where each record has a unique value, is called
a primary key. But there are some problems with Bob’s database. People keep asking Bob what a “Caramel”
is so he decides to change the category name to “Toffee”. In this flat-file database,
the Category “Caramel” is stored four times which means that Bob has to edit the
database four times to make just this one small change. That is a bit annoying! There is another problem as well. Bob makes
one sweet in the Fudge category, his Mint Chocolate Fudge, but it doesn’t sell very
well so he decides to stop selling it and to make a different kind. If he deletes the
record about Mint Chocolate Fudge from his database he will also delete the “Fudge”
category. Oops, that’s not good. And there is one last problem. Bob has decided
to offer a new category of sweet: Candy. He wants to add the “Candy” category to his
database but there is a problem. Because he hasn’t yet made any candy there is no sweet
information to go into the database record. This means that he can’t add the new category
until he has made a sweet in that category. Bob has an idea! Instead of storing everything in one table
he could use two. He could put the sweets in one table and the categories in another.
But how would he know which sweet belonged to which category? If he added a key to the category table and
also added the same key to the sweets table he could use the key values from one table
to link to the other one. This is an example of linked tables. We can
find which records are linked together by using the value of the shared key column.
If Bob wants to know which Category “Flumps” belong to he can find the record for “Flumps”,
read the value of the “CategoryID” and then search the Category table for the same
value. This tells him the category he was looking for is “Marshmallow”. Remember the key columns with unique values
in them, in this case “SweetID” in the “Sweet” Table and “CategoryID” in
“Category” table, are called primary keys. The “CategoryID” in the “Sweet” table
doesn’t contain unique values. This special column is used to link the “Sweet” and
“Category” tables together. The “CategoryID” in the “Sweet” table is still a key column
but we call this type of key a “foreign key”. Linked tables are a feature of what we call
“Relational Databases”. These are databases that are made up of two or more linked tables.
Remember a link isn’t a magic piece of string. The “link” between two tables exists when
they have foreign key and primary key values that are the same. It is these equal values
that create the link.

Danny Hutson

15 thoughts on “3 – Databases – linked tables and keys

  1. He could just edit the name of the sweet instead of deleting it and getting rid of the fudge category.

  2. This is so good. This series is wonderful. Exactly what I was looking for. Thank you! (I'd like to see more stuff like this if you're still making it! It reminds me of Khan Academy)

Leave a Reply

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