If a table has several candidate keys, you must make a choice. Always choose the one that consists of the smallest number of columns as the primary key. This simplifies the process of joining tables for, among other things, SELECT statements.

If this criterion does not lead to a good solution for some reason, choose the column that contains “internal” values. By “internal,” we mean values for which your own organization is the owner. For example, do not choose passport number as a key because the government is the owner of this number.

If this criterion does not work, choose the primary key that uses the least amount of storage space—for example, a CHAR(5) column instead of a VARCHAR(30) column.

This guideline is often referred to in the literature as the Boyce-Codd normal form; see, among others, [DATE95].

This is the first time we have used the term determinant, so it needs to be explained. Column A is a determinant of column B if, for each different value in A there is, at most, one associated value in B. The PLAYERNO column in the PLAYERS table, for example, is a determinant of all other columns in the table. A determinant can consist of more than one column.

Imagine that the column DETER in table T is a determinant of column C. The following SELECT statement will never return a result (work it out for yourself):

SELECT   DETER
FROM     T
GROUP BY DETER
HAVING   COUNT(DISTINCT C) > 1

Here is an example of a table design that does not follow the second guideline:

PLAYERNO  NAME       TEAMNO  DIVISION
--------  ---------  ------  --------
       6  Parmenter  1       first
      44  Baker      1       first
      27  Collins    2       second
     104  Moorman    2       second

The PLAYERNO column is the primary key. Thus, the table follows the first guideline. The determinant of the NAME column is PLAYERNO. This is also true for TEAMNO and DIVISION; every PLAYERNO belongs to, at most, one TEAMNO and one DIVISION. However, TEAMNO is also a determinant of DIVISION because every TEAMNO has, at most, one associated DIVISION. TEAMNO, then, is a determinant but not a candidate key. The conclusion is that the table does not follow the second guideline.

The most significant disadvantage of a table that does not comply with the second guideline is that certain “facts” are recorded several times. In the previous example, the fact that team 1 plays in the first division is recorded more than once. This situation leads to more complex updating requirements and inefficient use of storage space and, finally, to inconsistent data.

Columns that contain the same type of data, have the same meaning, and are placed in the same table form what is known a repeating group. Imagine that each player must register the first name of his or her children (to participate in a club Christmas party). A possible table structure for the CHILDREN table is:

PLAYERNO  CNAME1   CNAME2  CNAME3
--------  -------  ------  ------
       6  Milly    Diana   Judy
      44  ?        ?       ?
      83  William  Jimmy   ?

The columns CNAME1, CNAME2, and CNAME3 form a repeating group. They contain the same type of data—that is, the name of a child. They also have the same significance; they are children belonging to one player. Up to three children may be registered for each player. What are the consequences of such a design, for example, for SELECT and UPDATE statements? An example of each follows.

SELECT   PLAYERNO, 0
FROM     CHILDREN
WHERE    CNAME1 IS NULL
AND      CNAME2 IS NULL
AND      CNAME3 IS NULL
UNION
SELECT   PLAYERNO, 1
FROM     CHILDREN
WHERE    CNAME1 IS NOT NULL
AND      CNAME2 IS NULL
AND      CNAME3 IS NULL
UNION
SELECT   PLAYERNO, 2
FROM     CHILDREN
WHERE    CNAME1 IS NOT NULL
AND      CNAME2 IS NOT NULL
AND      CNAME3 IS NULL
UNION
SELECT   PLAYERNO, 3
FROM     CHILDREN
WHERE    CNAME1 IS NOT NULL
AND      CNAME2 IS NOT NULL
AND      CNAME3 IS NOT NULL
UNION
SELECT   PLAYERNO, 0
FROM     PLAYERS
WHERE    PLAYERNO NOT IN
        (SELECT   PLAYERNO
         FROM     CHILDREN)

PLAYERNO
-----------------
       6        3
      44        0
      83        2
       2        0
      27        0
       :        :

SELECT   *
FROM     CHILDREN
WHERE    PLAYERNO = 6

UPDATE    CHILDREN
SET       CNAME2 = 'Diane'
WHERE     PLAYERNO = 6

PLAYERNO  CNAME
--------  -------
       6  Milly
       6  Diana
       6  Judy
      83  William
      83  Jimmy

SELECT   PLAYERNO, COUNT(*)
FROM     PLAYERS LEFT OUTER JOIN CHILDREN
         ON PLAYERS.PLAYERNO = CHILDREN.PLAYERNO
GROUP BY PLAYERNO

UPDATE   CHILDREN
SET      CNAME = 'Diane'
WHERE    PLAYERNO = 6
AND      CNAME = 'Diana'

It seems that repeating groups often give rise to more complex statements and, therefore, should be avoided as much as possible. Moreover, the number of columns in a repeating group must be adapted to the maximum number of values possible. In the previous example, this might be as many as ten children! This applies to every row, of course, and puts excessive pressure on storage space.

The PLAYERS table consists of 13 columns. Some of these could be joined to make a single column. For example, the columns STREET, HOUSENO, and TOWN could be merged into one column called ADDRESS. This can make some SELECT statements easier to formulate.

SELECT   ADDRESS
FROM     PLAYERS
WHERE    PLAYERNO = 44

ADDRESS
--------------------------
23 Lewis Street, Inglewood

On the other hand, other questions are very difficult to formulate:

These disadvantages are specific to this example but provide a clear basis for generalization.

In SELECT and UPDATE statements, columns are compared with one another. The columns WON and LOST are compared in the following statement:

SELECT   MATCHNO
FROM     MATCHES
WHERE    WON - 2 > LOST

In contrast to this example, the following statement compares two columns that come from different tables:

SELECT   NAME
FROM     PLAYERS INNER JOIN PENALTIES
         ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO

Two data types are the same if the data type (CHAR, SMALLINT, and so on) and the defined length are the same.

If you perform calculations on values in a column, the column must be defined with a numeric data type. Otherwise, the calculations might be impossible. Sometimes, you might be inclined to give a numeric data type to a column that records specific codes (with no intrinsic significance) that consist entirely of digits (for example, the league number). The advantage of a numeric column is that it requires little storage space. Coding systems, on the other hand, change frequently. During the design, all the codes might be numeric, but the question is whether that will always be the case. Conversions from numeric to alphanumeric values, should the need arise, are not simple. Therefore, define a column as numeric only if this is necessary for calculations.

Independent of the data type, a column must have a length defined that allows its longest value to be accommodated. Work out how long the longest value is for each column. Do not assume that the largest value is one of your existing values; think also about possible future values.

For columns with an alphanumeric data type, there is a choice between values with a variable length and values with a fixed length. Variable length has been designed to save storage space. In the first instance, it often appears to be the best choice, but be careful: It is not always as good as it seems. Columns with a variable length have two disadvantages. First, for each value in such a column, the length of the particular value is recorded (this is not visible for users). This, of course, uses extra storage space. Second, in SELECT and UPDATE statements, columns with a variable length perform more slowly than columns with a fixed length. The general guideline is to use alphanumeric data types with a variable length only if, on average, there would be at least 15 unused positions for most values.

Never use the NULL value in an artificial manner. Never use it to represent something other than an unknown value because working with NULL values in calculations can be tricky, especially in conjunction with aggregation functions (see Chapter 9, “SELECT Statement: SELECT Clause and Aggregation Functions”).

Closing remark: Many more factors and guidelines can influence the design of a database structure than we have mentioned here. Nevertheless, we have discussed the most important ones in this chapter.