|
|
|
|
|
|
|
|
Up and Running with ALLBASE/SQL: HP 3000 and HP 9000 Computer Systems > Chapter 2 Looking at Data
Creating the Table Design |
|
The next step is to define the characteristics of each column you have defined. For each potential column value, you need to answer the following questions:
Some possible data types in ALLBASE/SQL are:
Decide whether you wish to use numeric or alphanumeric (character) data types. If a column value needs to participate in arithmetic operations, it should be either a numeric or date/time data type. Make sure that your data types are consistent from table to table where columns are to be joined. For example, when you want to look up all the selections for a specific album title, the AlbumCode column in the Albums table must be consistent with the AlbumCode column in the Selections table. If it is not, you may not get all the data you expect. In the case of character data, decide whether the type should be fixed length (CHAR) or variable length (VARCHAR). When a character column will contain values of uniform size, such as two-character alphabetic codes, use CHAR. If the size of values is not expected to be uniform, use a VARCHAR type, and specify the expected maximum size. Consider whether or not information will be available when deciding whether to permit NULL values. A NULL value is the absence of data for a specific column. For example, you might permit NULL values in the ComposerName column of the Selections table, because a selection may be anonymous; but you should not permit the ComposerName column in the Composers table to be NULL.
One further consideration: Is all the data within a particular entity accessed at the same time? If not, it may be wise to consider subdividing as you convert the entity into a table description. You might move some of the information in the Albums table to a different table if it is not used very often. A related consideration: Is some of the data from two or more tables always accessed together? In this case, consider combining two tables into one. For example, you might include AlbumCode and AlbumTitle in the Selections table if these data items are always included with other data about selections. The formal term for modifying the table design by examining the relationships among columns is called normalization, which is described in the "Logical Design" chapter of the ALLBASE/SQL Database Administration Guide. Also, refer to the list of references at the end of this chapter. When you have made decisions on these issues, you can create a description of each table, as follows: Table 2-3 Albums Table
Table 2-4 Titles Table
Table 2-5 Composers Table
Table 2-6 Log Table
The sizes shown here are taken from the ALLBASE/SQL Database Administration Guide chapter on "Physical Design." |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||
|
|
|
|||||||||
|
|||||||||
