Creating Databases and Tables - Learning MySQL and MariaDB - page 63

This SQL statement creates a table named bird_orders with four columns to start.
The first one, order_id , is the key in which rows will be referenced from the
bird_families table. This is followed by scientific_name for the scientific
name of the order of birds, with a data type of VARCHAR . We're allowing the maximum
number of characters for it. It's more than we'll need, but there won't be many entries in
this table and it's difficult to guess what what the longest description will be. So we'll set
it to the maximum allowed for that data type. We're naming this column
brief_description , as we did in the earlier bird_families table.
Because all three tables that we've created so far have similar names for some of the
columns (e.g., scientific_name ), that may cause us a little trouble later if we try to
join all of these tables together. It might seem simpler to use distinct names for these
columns in each of these tables (e.g., order_scientific_name ). However, we can
resolve that ambiguity easily whennecessary.
In the previous SQL statement, notice that we have a column for an image to represent the
order of birds. We might put a photo of the most popular bird of the order or a drawing of
several birds from the order. Notice that for this image file, the data type we're using isa
BLOB . While the name is cute and evocative, it also stands for binary large object . We
can store an image file, such as a JPEG file, in the column. That's not always a good idea.
It can make the table large, which can be a problem when backing up the database. It
might be better to store the image files on the server and then store a file path or URL ad-
dress in the database, pointing to where the image file is located. I've included a BLOB
here, though, to show it as a possibility.
After the list of columns, we've included the default character set and collation to be used
when creating the columns. We're usingUTF-8 (i.e., UCS Transformation Format, 8-bit),
because some of the names may include characters that are not part of thedefault lat-
in1 character set. For instance, if our fictitious bird-watcher site included German words,
the column brief_description would be able to accept the letters with umlauts over
them (i.e., ä ). The character set utf8 allows for such letters.
For a real bird-watching database, both the bird_families and bird_orders
tables would have more columns. There would also be several more tables than the few
we're creating. But for our purposes, these few tables as they are here will be fine for
now.