Handling Unknown Column Types at Creation Time in MySQL

MySQLschema

I have a table in a MySQL database that stores survey results.

CREATE TABLE survey_responses (
  response_id INTEGER UNSIGNED AUTO_INCREMENT,
  survey_id INTEGER UNSIGNED NOT NULL,
  question_id INTEGER UNSIGNED NOT NULL,
=>response WELL_IT_DEPENDS_REALLY,
  FOREIGN KEY (survey_id) REFERENCES surveys (survey_id),
  FOREIGN KEY (question_id) REFERENCES questions (question_id)
) ENGINE=InnoDB;

If the question is free form, a BLOB may be best for the response. If the question is "select the best answer", an INT pointing to that answer's record would be best. I'm sure I could store this in a document database and determine the type during runtime. But I would hate to maintain two databases just for this one column.

I've thought about having response_blob, response_int, response_char, and having them default to NULL. Then I would read the appropriate column at runtime. Is that acceptable or just a silly idea?

Is there a standard way this situation is handled?

Best Answer

Looks like you are describing a subtype/supertype structure.

Start by creating a reference table to hold the different types of response types you can create. This table can be expanded easily if you come up with more types later on.

CREATE TABLE survey_response_types (
  `response_type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(255) NOT NULL,
) ENGINE=InnoDB;

Modify your survey_responses table to use the response_type_id value instead of actually holding the response. This is the discriminator that lets you know what type of response you have when looking at or joining into the survey_responses table.

CREATE TABLE survey_responses (
  `response_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `survey_id` INTEGER UNSIGNED NOT NULL,
  `question_id` INTEGER UNSIGNED NOT NULL,
  `response_type_id` INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (`survey_id`) REFERENCES surveys (`survey_id`),
  FOREIGN KEY (`question_id`) REFERENCES questions (`question_id`),
  FOREIGN KEY (`response_type_id` REFERENCES `response_types` (`response_type_id`)
) ENGINE=InnoDB;

Then for each type of response, create another table, whose primary key is exactly the the same as the primary key of the survey_responses table. Also, make a foreign key constraint back to the survey_responses table. Do NOT create a new auto_increment key for this table, there is no need.

CREATE TABLE text_responses (
  `response_id` INTEGER UNSIGNED NOT NULL,
  `text` TEXT NOT NULL,
  FOREIGN KEY (`response_id`) REFERENCES `survey_responses` (`response_id`),
) ENGINE=InnoDB;

You mentioned linking back to the int of an answer, so here is how this table would look.

CREATE TABLE answer_responses (
  `response_id` INTEGER UNSIGNED NOT NULL,
  `answer_id` INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (`response_id`) REFERENCES `survey_responses` (`response_id`),
  FOREIGN KEY (`answer_id`) REFERENCES `answers` (`answer_id`)
) ENGINE=InnoDB;

Add as many more tables as there are possible types of answers.

When inserting the data, you should always insert an entry both into the survey_responses table, and then within the same transaction, insert an entry into the correct subtype table. MySQL does not support check constraints, so you'll have to handle the constraint code in the application layer to ensure that the response_type_id always matches correct subtype table that is used.

Together a subtype and supertype create a single 'record' that you can access. They should always exist in pairs. Never just a super type (survey_responses table record) nor just a subtype (which the foreign key constraint fortunately automatically prevents).

You can query them however you desire though. If you just want to make sure a record exists, simply query or join into the survey_responses table. If you want the actual response, construct your joins and queries appropriately.

Using this setup you can avoid having multiple null columns, and also protect against accidents where somehow a response gets two different types of responses. Although the lack of check constraints in MySQL will actually allow two types of responses in the subtype/supertype setup, the discriminator will let you identify the correct one when querying the data. (Any incorrect ones would be a result of bad coding in the application code.)

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Sql-server – Converting Database from GUID to Integer Keys

Your plan sounds basically OK to me. I would proceed cautiously though - add a new int column to the tables, populate it with unique values (the ROW_NUMBER() function is handy for this kind of thing) and then change it to be the primary key and check all is ok before considering dropping the GUID cols.

You perhaps should create a set of archive tables containing the GUID and PK for each of the tables that you can refer back to if you needed to.

Best Answer

Related Solutions

Mysql – Drop auto increment hack w/o alter table

Sql-server – Converting Database from GUID to Integer Keys

Related Question