Skip to content
Darian Chen edited this page Oct 9, 2022 · 3 revisions

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`
Clone this wiki locally