What is the correct model for related tables with millions of rows?

Question

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

Answer 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)
);