Change log
- Added setting,category,category_post_type,member,post_duplicate_link
- social_media → social_media_type
setting
display_name (text) UNIQUE NOT NULL
current_value (text)
can_be_changed_by_mods (boolean default to FALSE)
category
display_name (text)
url_part (varchar 20)
is_primary (boolean default to FALSE)
short_explanation (text)
long_explanation (text)
contributes_to_tl (boolean default to TRUE) – whether this category is included for trust level calculations
minimum_tl_to_participate (bigserial FK to trust_level.id)
category_post_type
category_id
post_type_id
is_active (boolean default to TRUE)
UNIQUE CONSTRAINT (category_id,post_type_id)
post_duplicate_link
original_id
duplicate_id
UNIQUE CONSTRAINT (original_id,duplicate_id)
member
display_name (varchar 150)
bio (text)
profile_picture_link (text)
is_temporarily_suspended (boolean default to FALSE)
temporary_suspension_end_date (date)
temporary_suspension_reason (varchar 35)
trust_level_id (ref to trust_level.id)
network_account_id (bigserial, nullable)
is_moderator (boolean default to FALSE)
is_administrator (boolean default to FALSE)
is_synced_with_network_account (boolean default to TRUE)
trust_level
display_name (text 100) UNQIUE NOT NULL
explanation (text DEFAULT NULL) UNQIUE
social_media_type
The types of social media that the member can display in his profile
display_name (text 100)
member_social_media_type
The social media that the member would like to display in his profile per community
social_media_id (bigserial)
member_id (bigserial)
content (text 100)
UNIQUE CONSTRAINT (member_id,social_media_id)
privilege
Table for privileges
display_name (text 100)
description (text)
member_privilege
For recording which members have which privilege in a community. If a member has a privilege suspended, then that is also recorded here, and a nightly task will auto undo the suspension once the privelege_suspension_end_date has passed.
member_id (bigserial)
privilege_id (bigserial)
is_suspended (boolean default FALSE)
privilege_suspension_start_date (date)
privelege_suspension_end_date (date)
UNIQUE CONSTRAINT (member_id, privilege_id)
post_type
Records the type of post, question/answer/blog etc
display_name (Text 100)
description (Text)
status
For setting the status of a post locked/featured etc
display_name (Text 100) UNIQUE
description (Text)
post
I thought about splitting into a answers table and and questions_table but doing it in the same table lets comments have a post_id instead of a questions_id and a answers_id. Meta posts are denoted by the is_meta column. Type of post is determined by the post_type_id
member_id (bigserial)
title (Text 255 default NULL)
body (text)
upvotes (bigint)
downvotes (bigint)
net_votes (bigint)
score (decimal)
views (bigint)
is_accepted (boolean default FALSE)
post_type_id (bigserial)
is_closed (boolean default FALSE)
is_protected (boolean default FALSE)
parent_id (bigserial FK to post default NULL)
category_id
is_deleted (boolean default FALSE)
post_status
post_id (bigserial)
status_id (bigserial)
UNIQUE CONSTRAINT (post_id, status_id)
comment
Table for the comments on posts, both questions and answers.
member_id (bigserial)
post_id (bigserial)
parent_comment_id (bigserial FK to comment default NULL)
body (text)
upvotes (bigint default 0)
downvotes (bigint) default 0)
net_votes (bigint) default 0
score (decimal default 0)
is_deleted (boolean Default FALSE)
deleted_at (Date)
comment_votes
comment_id (bigserial)
member_id (bigserial)
vote_types_id (bigserial)
UNIQUE CONSTRAINT (comment_id, member_id)
tag
Table for all of the tags
body (text 100 default NULL UNIQUE)
description (text)
tag_wiki (text default NULL)
is_active (boolean default TRUE)
tag_synonym_id (bigserial FK to tag default NULL)
parent_id (bigserial FK to tag default NULL)
usages (bigint)
post_tag
post_id (bigserial)
tag_id (bigserial)
UNIQUE CONSTRAINT (post_id , tag_id )
vote_type
Table for the vote types, upvote/downvote.
display_name (text) UNIQUE
post_vote
The reason for this table is so that votes by spammers/serial voters can be undone.
post_id (bigserial)
vote_type_id (bigserial)
member_id (bigserial)
UNIQUE CONSTRAINT (post_id, member_id)