1

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.

1

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.

  • This helped, thank you! Broke it down and everything you said makes sense (or will once I process it some more-- first time hearing about unsigned keys, have to look into that more). Still need to revise but going to post the structure below.. broke it down to two tables per your advice, and it looks pretty clean so far. Still, a work in progress. – Brian Bruman May 6 '18 at 20:50
  • Aside from there being more key's than columns in the answers table haha. Just kind of noticed that, and not entirely sure. Gonna have to delve deeper into each one to see of its efficacy – Brian Bruman May 6 '18 at 21:17
  • @BrianBruman I've updated the answer to explain better about int unsigned. – dbdemon May 6 '18 at 22:00
  • Ah! That makes sense. Surprised I haven't heard about this before, but I guess that's because it's usually by default signed I'm guessing. But yes negative numbers are not necessary for auto_increments :). Will make the fix thanks again. – Brian Bruman May 7 '18 at 15:38
0

Just posting progress with this, but was able to revamp it down to two tables. Haven't looked too deeply in column structure yet-- working on getting the relationship schema down first. Any advice or thoughts on this would be helpful and appreciated. Just finished this so there might be some gaps.

Here we go.

Table #1: Questions

CREATE TABLE `questions` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 `question` varchar(255) DEFAULT NULL,
 `answer_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`question_id`),
 KEY `questions_index` (`answer_id`),
 CONSTRAINT `questions_index` FOREIGN KEY (`answer_id`) REFERENCES `answers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table #2: Answers

CREATE TABLE `answers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `answer` varchar(255) DEFAULT NULL,
 `question_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `question_index` (`question_id`) USING BTREE,
 KEY `answers_index` (`answer`) USING BTREE,
 KEY `id` (`id`),
 CONSTRAINT `answer_index` FOREIGN KEY (`question_id`) REFERENCES `questions` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And a PHPMyAdmin Visual (with mouse over)

questions:

enter image description here

answers:

enter image description here

Happy so far, but I'm sure there is more to do. Especially if I wish to expand from this basic template. Any thoughts appreciated as I said it is still a work in progress, but the simplicity behind it seems nice (but what do I know :)?)

And in fact have no idea what USING BTREE is actually.. not even a clue how it got in my structure or what its use is. Going to look into this soon.

  • 1
    Looks better, but how are you going to record what users have answered? Also, I don't think the index on the answer column is useful. – dbdemon May 6 '18 at 22:11
  • I was thinking about that as well. Haven't yet come up with anything yet. And thank you I will check that. – Brian Bruman May 6 '18 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.