Database Reference
In-Depth Information
The third table is where we will store the answers to the survey questions. This time we
definea
VARCHAR
column to hold the dynamic column. We will link
sur-
vey_answers
to
survey_questions
based on the
question_id
, and
sur-
vey_questions
to
surveys
based on the
survey_id
.
Now let's put some data in these tables. If you're using MariaDB, enter the following
SQL statements to add SQL statements:
INSERT INTO
surveys
(
survey_name
)
VALUES
(
"Favorite Birding Location"
);
INSERT INTO
survey_questions
(
survey_id
,
question
,
choices
)
VALUES
(
LAST_INSERT_ID
(),
"What's your favorite setting for bird-watching?"
,
COLUMN_CREATE
(
'1'
,
'forest'
,
'2'
,
'shore'
,
'3'
,
'backyard'
) );
INSERT INTO
surveys
(
survey_name
)
VALUES
(
"Preferred Birds"
);
INSERT INTO
survey_questions
(
survey_id
,
question
,
choices
)
VALUES
(
LAST_INSERT_ID
(),
"Which type of birds do you like best?"
,
COLUMN_CREATE
(
'1'
,
'perching'
,
'2'
,
'shore'
,
'3'
,
'fowl'
,
'4'
,
'rapture'
) );
That created two surveys: one with a set of choices about where the birders like to watch
birds; the second with a simple, not comprehensive set of bird types they prefer. Weused
COLUMN_CREATE()
to create the enumerated lists of choices: each choice has a key and
a value. Thus, in
survey_questions
, choice 1 is “forest,” choice 2 is “shore,” and
choice 3 is “backyard.” Starting with MariaDB version 10.0.1, you can give strings for the
keys instead of numbers.
Let's see now how data may be retrieved from a dynamic column:
SELECT COLUMN_GET(choices, 3 AS CHAR)
AS 'Location'
FROM survey_questions
WHERE survey_id = 1;
+----------+
| Location |
+----------+
| backyard |
+----------+
