Relational Database Design

Relational Database Design - Overview

Let's imagine a car valeting service wanted to set up a database. It would probably want to store some of the following information.

name
address
telephone number
car make
car model
car registration
date of last visit

Problem

Some customers may own 2 cars - in a normal "flat-file database" you would have to type in their name and address twice. Even worse, if a customer moved house you would have to change the address twice! This is something we want to avoid
Storing repeating information (e.g. the customer's name and address) takes up extra disk space and makes searching the database take more time

Example flat-file database. Can you see that Joyce Ball has 2 cars? It is a waste of time keying in and a waste of computer disk space storing her name, address and phone number twice

Here is the solution - design a relational database!

Step 1 - what entities are there in the database?

An entity is a thing or person. From the above list it should be clear that there are 2 entities in this database:

a list of customers
a list of cars

Step 2

We can create a database with 2 tables - one containing customers, one containing cars.

Customer Table	Car Table
Name	Make
Address	Model
Phone Number	Registration
	Date of last visit

Step 3

How will the database know which customer owns which car? Answer: it does not!

We will have to add an extra field to the tables to create a common field between the databases.

This field must be unique - no customer or car can have the same identity (you could have 2 customers with the same name or 2 Ford Focus 1.6 Zetecs in Blue). These special unique fields are known as the primary key
Number codes are the most common way to create a primary key
Look at the example below - each customer has a unique code and each car has a unique code
In the example below BOTH tables contain customer code - this is what allows the join between the tables

Customer Table	Car Table
Customer ID This is a unique number that can identify each customer. The PRIMARY KEY	Car Code This is a unique number that can identify each car. The PRIMARY KEY
Name	Customer ID - this is the FOREIGN or COMMON key that joins the tables (joins with Customer ID in the Customer table)
Address	Make
Phone Number	Model
	Registration
	Date of last visit

The fields in bold are the primary keys.
Customer code in the car table is the field it has in common with the Customer Table. This is known as the common key. It is necessary so that one 1 table knows which information it is joined to in another table and how the records relate (this is why these databases are known are relational databases)
Customer code in the car table is known as the foreign key (it links to a primary key in another table)

This would give us 2 tables like this

Note that Joyce Ball's full details are only included once. In the table below her Customer Code appears twice.

QUESTION: Which field could be used as a primary key other than Car Code? Why?

Step 4 - join the tables together

In Microsoft Access it is possible to join the tables together. Look at how the Customer ID numbers have been joined together. The 1 next to CustomerID means that there can only be 1 unique customer ID. The oo symbol next to CustomerID in the Car Table means that there can be more than 1 identical customer ID e.g. Joyce Ball's code appears twice

Step 5 - review the results

Below is the customer table. Note that each person has a + sign next to their ID. Joyce Ball's has been clicked on to reveal information about her cars. This is only possible because of creating a relationship between the tables.

Back to index