Database Design of Linuxbar

tables

# --------------------------------------------------------------------------------------
# This is an old design.
# The new design is available at https://github.com/910JQK/linuxbar/blob/master/models.py
# --------------------------------------------------------------------------------------
config
  name varchar(64) primary
  value varchar(255)
user
  id int(8) primary auto_increment
  mail varchar(64) unique
  name varchar(32) unique
  password char(64) # sha256(salt_l4 + sha256(password) + salt_r4)
  reg_date timestamp
salt
  user -> user primary
  salt char(8)
site_admin
  user -> user primary
board
  id int(8) primary auto_increment
  short_name varchar(32) unique
  name varchar(64) unique
  description varchar(255)
  announcement text
ban
  user -> user primary
  operator -> user
  board -> board
  date timestamp
  expire_date timestamp
ban_global
  user -> user primary
  operator -> user
  date timestamp
  expire_date timestamp
board_admin
  id int(8) primary auto_increment
  user -> user
  board -> board
  level int(1) default 0
topic
  id int(8) primary auto_increment
  title varchar(64)
  board -> board
  author -> user
  summary text
  reply_count int(8) default 0
  last_post_date timestamp
  last_post_author -> user
  deleted int(1) default 0 # bool
  delete_date timestamp null
  delete_operator -> user null
  date timestamp
post
  id int(8) primary auto_increment
  content text
  topic -> topic
  topic_author -> user # a post emits a reply to its topic
  author -> user
  edited int(1) default 0 # bool
  edit_date timestamp null
  deleted int(1) default 0 #bool
  delete_date timestamp null
  delete_operator -> user null
  date timestamp
subpost
  id primary auto_increment
  content text
  author -> user
  reply0 -> topic # a subpost emits replies both to the post and the topic
  reply0_author -> user
  reply1 -> post
  reply1_author -> user
  reply2 -> subpost null # a subpost may emit a reply to another subpost
  reply2_author -> user null
  edited int(1) default 0 # bool
  edit_date timestamp null
  deleted int(1) default 0 # bool
  delete_date timestamp null
  delete_operator -> user null
  date timestamp
at_from_post
  id primary auto_increment
  post -> post
  caller -> user
  callee -> user
  date timestamp
at_from_subpost
  id primary auto_increment
  subpost -> subpost
  caller -> user
  callee -> user
  date timestamp
image
  sha256 char(64) primary
  uploader -> user
  name varchar(64) null
  date timestamp