Databases Reference
In-Depth Information
That almost surely ought to be an
ENUM
instead of a
SET
, assuming that it can't be
both true and false at the same time.
NULL
not invented here
We wrote earlier about the benefits of avoiding
NULL
, and indeed we suggest con-
sidering alternatives when possible. Even when you do need to store a “no value”
fact in a table, you might not need to use
NULL
. Perhaps you can use zero, a special
value, or an empty string instead.
However, you can take this to extremes. Don't be too afraid of using
NULL
when
you need to represent an unknown value. In some cases, it's better to use
NULL
than
a magical constant. Selecting one value from the domain of a constrained type,
such as using −1 to represent an unknown integer, can complicate your code a lot,
introduce bugs, and just generally make a total mess out of things. Handling
NULL
isn't always easy, but it's often better than the alternative.
Here's one example we've seen pretty frequently:
CREATE TABLE ... (
dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
That bogus all-zeros value can cause lots of problems. (You can configure MySQL's
SQL_MODE
to disallow nonsense dates, which is an especially good practice for a new
application that hasn't yet created a database full of bad data.)
On a related topic, MySQL does index
NULL
s, unlike Oracle, which doesn't include
non-values in indexes.
Normalization and Denormalization
There are usually many ways to represent any given data, ranging from fully normalized
to fully denormalized and anything in between. In a normalized database, each fact is
represented once and only once. Conversely, in a denormalized database, information
is duplicated, or stored in multiple places.
If you're not familiar with normalization, you should study it. There are many good
books on the topic and resources online; here, we just give a brief introduction to the
aspects you need to know for this chapter. Let's start with the classic example of em-
ployees, departments, and department heads:
EMPLOYEE
DEPARTMENT
HEAD
Jones
Accounting
Jones
Smith
Engineering
Smith
Brown
Accounting
Jones
Green
Engineering
Smith
