Mysql – Designing a database for Questions, Options and Answers

database-designMySQLschema

A practice in trying to adhere to relational database techniques.
Can anyone give me a little bump as to what I'm doing wrong/how this should be?

In Short All Questions have 5 Possible Answers. That's the whole database act.

My idea/what I've thought to do so far was create three tables:

questions

CREATE TABLE `questions` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 `question` mediumtext NOT NULL,
 PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

options

CREATE TABLE `options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 INDEX choice_index (id),
    FOREIGN KEY (`id`)
        REFERENCES questions(question_id),
 `answer_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`choices_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here's where I'm coming to a dead-end.

answers

CREATE TABLE `answers` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 ??`answer_id` int(11) NOT NULL
 //Foreign Key?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

A foreign key has to be an integer value1 — but my answer choices are all varchar(255).

What is the proper way to link these three tables up?

1 This is an assumption, since most if not all relational schemas I've seen are "binded" by integer values, so that is where this assumption is coming from.

Best Answer

The relationship between questions and options is one-to-one, so if you wanted to simplify your table structure, then these two tables could be merged into one.

Are you 100% certain that every question will have only 5 (or less) options? For flexibility reasons, it might make sense to have only one answer option per record. In that case, the options table must remain separate from the questions table, and it becomes a one-to-many relationship.

Which is the right answer option? You should probably indicate that somehow.

In the answers table, the answer_idis obviously the primary key. The question_id is a foreign key. And you seem to have forgotten a foreign key to options, as well as a foreign key to a users table to indicate who gave this answer.

A minor issue: In the questions table, the question column is a MEDIUMTEXT. This is up to 16 million bytes. Do you really need that many? A TEXT column can store up to 64K bytes, maybe that would be enough?

Also, you would want to use int unsigned for your auto_increment columns. The reason for this is that auto incrementing ints start at 1, so by allowing signed ints you're effectively wasting a bit and limiting the range of values you can use.

There may be other opportunities for improvements, these are just a few I noticed.

Related Question