0

I need to create a question and answer tables that will have millions (maybe billions) of rows.

The current model is:

Question Table id_question (PK, auto increment) id_user question_content question_date

Answer Table id_answer (PK, auto increment) id_question id_user answer_content answer_date

Is this a correct model (considering better query performance)? Should I add the id_question and id_user columns to the primary key?

Thanks

  • "better query performance" for what queries? – Mat Apr 29 '13 at 10:49
  • It will work like StackExchange, queries as simple as "SELECT * FROM Question WHERE id_question = @id" and "SELECT * FROM Answer WHERE id_question = @question ORDER BY answer_date DESC". But my question is regarding better performance considering indexes and correct modeling. Things like should I add an auto increment column for this answers table and let it be the only primary key? Or should I add it as an unique identifier (or something like), making a composite primary key with id_question, id_answer and id_user? Thanks. – Juliano Nunes Silva Oliveira Apr 29 '13 at 11:29
1

You're right. Your tables would look something like this then ( oracle ddl ) :

create table Users (id int,
                    name varchar2(1000),
                    constraint pk_users primary key (id)
);

create table Questions (id int,
                        user_id int,
                        content CLOB,
                        question_date date,
                        constraint pk_questions primary key (id),
                        constraint fk_questions_user_id foreign key (user_id) references users(id)
                        );

create table Answers (id int,
                      question_id int,
                      user_id int,
                      content CLOB,
                      answer_date date,
                      constraint pk_answers primary key (id),
                      constraint fk_answers_questions foreign key (question_id) references Questions(id), 
                      constraint fk_answers_user_id foreign key (user_id) references users(id)
);
  • The user_id is required on both tables because who answers the question is not the same person who posts the question. – Juliano Nunes Silva Oliveira Apr 29 '13 at 11:49
  • ahh, ok. Then your original model is fine. I'll modify my answer as well – druzin Apr 29 '13 at 11:58

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.