Tutorials‎ > ‎

Basics Of Database Table Design - Pt6

posted 9 Nov 2012, 04:16 by Alistair Hamilton   [ updated 19 Nov 2012, 06:36 ]
Basics of Database Table Design
Now that you are familiar with the first two normal forms of database table design, as well as the the different types of key or primary field, we move on to the last of the normalisation levels that will be discussed in this series.

If you recall from part 2 of the series, the Third Normal Form is defined as "eliminates functional dependencies on non-key fields by putting them into a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key".

In essence, each record in a table should have fields that describe attributes of that particular record or entity. For example, we may have an employee table which has fields that indicate which department the employee works in and where that department is located. We've already seen that we should remove the department field and stick it in its own table. However, to illustrate the point, I've modified the Employee as shown below:

EmployeeID

Employee

Department

Department Location

EMP001

John Smith

Software Engineering

Building 1

EMP002

Jill Jones

Software Engineering

Building 1

EMP003

Thomas Harding

Software Engineering

Building 1

EMP004

Lee Walsh

Human Resources

Building 2

EMP005

Joe Jordan

IT

Building 1

EMP006

Neil Watson

Web Design

Building 1

EMP007

John Smith

Human Resources

Building 2



The Third Normal Form specifies that each non-key field should describe the entity (or contribute to its description) identified by the key field. In the case of the Employee table above, while we may have a need to find out which building Lee Walsh works in, that building identity has nothing to do with the employee himself. The same argument goes for the department.

The Department Location is a functionally dependent on the Department field which of course is a non-key field and therefore needs to be removed to its own table. The field make no contribution to the data held that specifically defines or describes an employee.

All the time, one must think of each table in terms of just that table and the data that the table is designed to hold.

Summary

The main thrust of this series has been to give non-technical users of desktop database applications such as MS Access and LibreOffice the basic tools for designing their database tables in order to ensure the integrity of the data being stored.

The process of normalisation is fundamental to that and, although only the first three of five normalisation levels have been discussed, getting your database tables into the Third Normal Form will ensure that your database design will be able to handle changes and additions while giving you accurate query results when asked.

While the normalisation process described in this series may seem complicated, it is essential for the integrity of the databases you are designing. It is worth the effort in getting to grips with the techniques involved.

If I was to summarise the techniques described and give a general rule of thumb that will cover most situations it is this:

Break all your tables down into their smallest component parts that make sense and create separate tables for each of them.

The series is almost finished now. There's one thing to cover before the end. Something that will allow you to design your database tables quickly and which normalises the tables almost automatically as you do so. Look out for the last part when I'll introduce Entity Relationship Diagrams and explain why they are one of the first tools you should use even before you go anywhere near your database application software.