Need database design advise

Question

I have a Questions table which looks like that

as you see, there are 2 id rows that are nearly same: id, question_id. id - is autoincremented, unique id of each question, and question_id - is, for example, course 1 lesson 1 has 5 questions like: question 1, 2, 3, 4, 5. And course 1 lesson 2 has 3 questions: 1, 2, 3 etc.. In other words it's like autoincrement field for each unique course_id, lesson_id combination. I decided to add manually question_id based on course_id, lesson_id for keeping database structure readability and not messing up database, with bunch of association tables between course-lesson-question unique id values.

First question is

How do you think, is it good solution or not? How you'd design this database?

---

The problem is, when I want to insert new question with given course_id, lesson_id the id field will auto-increment, but I got

Second question

How can I get, last question_id column value based on unique course_id, lesson_id combination. For example, if course 1 lesson 2 has 3 questions: 1, 2, 3 and I want to add 4th question (as I said, in current db design I'm inserting question_id value manually), how do I know that, the last question of course 1 lesson 2 is 3th, and insert new question with (last_number=3)++=4?

Update

Situation is a bit complicated but I will try to explain:

It's online tutorials website. There are different courses, and each course has bunch of lessons. Now I'm designing question-answer part, in which teacher posts questions, and users getting dedicated questions.

Full size image here

Now, the table questions is dedicated for course>lesson based questions.

question_from_to_assoc - It's for, creating assoications between question author and receiver user. For example admin (id=.) sends question to some user with id=5.

qa_assoc - question-answer associations table.

Answer 1

Question 1: No. I would just use that ID column in that questions table as your unique identifier and drop that question_id field. My design:

 create table author (
   id int(11) NOT NULL auto_increment,
   name varchar(256)
 ) engine=innodb;

 create table course (
   id int(11) not null auto_increment,
   primary key(id),
   name varchar(256)
 ) engine=innodb;

create table lesson (
  id int(11) not null auto_increment,
  primary key(id),
  name varchar(256),
  course_id int(11) NOT NULL,
  FOREIGN KEY(course_id) references course(id)
) engine=innodb;

create table question(
   id int(11) not null auto_increment,
   primary key(id),
   question_text text,
   correct_answer text,
   lesson_id int(11) NOT NULL,
   foreign key(lesson_id) references lesson(id),
   author_id int(11) not null,
   FOREIGN KEY(author_id) REFERENCES author(id)
) engine=innodb;

Question 2: Don't do that. What if I have course_id 1, lesson 2, and question 11? That ID column would be identical to course_id 1, lesson 21, question 1.

And as an aside, I really hope you're using foreign keys. Since it says you're using mysql, be sure to use the Innodb storage engine with these tables so you can use foreign keys to enforce referential integrity.

The key to querying this database efficiently, avoiding collisions in supposedly unique values, avoiding serious performance issues in the future, and data duplication is to design your database in a normalized manner. My example above is normalized and avoids data duplication, as well as the composite key scheme that would not result in unique keys that you defined above. It's best to work with the features built into MySQL rather than try to reinvent the wheel.

Answer 2

First of all, this is not an optimal database design. Your schema is denormalized, which is not really good.

To answer your first question. I would split Lesson, Course, Question and Author into separate tables. Then I would add a number field beside the Primary Key for Course, Lesson and Question. The PK will only ensure uniqueness of a row, but the number field will be your course number, question number, etc. Using the PK to represent a question number for instance is not a good idea in my opinion, because it should be kept unchanged. For instance, if your questions are changed to letters instead of numbers, your PK would have to be changed and this might break referential integrity.

After that, I would add a unique constraint on question numbers and FK like [question_no, lesson_id] to ensure that you cannot have two question 1 for the same lesson. Same thing for Lesson. Course would have a unique constraint on course_no.

Finally, to automatically increment question numbers depending on lesson, I would use a trigger which would do something like :

CREATE TRIGGER tr_question_number BEFORE INSERT ON questions
FOR EACH ROW BEGIN
   SET NEW.question_no = (SELECT MAX(question_no)+1 FROM questions WHERE lesson_id = NEW.lesson_id FOR UPDATE)
END;

This trigger will set the question number field with the latest value + 1. The FOR UPDATE clause is very important, because it will lock the row to avoid concurrent insertion to get the same number.

The trigger is just a draft, but that's just a general idea of what I would have done.

I hope this will help you.

Answer 3

First question. Actually, I can see why you might need the "redundant" id. For example, it may play a role in the process of presenting your questions to test takers. But, it surely does not need to be tied to the values of other columns in the row, the autogenerated id guarantees uniqness anyway.

Second question. Use the onInsert trigger. It the best way to prevent collisions.

Answer 4

I personally would set this up different:

Question table:
id int, -- PK auto-increment
content varchar(50),
answer varchar(50),
authorid int

Course table:
id int, -- PK auto-increment
name varchar(50)

Lesson Table: 
id int, -- PK auto-increment
name varchar(50)

Question_Course_Lesson join table: 
questionid int, -- PK
courseid int,  -- PK
lessonid int   -- PK

Answer 5

1. You should try to get rid of question_id and only leave the autoincrement id as a primary key. Otherwise inserting will get messy.

The problem is, now when I want to insert new question with given course_id, lesson_id the id field wil auto-increment.

I don't understand the problem - auto_increment fields usually do that :).

2. You can use

SELECT MAX(question_id) WHERE course_id = <something> AND lesson_id = <some_other_thing>

To get the current max id for a given course_id and lesson_id. But you will have to lock the table (or use FOR UPDATE and a transaction if the table is InnoDB) and unlock it after you insert the new record to make sure it remains consistent.