Relational Database Design

Most popular database management systems are relational systems, and are usually referred to as Relational Database Management Systems (RDBMS). What this means is that their databases can contain multiple tables, some (or all) of which are related to each other.

For example, consider the following screenshot:

In this example, the database has 20 tables. Each table serves a specific purpose. This enables us to organize our data much better. It can also help us with theintegrity of our data.

Using the example above, the Individual table can hold data that is strictly about the individual. The City table can hold a list of all cities. If we want to know which city each individual lives, we could store a "pointer" in the Individual table to that city in the City table.

The above example demonstrates the relationship between the Individual table and the City table. The individuals in the "Individual" table live in cities that are defined in the "City" table. Therefore, we can cross-reference each "Individual" record with a "City" record.

How Does This Work?

Firstly, in the City table, each record has a unique identifier. A unique identifier is a value that is unique to each record. This identifier can be as simple as an incrementing number. So, in our City table, the first record has a number of 1, the second record has a number of 2, and so on.

Secondly, when entering each individual into the Individual table, instead of writing out the full city name in that table, we only need to add the city's unique identifier. In this case, the unique identifier is a number, so we enter this number into the "CityId" column of the "Individual" table.

The following screenshots demonstrate this:

So, by looking at both tables, we can determine that Homer lives in Sydney, Barney lives in Cairns, and both Ozzy and Fred live in Osaka. At this stage, nobody in our database lives in Queenstown or Dunedin.

Primary Keys and Foreign Keys

Primary keys and foreign keys are terms that you will become very familiar with when designing databases. These terms describe what role each of the columns play in their relationship with each other.

The column that contains the unique identifier is referred to as the Primary Key. So, in our City table, the primary key is the CityId column.

A foreign key is the column in the other table that points to the primary key. Therefore, the CityId column in the Individual table is a foreign key to the CityId column in the City table.