Tip:
Highlight text to annotate it
X
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.