get stuck to create table for answers for forum?

Question

I'm trying to create a forum using jsp and mySql database.

So, basically i'm getting stuck to create a design for table.

I've this table:

create table if not exists thread_question(
question_id INT NOT NULL auto_increment,
person_name VARCHAR(100) NOT NULL,
question_title VARCHAR(500) NOT NULL,
question VARCHAR(100000) NOT NULL,
question_dateTime VARCHAR(100) NOT NULL,
PRIMARY KEY(question_id)
);

create table if not exists thread_answer(
answer_id INT NOT NULL auto_increment,
person_name_answer VARCHAR(100) NOT NULL,
answer VARCHAR(100000) NOT NULL,
answer_dateTime VARCHAR(100) NOT NULL,
PRIMARY KEY(answer_id)
);

Here if i fetch all those answers, it shows the same answers according to all questions. Here i get stuck..

So, how to show an answer with separate question so that it would show with different question. Hope so you understand what i'm trying to say.

Surely, Help would be appreciated!!

Answer 1

You need to connect between your answers and your questions.

So you need to define the thread_answer table more like this:

create table if not exists thread_answer(
    answer_id INT NOT NULL auto_increment,
    question_id INT NOT NULL references thread_question(question_id),
    person_name_answer VARCHAR(100) NOT NULL,
    answer VARCHAR(100000) NOT NULL,
    answer_dateTime VARCHAR(100) NOT NULL,
    PRIMARY KEY(answer_id)
);

When you store a record in this table, you have to include the question_id of the question that is being answered. When you prepare the answer form in you jsp, make sure you include the question_id number in it as a hidden field.

When you retrieve all the answers to a question, you can add question_id = NNNN to your WHERE part and then you'll get just the answers to that question.