patternrubyrailsMinor
Critique my Stack Overflow schema
Viewed 0 times
overflowcritiqueschemastack
Problem
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.i
```
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.i
Solution
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?
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?
Context
StackExchange Code Review Q#32141, answer score: 3
Revisions (0)
No revisions yet.