by J.R. Powell

Created on: December 18, 2011   Last Updated: April 22, 2012

A relational database is a database which has tables which can be related to one another.

For example, you could have a stock control database into which you record the orders which you receive. To record the orders you could have one table to hold the main details of the order (eg. which customer it is for, etc.), and another table to record each item they have ordered within that order. For example:

Order_Header

1 Order_Nr

• Customer_Nr

Order_Date

Despatched?

etc.

Order_Item

1 Order_Nr

2 Item_Nr

• Product_Code

Quantity_Ordered

etc.

In the above example, the two tables are related to one another - the Order_Header table is the parent table of Order_Item. There can be many Order Items for each Order. This is known as a one-to-many relationship.

  • Primary and Secondary keys

In the above example, the order number (Order_Nr) is the primary key to the Order_Header table. This means that the Order_Nr uniquely identifies a record in this table (since only one order can have this order number).  In the The Order_Item table above, the order number and item number make up the primary key.  Both of these keys together will uniquely identify a record in the Order_Item table.  A secondary key allows an additional way of accessing the records in the table by use of an index (see Index section below).

  • Foreign keys

The foreign key is a key of one table stored in another table, for example:

Order_Header

1 Order_Nr

• Customer_Nr

Order_Date

Despatched?

Customer

1 Customer_Nr

Customer_Name

In the above example, the customer number (Customer_Nr) in the order header is the foreign key. It is the primary key to the Customer table, and uniquely identifies a record in the customer table.

  • Indexes

An index can be used to speed up certain reads from database tables, but like with a database table, will increase the size of the database. It will also slightly slow down writes to the table being indexed. In the above example tables, if you wanted to read a record from the Order_Header table and you know the order number, there would be no need for a separate index, since the order number is the primary key to the table. If however, you wanted a report or on-screen display of all un-despatched orders, sorted by Order_Date, you might want to create an index to save reading many database records - which could take a long time.

eg.

Order_Despatched_Index

1 Despatched?

2 Order_Date

Adding the above index should allow you to speed up reads of all un-dispatched (or dispatched) orders, and give the results in Order_Date order.

  • Advantages of Relational Databases

A relational database reduces data duplication (which can mean less user input), and can help with data integrity and security. They can also allow more efficient storage and retrieval of data, as well as allowing for data access and updating by multiple users.

Learn more about this author, J.R. Powell.
Click here to send this author comments or questions.