Tutorials‎ > ‎

Basics Of Database Table Design - Pt5

posted 21 Oct 2012, 04:54 by Alistair Hamilton   [ updated 19 Nov 2012, 06:35 ]
Basics of database table design
Before continuing the discussion on optimising database tables through normalisation, I'm going to take a slight detour in this episode.

I mentioned in earlier discussions the term 'key field'. Indeed, in the definition of the various normalised forms introduced in part two of the series, the idea of a key field is specifically referenced in those definitions.

So what exactly is a key field?

Put simply, the key field is the field within a database record that makes that record unique within the database table in which it is held. It can be made up of a single field or a number of fields that , when considered together are unique (though not necessarily unique on their own).

As ever, things are best explained with an example so let's consider the Employee table we came across in previous episodes.

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



The key field is used to identify each record within a table. By definition, the contents of the key field cannot have duplicate entries. They must be unique.

In the employees table as it currently stands, the obvious field to use as the key field would be the 'Employees' field. At the moment, it contains unique entries. No employee name is repeated. Therein lies the problem with using this field as the key.

What happens if the company employs a new member of staff with the name John Smith? With the 'Employees' field set as the key field the details for the new member of staff could not be entered into the table without violating the uniqueness of the key field. Indeed, most database applications wouldn't allow the creation of a new record under such circumstances.

One of the solutions to this would be to use BOTH the 'Employees' and 'Home Address' fields and create what is known as a JOINT KEY.

In this case, each field can have repeating content, but when the two fields are considered as a pair, no pair has the same joint content as you can see in the updated table below where the new employee has been added at the bottom:

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

John Smith

73 Alderbank, Elsewhere



However, this solution is not without potential problems.

Although our two employees named 'John Smith' are adequately catered for using a joint key made up of the 'Employees' and 'Home Address' fields, the table still has a problem if, in the perhaps unlikely event, that two people, with the same name living at the same address, are employed by the firm. Sounds unlikely perhaps, but the possibility exists for a father and son for example to be hired by the firm.

Although such a scenario may be unusual, it is better to design the table to take this possibility into account rather than wait for the issue to cause a problem in the future. In the case of our employees table, the obvious thing to do would be to insert a new field that would hold each employee's ID number and use that as the key field.

Note that many database applications have a mechanism where an auto-incrementing field can be used as a key field. Personally, I do not like using them as it makes it very easy for, what is effectively, duplicate records to be inserted into the table. The database software would treat them as unique records because of the auto-incremented field.

There is another term that you may come across when designing databases. A FOREIGN KEY is a field in a table that contains the contents from the key field of a related table. It is NOT a key field in and of itself but is used to set up relationships between tables. This will be the topic of conversation in a later episode.

From this you can hopefully see why defining the key fields within your tables is an important step in the design process. It has a direct influence on the normalisation process. If you recall from part 2 of the series, The definition of the 3rd Normal Form is :
  • Third Normal Form – 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.

This will be discussed in the next part of the series.


Until next time...


<< Basics of Database Table Design - Pt4