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

2
  • "better query performance" for what queries?
    – Mat
    Apr 29, 2013 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. Apr 29, 2013 at 11:29

1 Answer 1

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)
);
2
  • The user_id is required on both tables because who answers the question is not the same person who posts the question. Apr 29, 2013 at 11:49
  • ahh, ok. Then your original model is fine. I'll modify my answer as well
    – druzin
    Apr 29, 2013 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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