4
\$\begingroup\$

I'm learning DB design. How would you change the following schema which tries to replicate Stack Overflow's functionality:

create_table "questions", :force => true do |t|
    t.text      "question",         :null => false
    t.text      "description",  :null => false
    t.integer       "up_votes",         :null => false, :default => 0
    t.integer       "down_votes",       :null => false, :default => 0
  t.timestamps
end

create_table "answers", :force => true do |t|
    t.integer       "question_id",  :null => false
    t.text      "answer",               :null => false
    t.integer       "up_votes",         :null => false, :default => 0
    t.integer       "down_votes",       :null => false, :default => 0
  t.timestamps
end

create_table "question_comments", :force => true do |t|
    t.integer   "question_id",  :null => false
    t.text      "comment",          :null => false
  t.timestamps
end

create_table "answer_comments", :force => true do |t|
    t.integer   "answer_id",    :null => false
    t.text      "comment",      :null => false
  t.timestamps
end

create_table "tags", :force => true do |t|
    t.string        "tag",  :null => false, :limit => 100
  t.timestamps
end
add_index "tags", ["tag"], :name => "tag_UNIQUE", :unique => true

create_table "question_tags", :force => true do |t|
  t.integer     "question_id",  :null => false
  t.integer     "tag_id",               :null => false
  t.timestamps
end

create_table "users", :force => true do |t|
    t.string        "first_name",   :limit => 45,       :null => false
    t.string        "last_name",    :limit => 45,       :null => false
    t.string        "email",            :limit => 100,  :null => false
    t.string        "password",                                     :null => false
    t.string        "salt",                                             :null => false
  t.timestamps
end

create_table "user_votes", :force => true do |t|
    t.integer       "user_id",  :null => false
    t.integer       "question_id"
    t.integer       "answer_id"
    t.boolean       "is_positive_vote"
    t.timestamps
end

create_table "user_favorites", :force => true do |t|
    t.integer       "user_id",  :null => false
    t.integer       "question_id"
    t.integer       "answer_id"
    t.timestamps
end

create_table "question_flags", :force => true do |t|
    t.integer       "user_id",  :null => false
    t.integer       "question_id"
    t.timestamps
end

create_table "answer_flags", :force => true do |t|
    t.integer       "user_id",  :null => false
    t.integer       "question_id"
    t.timestamps
end

create_table "related_questions", :force => true do |t|
    t.integer       "question_id",                  :null => false
    t.integer       "related_question_id",  :null => false
    t.timestamps
end
\$\endgroup\$
3
\$\begingroup\$

Your answers table doesn't actually have an answerID, which is later referenced in your answer comments.

Your link tables for comments, I would have a primary key assigned that is separate from the answerid, since you don't want a composite key between answerID and comment (Think about updating/removing post logic). Apply the same to question comments.

Consider storing users against questions/answer submissions, otherwise, how will you know who posted it? Think about edit functionality too, do you want to audit changes?

\$\endgroup\$

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.