schema

Postgres Database Schema

users

column name	data type	details
id	integer	not null, primary key
display_name	string	not null
email	string	not null, indexed, unique
about_me	text
reputation	integer	not null, default=0
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• index on email, unique: true
• index on session_token, unique: true
• has_many questions
• has_many answers
• has_many comments
• has_many followed_questions

questions

column name	data type	details
id	integer	not null, primary key
title	string	not null, indexed
body	text	not null
views	integer	not null, default=0
author_id	integer	not null, indexed, foreign key
editor_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• author_id references users
• editor_id references users
• index on title
• index on author_id
• index on editor_id
• belongs_to author
• has_many answers
• has_many comments
• has_many votes
• has_many taggings
• has_many tags through taggings

comments

column name	data type	details
id	integer	not null, primary key
body	text	not null
commenter_id	integer	not null, indexed, foreign key
commentable_id	integer	not null, indexed
commentable_type	string	not null, indexed
created_at	datetime	not null
updated_at	datetime	not null

• commenter_id references users
• index on commenter_id
• belongs_to questions

answers

column name	data type	details
id	integer	not null, primary key
body	text	not null
answerer_id	integer	not null, indexed, foreign key
question_id	integer	not null, indexed, foreign key
editor_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• answerer_id references users
• question_id references users
• editor_id references users
• index on answerer_id
• index on question_id
• index on editor_id
• belongs_to updater
• belongs_to questions
• belongs_to users

votes

column name	data type	details
id	integer	not null, primary key
voter_id	integer	not null, indexed, foreign key
votable_id	integer	not null, indexed, foreign key
votable_type	string	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• voter_id references users
• index on votable_id
• index on votable_type

tags

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed
created_at	datetime	not null
updated_at	datetime	not null

• has_many taggings
• has_many questions through taggings

taggings

column name	data type	details
id	integer	not null, primary key
tag_id	integer	not null, indexed
question_id	integer	not null, indexed
created_at	datetime	not null
updated_at	datetime	not null

• tag_id references tags
• question_id references questions
• index on tag_id
• index on question_id`
• belongs_to tags
• belongs_to questions

badges

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed
description	text	not null, indexed

• has_many user_badges`
• has_many users through user_badges

user_badges

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign_key
badge_id	integer	not null, indexed, foreign_key

• user_id references users
• badge_id references badges
• index on user_id
• index on badge_id`