Database Reference
In-Depth Information
The result of this query for the ORDER_ITEM values we have been using is:
In this case, there are no NULL values, but if there were, we would know how many, and
then we could use a SELECT * statement to find the data of any row that has a null value.
When creating a database from existing data, if you try to define a column that has null
values as the primary key, the DBMS will generate an error message. You will have to remove
the nulls before creating the primary key. Also, you can tell the DBMS that a given column is
not allowed to have null values, and when you import the data, if any row has a null value in
that column, the DBMS will generate an error message. The particulars depend on the DBMS
in use. See Chapter 10A for Microsoft SQL Server 2012, Chapter 10B for Oracle Database 11
g
Release 2, and Chapter 10C for MySQL 5.6. You should form the habit of checking for null val-
ues in all foreign keys. Any row with a null foreign key will not participate in the relationship.
That may or may not be appropriate; you will need to ask the users to find out. Also, null values
can be problematic when joining tables together. You will learn how to deal with this problem
in Chapter 7.
The General-Purpose Remarks Column
The general-purpose remarks column problem is common, serious, and very difficult to solve.
Columns with names such as Remarks, Comments, and Notes often contain important data
that are stored in an inconsistent, verbal, and verbose manner. Learn to be wary of columns
with any such names.
To see why, consider customer data for a company that sells expensive items such as air-
planes, rare cars, boats, or paintings. In a typical setting, someone has used a spreadsheet to
track customer data. That person used a spreadsheet not because it was the best tool for such
a problem, but rather because he or she had a spreadsheet program and knew how to use it
(although perhaps “
thought
he or she knew how to use it” would be more accurate).
The typical spreadsheet has columns like LastName, FirstName, Email, Phone, Address,
and so forth. It almost always includes a column entitled Remarks, Comments, Notes, or
something similar. The problem is that needed data are usually buried in such columns and
nearly impossible to dig out. Suppose you want to create a database for a customer contact ap-
plication for an airplane broker. Assume your design contains the two tables:
CONTACT (
ContactID
, ContactLastName, ContactFirstName,
Address, . . . {other data}, Remarks,
AirplaneModelID
)
AIRPLANE_MODEL (
AirplaneModelID
, AirplaneModelName,
AirplaneModelDescription, . . . {other airplane model data})
where CONTACT.AirplaneModelID is a foreign key to AIRPLANE_MODEL.AirplaneModelID.
You want to use this relationship to determine who owns, has owned, or is interested in buying
a particular model of airplane.
In the typical situation, the data for the foreign key have been recorded in the Remarks col-
umn. If you read the Remarks column data in CONTACT, you will find entries like: 'Wants to buy
a Piper Seneca II', 'Owner of a Piper Seneca II', and 'Possible buyer for a turbo Seneca'. All three of
these rows should have a value of AirplaneModelID (the foreign key in CONTACT) that equals the
value of AIRPLANE_MODEL.AirplaneModelID for the AirplaneModelName of 'Piper Seneca II',
but without the proper foreign key value, you would pull your hair out making that determination.
Another problem with general-purpose remarks columns is that they are used incon-
sistently and contain multiple data items. One user may have used the column to store the
name of the spouse of the contact, another may have used it to store airplane models as just
described, and a third may have used it to store the date the customer was last contacted. Or
the same user may have used it for all three purposes at different times!
Search WWH ::
Custom Search