Altering Tables - Learning MySQL and MariaDB - page 78

birds table. We should change the column and create another reference table for it.
We'll do that later,though.
Dynamic Columns
We just covered ENUM , so let'sdigress from ALTER TABLE for a moment to cover dy-
namic columns. This is something that is available only in MariaDB, as of version 5.3. It's
similar to an ENUM column, but with key/value pairs instead of a plain list of options. That
will initially sound confusing, but it make more sense when we look at some examples. So
let's create a few tables with dynamic columns.
To make the bird-watchers site more interesting, suppose we've decided to do some sur-
veys of the preferences of bird-watchers. We'll ask the members to rate birds they like the
most. That will be a simple start. In time, we might ask them to rate the best places to see
birds in an area, or maybe binocular makers and models they like the best. For this scen-
ario, let's create a set of tables.
If you're not using MariaDB and don't want to replace MySQL with it, just read along. If
you do have MariaDB installed on your server, enter the following:
USE birdwatchers ;
CREATE TABLE surveys
( survey_id INT AUTO_INCREMENT KEY ,
survey_name VARCHAR ( 255 ));
CREATE TABLE survey_questions
( question_id INT AUTO_INCREMENT KEY ,
survey_id INT ,
question VARCHAR ( 255 ),
choices BLOB );
CREATE TABLE survey_answers
( answer_id INT AUTO_INCREMENT KEY ,
human_id INT ,
question_id INT ,
date_answered DATETIME ,
answer VARCHAR ( 255 ));
The first table we created here will contain a list of surveys. The second table is where
we'll put the questions. Because we intend to do only polls, the choices column will
contain the survey choices. We defined it with a very generic type, BLOB , but we'll use it
to store a dynamic column. The data type used has to be able to hold the data that will be
given to it when we create the dynamic column. BLOB can be agood choice for that.