Figure 4-9
shows the fields of the department table. department_id is an int data type, and name and
description are varchar data types.
Figure 4-9. Designing the department table
For varchar, the associated dimension??”such as in varchar(100)??”represents the maximum
length of the stored strings. We??™ll choose to have 100 characters available for the department??™s
name and 1,000 for the description. An integer record, as shown in the table, always occupies
4 bytes.
CHAPTER 4 ?– CREATING THE PRODUCT CATALOG: PART 1 74
NOT NULL Columns and Default Values
For each column of the table, you can specify whether it is allowed to be NULL. The best
and shortest definition for NULL is ???undefined.??? For example, in your department table, only
department_id and name are really required, whereas description is optional??”meaning that
you are allowed to add a new department without supplying a description for it. If you add
a new row of data without supplying a value for columns that allow nulls, NULL is automatically
supplied for them.
Especially for character data, there is a subtle difference between the NULL value and an
empty value. If you add a product with an empty string for its description, this means that you
actually set a value for its description; it??™s an empty string, not an undefined (NULL) value.
The primary key field never allows NULL values.
Pages:
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149