Changes this iteration,
- Singularized the table names and changed Users to Member because User is a reserved word.
- Added reference tables to replace the CanUpVote etc columns and for the PostTypes.
- Changed how the TrustLevel and the Privileges+Suspension of privileges are recorded.
Member
This table will hold the global user records for an Codidact Instance. A user should only have one email to login with, that would be stored here. I am leaving the password storage and hashing to the experts.
DisplayName (Text 100)
Bio (Text)
Personal Website (Text 255)
Github (Text 100)
Twitter (Text 100)
Email (Text 255) UNQIUE
GravatarHash (Text)
Location (Text 100)
IsFromStackExchange (Boolean default FALSE)
StackExchangeId (Bigint)
StackExchangeValidated (Date)
StackExchangeLastImported (Date)
IsEmailVerified (Boolean default FALSE)
IsSuspended (Boolean default FALSE)
SuspensionEndDate (DATE)
Community
Table for each of the individual sites inside of a Codidact installation
Name (Text 40) UNQIUE
Tagline (Text 100) UNQIUE
URL (Text 255) UNQIUE
HelpText (Text)
FAQ (Text)
Status (ENUM)
TrustLevel
This needs to be specific to a site for when we get different language sites and it needs to be localized.
Name (Text 100) UNQIUE
Explanation (Text) UNQIUE
TrustLevelCommunity
CommunityId (bigserial)
IsSameAsInstance (Boolean default TRUE)
Name (Text 100) UNQIUE
Explanation (Text) UNQIUE
MemberCommunity
A user may want to join one site and not another, and having a join table between users and sites allows a user to be suspended or gain/lose privileges on a single site. I listed the privilege booleans in the order of lowest to highest, the privileges after IsModerator arenât listed because we probably are going to take any of moderatorâs lower privileges away without demodding them.
CommunityId (bigserial)
DisplayName (Text 100)
Bio (Text)
Personal Website (Text 255)
Github (Text 100)
Twitter (Text 100)
Email (Text 255) UNQIUE
GravatarHash (Text)
Location (Text 100)
IsFromStackExchange (Boolean default FALSE)
StackExchangeId (Bigint)
IsSameAsInstance (Boolean default TRUE)
StackExchangeValidated (Date)
StackExchangeLastImported (Date)
IsSuspended (Boolean default FALSE)
SuspensionEndDate (Date)
TrustLevelCommunityId (bigserial)
IsModerator (Boolean default FALSE)
IsDeleted (Boolean Default FALSE)
UNIQUE CONSTRAINT (MemberId,CommunityId)
Privilege
Table for privileges
Name (Text 100)
Description (Text)
MemberCommunityPrivilege
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 PrivelegeSuspensionEndDate has passed.
CommunityId (bigserial)
MemberId (bigserial)
PrivilegeId (bigserial)
IsSuspended (boolean DEFAULT FALSE)
PrivilegeSuspensionStartDate (Date)
PrivelegeSuspensionEndDate (Date)
UNIQUE CONSTRAINT (CommunityId,MemberId,PrivilegeId)
PostType
Records the type of post, question/answer/blog etc
Name (Text 100)
Description (Text)
Post
I thought about splitting into a Answers table and and QuestionsTable but doing it in the same table lets comments have a PostId instead of a QuestionsId and a AnswersId. Meta posts are denoted by the IsMeta column. Type of post is determined by the PostTypeId
MemberId (bigserial)
CommunityId (bigserial)
Title (Text 255 Default NULL)
Body (Text)
Upvotes (bigint)
Downvotes (bigint)
NetVotes (bigint)
Score (decimal)
IsAccepted (Boolean Default FALSE)
PostTypeId (bigserial)
IsClosed (Boolean default FALSE)
IsProtected (Boolean default FALSE)
ParentId (bigserial Default NULL)
IsMeta (Boolean Default FALSE)
IsDeleted (Boolean Default FALSE)
UNIQUE CONSTRAINT (Community,Title)
Comment
Table for the comments on posts, both questions and answers.
MemberId (bigserial)
PostId (bigserial)
ParentCommentId (bigserial DEFAULT NULL)
Body (Text)
Upvotes (bigint DEFAULT 0)
Downvotes (bigint) DEFAULT 0)
NetVotes (bigint) DEFAULT 0
Score (decimal DEFAULT 0)
IsDeleted (Boolean Default FALSE)
DeletedAt (Date)
CommentVotes
CommentId (bigserial)
MemberId (bigserial)
VoteTypesId (bigserial)
UNIQUE CONSTRAINT (CommentId,MemberId)
Tag
Table for all of the tags
CommunityId (bigserial)
Name (Text 100 NOT NULL)
Description (Text)
TagWiki (Text default NULL)
IsActive (Boolean default TRUE)
TagSynonymId (bigserial FK to Tag default NULL)
Usages (bigint)
UNIQUE CONSTRAINT (CommunityId,Name)
PostTag
PostId (bigserial)
TagId (bigserial)
UNIQUE CONSTRAINT (PostId,TagId)
VoteType
Table for the vote types, upvote/downvote.
Name (Text) UNIQUE
PostVote
The reason for this table is so that votes by spammers/serial voters can be undone.
PostId (bigserial)
VoteTypesId (bigserial)
MemberId (bigserial)
UNIQUE CONSTRAINT (PostId,MemberId)