Database Reference
In-Depth Information
The next column will contain the scientific name of each bird (e.g.,
Charadrius vociferus
,
instead of
Killdeer
). You might think that the
scientific_name
column would be the
ideal identifier to use as the primary key on which to index the
birds
table, and that we
wouldn't need the
bird_id
column. But the scientific name can be very long and usu-
ally in Latin or Greek (or sometimes a mix of both languages), and not everyone is com-
fortable using words from these languages. In addition, would be awkward to enter the
scientific name of a bird when referencing a row in the table. We've set the
scientif-
ic_name
column to have a variable-width character datatype (
VARCHAR
). The 255 that
we specify in the parentheses after it sets the maximum size (255 should be sufficient for
the long names we'll need to accommodate).
If the scientific name of a bird has fewer than 255 characters, the storage engine will re-
duce the size of the column for the row. This is different from the
CHAR
columndata type.
If the data in a
CHAR
column is less than its maximum, space is still allocated for the full
width that you set. There are trade-offs with these two basic character data types. If the
storage engine knows exactly what to expect from a column, tables run faster and can be
indexed more easily with a
CHAR
column. However, a
VARCHAR
column can use less
space on the server's hard drive and is less prone to fragmentation. That can improve per-
formance. When you know for sure that a column will have a set number of characters,
use
CHAR
. When the width may vary, use
VARCHAR
.
Next, we set the column data type for the
common_name
of each bird to a variable-width
character column of only 50 characters at most.
The fourth column (
family_id
) will be used as identification numbers for the family of
birds to which each bird belongs. They are integer data types (i.e.,
INT
). We'llcreate an-
other table for more information on the families. Then, when manipulating data, we can
join the two tables, use a number to identify each family, and link each bird to its family.
The last column is for the description of each bird. It's a
TEXT
data type,which means
that it's a variable-width column, and it can hold up 65,535 bytes of data for each row.
This will allow us to enter plenty of text about each bird. We could write multiple pages
describing a bird and put it in this column.
There are additional factors to consider when searching for a bird in a database, so there
are many columns we could add to this table: information about migratory patterns, not-
able features for spotting them in the wild, and so on. In addition, there are many other
data types that may be used for columns. We can have columns that allow for larger and
smaller numbers, or for binary files to be included in each row. For instance, you might
want a column with a binary data type to store a photograph of each bird. However, this
basic table gives you a good sampling of the possibilities when creating tables.
