Relational Database Design

Introduction

This tutorial concerns database design. Here you would learn how to design better databases rather than simply the commands you need to create a database. Essentially databases are programs that allow a user to store, search and retrieve data. A user can be a human being or a software. Only relational databases would be covered here.

Relational Model
The relational model is a set of best practices which can be used to design relational databases. A relational model describes:

1. how data should be divided into tables
2. how tables should be linked to each other

Since the introduction of the relational model, several software products supporting relational model specifications have appeared in the market. They are called Relational Database Management Systems (RDMS). They include Oracle, MySQL, PostGreSQL, Microsoft SQL server, and many others.

So what are the characteristics of a RDMS

• storage and retrieval of data
• linking of tables using keys
• data types - text, numeric, data, etc.
• data integrity
• SQL support

Primary key
Relational databases store all data in tables. A primary key is used to uniquely identify a record. A primary key is a unique number assigned to each record. A social security number or an identity card number can be thought of as a primary key as no two persons have the same numbers. Thus we can uniquely identify the person even if their names and dates of birth are exactly the same.

If we have a table called employee which stores employee information, it would be useful to use primary keys so that we don't confuse Linda working in accounting with Linda working in marketing.

Primary keys can be integers or text, however, using integers is far more efficient. Most relational databases have capabilities to automatically assign primary keys and they also make sure that there are not duplicates.

Linking tables with keys
Relational database tables are linked by keys. Suppose we have two tables called employee and department. The employee table contains data of 500 employees such as names, phone numbers, identification, etc. The department table contains data concerning the department such as budget, director, address, etc. Obviously, each employee works in a department so his data must be linked to that department. To do so, we simply add a the primary key of the department (did) in the employee table, as follows:

+--------------+   +------------+
| employee     |   | department |
+---------------   +------------+
| eid          |   | did        |
| name         |   | name       |
| phone number |   | manager    |
| did          |   | budget     |
+--------------+   +------------+

Instead if we decide to add primary key of employee (eid) to the department table, we would only be able to link one employee to the department table. This obviously is not desirable as a department would most likely have more than one employee. However, it is logical for each employee to be linked to one departement.

In the employee table 'eid' is the primary key and 'did' is the foreign key as it refers to a primary key in another table.

The relation in the tables above is called a one-to-many relationship. Each employee works in one department. Each department can have many employees. The relational model allows three kinds of relationships between data:

• one-to-many relationship
• one-to-one relationship
• many-to-many relationship

When linking tables, it is important to consider which one of the three relationships would be produced and whether it is desirable.