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

the name and other information related to each family of birds for each bird in the birds
table:
CREATE TABLE bird_families (
family_id INT AUTO_INCREMENT PRIMARY KEY ,
scientific_name VARCHAR ( 255 ) UNIQUE ,
brief_description VARCHAR ( 255 ) );
We're creating three columns in the table. The first is the most interesting for our purposes
here. It's the column that will be indexed and will be referenced by the birds table. That
sounds like there is a physical connection or something similar within the birds table,
but that's not what will happen. Instead, the connection will be made only when we ex-
ecute an SQL statement, a query referencing both tables. With such SQL statements, we'll
join the bird_families table to the birds table based on the family_id columns
in both. For instance, we would do this when we want a list of birds along with their cor-
responding family names, or maybe when we want to get a list of birds for a particular
family.
Now we can put all the information we want about a family of birds in one row. When we
enter data in the birds table, we'll include the family_id identification number that
will reference a row of the bird_families table. This also helps to ensure consistency
of data: there's less chance of spelling deviations when you only enter a number and not a
Latin name. It also saves space because you can store information in one row of
bird_families and refer to it from hundreds of rows in birds . We'll see soon how
this works.
The scientific_name column will hold the scientific name of the family of birds
(e.g., Charadriidae ). The third column is basically for the common names of families
(e.g., Plovers ). But people often associate several common names to a family of birds, as
well as vague names for the types of birds contained in the family. So we'll just call the
column brief_description .
Let's next create a table for information about the orders of the birds. This is a grouping of
families of birds. We'll name it bird_orders . For this table, let's try out some of the
extra options mentioned earlier. Enter the following SQL statement:
CREATE TABLE bird_orders (
order_id INT AUTO_INCREMENT PRIMARY KEY ,
scientific_name VARCHAR ( 255 ) UNIQUE ,
brief_description VARCHAR ( 255 ),
order_image BLOB
) DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ;