HP 3000 Manuals HP 3000 Manuals
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: * What is the data type and size? * For character data, should values be fixed or variable length? * Are null values allowed? Data Type and Size Some possible data types in ALLBASE/SQL are: CHAR Fixed length character string. VARCHAR Variable length character string. INTEGER Four-byte integer values. SMALLINT Two-byte integer values. DECIMAL Fixed-point packed decimal values. FLOAT Floating point numbers. DATE Date values. TIME Clock time values. DATETIME Timestamp values (date and time combined). INTERVAL Elapsed time values. 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. Character Data 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. NULL Values 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.
NOTE Key columns should never be NULL.
Modifying the Table Design 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. Table Descriptions When you have made decisions on these issues, you can create a description of each table, as follows: Albums Table ----------------------------------------------------------------------------------------------------- - Table Name - Column Name - Data Type - NOT NULL - Size - ----------------------------------------------------------------------------------------------------- - Albums - AlbumCode - INTEGER - NOT NULL - 4 bytes - - - AlbumTitle - VARCHAR(40) - - 40 bytes - - - Medium - CHAR(2) - - 2 bytes - - - AlbumCost - DECIMAL(6,2) - - 4 bytes - - - RecordingCo - CHAR(10) - NOT NULL - 10 bytes - - - DateRecorded - DATE - - 16 bytes - - - MfgCode - VARCHAR(40) - - 40 bytes - - - Comment - VARCHAR(80) - - 80 bytes - ----------------------------------------------------------------------------------------------------- Titles Table ----------------------------------------------------------------------------------------------------- - Table Name - Column Name - Data Type - NOT NULL - Size - ----------------------------------------------------------------------------------------------------- - Selections - AlbumCode - INTEGER - NOT NULL - 4 bytes - - - Selection - VARCHAR(40) - NOT NULL - 40 bytes - - - ComposerName - VARCHAR(16) - - 16 bytes - - - Timing - INTERVAL - - 16 bytes - - - Performers - VARCHAR(40) - - 40 bytes - - - Comment - VARCHAR(80) - - 80 bytes - ----------------------------------------------------------------------------------------------------- Composers Table ----------------------------------------------------------------------------------------------------- - Table Name - Column Name - Data Type - NOT NULL - Size - ----------------------------------------------------------------------------------------------------- - Composers - ComposerName - VARCHAR(16) - NOT NULL - 16 bytes - - - Birth - DATE - - 16 bytes - - - Death - DATE - - 16 bytes - - - Birthplace - VARCHAR(40) - - 40 bytes - - - Comment - VARCHAR(80) - - 80 bytes - ----------------------------------------------------------------------------------------------------- Log Table ----------------------------------------------------------------------------------------------------- - Table Name - Column Name - Data Type - NOT NULL - Size - ----------------------------------------------------------------------------------------------------- - Log - AlbumCode - INTEGER - NOT NULL - 4 bytes - - - SelectionName - VARCHAR(40) - NOT NULL - 40 bytes - - - StartTime - DATETIME - NOT NULL - 16 bytes - - - EndTime - DATETIME - NOT NULL - 16 bytes - - - Announcer - VARCHAR(40) - NOT NULL - 40 bytes - ----------------------------------------------------------------------------------------------------- The sizes shown here are taken from the ALLBASE/SQL Database Administration Guide chapter on "Physical Design."