Tutorials‎ > ‎

Basics Of Database Table Design - Pt4

posted 9 Oct 2012, 05:45 by Alistair Hamilton   [ updated 21 Oct 2012, 04:57 ]
Basic Database Table Design - Pt4
In the last episode, Basics Of Database Table Design – Pt3, we looked at the first of the normal forms. This eliminated repeating columns of information within the database and subsequently removed a number of issues that limited the table design.

Now we move on the the second of or normalisation procedures where we eliminate fields from each table that have nothing to do (or are only loosely linked) with the objects or records that the table is holding. Before we do that however, we need to expand the somewhat simplistic tables that have been used so far.

Let us consider an expanded Employees table used earlier:

Employees

Home Address

Job Function

Department

John Smith

5 Main Street, Sometown

Developer

Software Eng

Jill Jones

22 The Glebe, Sometown

Developer

Software Eng

Thomas Harding

1A High Street, Elsewhere

Analyst

Software Eng

Lee Walsh

10 Station Road, Mytown

HR Manager

Human Resources

Joe Jordan

19 Bank Drive, Mytown

System Admin

IT

Neil Watson

15 Highgrove, Thistown

Developer

Web Design


Second Normal Form

If you recall from part 2, getting our tables into second normal form involves the removal of fields that are not related to, or are only partially related to, the information that the table contains.

In the example table above, the information being held is supposed to be about the employees. Each field in the table should be directly related to each corresponding record.

Clearly, the Home Address field is related to the Employees field, but what about the Job Function and the Department fields? What if we included another field in the table that holds the Employees' salary?

This is where you may start to find some ambiguity in the interpretation of the data being held within the table. You may find it easier to look at it from the point of view of the field concerned. For example, consider the Department field. 

While it is obvious that an employee will work in a particular department, each department itself does not have anything to do with the definition of what an employee is within the database. Its inclusion within the table above brings nothing extra to the information stored about each employee.

In addition, because each department may have numerous employees working within it, listing each department in the table above results in that department name being listed in numerous locations/records. This is always a recipe for user input errors and so on. If a department name changes for example, it would have to be changed in any number of records within this table.

In this case, it is better to remove the Department field and stick it in its own table where other information can be held that specifically defines each department.

A similar argument can be put forward for the Job Function field.

As mentioned earlier, The interpretation of this normalisation rule can be somewhat open when considering whether a field should be removed to its own table. This is where the specification process comes in. Reference to the design objectives will help you out here.

For example, the idea of including an employee's salary in the table above may seem quite reasonable. However, what happens if you also want to include pay grades and so on? At that point it must be argued that such remuneration information should be removed.

After removing these fields, the expanded Employees table is now in Second Normal Form.

Employees

Home Address

John Smith

5 Main Street, Sometown

Jill Jones

22 The Glebe, Sometown

Thomas Harding

1A High Street, Elsewhere

Lee Walsh

10 Station Road, Mytown

Joe Jordan

19 Bank Drive, Mytown

Neil Watson

15 Highgrove, Thistown



In summary, the Second Normal Form involves the removal of fields that do not had any direct relationship with the information being held by a table. Each field within a table must have something to do with the other fields of that corresponding record within the table. More specifically, it should contain information that is pertinent to the Key Field – the record's unique identifier.

Although the Third Normal Form is the next step in this normalisation process, we'll be taking a slight detour in the next instalment to explain what we mean by Key Field and the issues that arise from it.