Database Structure

From ESEEWiki

Contents 1 Example Queries 2 Database Relations 2.1 Committee 2.2 Document 2.3 DocumentCommittee 2.4 NewsFeedItem 2.5 Section 2.6 SectionProposal 2.7 SectionVersion 2.8 User 2.9 UserCommittee 2.10 UserSectionProposal

Example Queries

• INSERT INTO Section (Title, Body) VALUES ('This Is The Title', 'Hello, I am the Body of the section.');
• SELECT S.Title, S.Body FROM Section S WHERE SectionId = 1 ORDER BY S.Title DESC;
• UPDATE Section SET Title = 'New Title' WHERE SectionId = 1;
• DELETE FROM Section WHERE Section.SectionId = 1;

• SELECT * FROM Section;
• SELECT S.SectionId FROM Section WHERE S.Title = 'Title' AND S.Body = 'Body' ORDER BY S.SectionId DESC LIMIT 0, 1;

Database Relations

Committee

• CommitteeId INTEGER PRIMARY KEY AUTO_INCREMENT
• Name VARCHAR(255)
• ApproveRatio DOUBLE (ranges from 0 to 1, 1 being unanimous, 0.75 being 3/4 approval, 0.67 being 2/3 approval, etc)

Document

• DocumentId INTEGER PRIMARY KEY AUTO_INCREMENT
• SuperSectionId INTEGER FOREIGN KEY REFERENCES Section.SectionId
• CreationTimestamp DATETIME

DocumentCommittee

• DocumentCommitteeId INTEGER PRIMARY KEY AUTO_INCREMENT
• DocumentId INTEGER FOREIGN KEY REFERENCES Document.DocumentId
• CommitteeId INTEGER FOREIGN KEY REFERENCES Committee.CommitteeId (-1 = anonymous, -2 = members)
• HasView INTEGER (1 = committee has View permission on document, 0 = doesn't)
• HasModifyDirect INTEGER (1 = committee has ModifyDirect permission on document, 0 = doesn't)
• HasModifyPropose INTEGER (1 = committee has ModifyPropose permission on document, 0 = doesn't)
• HasVote INTEGER (1 = committee has Vote permission on document, 0 = doesn't)

NewsFeedItem

• NewsFeedItemId INTEGER PRIMARY KEY AUTO_INCREMENT
• Content TEXT
• Timestamp DATETIME
• SubjectCategory VARCHAR(50)
• SubjectId INTEGER
• Type INTEGER (0 = guest, 1 = member, 2 = admin)

Section

• SectionId INTEGER PRIMARY KEY AUTO_INCREMENT
• ParentId INTEGER NULL FOREIGN KEY REFERENCES Section.SectionId
• SortOrder INTEGER
• LatestVersionNumber INTEGER // this stores the latest version number, as a cache of the latest version in SectionVersion
• LatestTitle VARCHAR(255) // this stores the latest title, as a cache of the latest version in SectionVersion
• LatestBody TEXT // this stores the latest body, as a cache of the latest version in SectionVersion

SectionProposal

• SectionProposalId INTEGER PRIMARY KEY AUTO_INCREMENT
• OriginVersionId INTEGER FOREIGN KEY REFERENCES SectionVersion.SectionVersionId
• DestVersionId INTEGER FOREIGN KEY REFERENCES SectionVersion.SectionVersionId
• CreatorId INTEGER FOREIGN KEY REFERENCES User.UserId
• Title VARCHAR(255)
• Body TEXT
• SubmissionTimestamp DATETIME
• LastModified DATETIME
• IsVoteOpen INTEGER
• VoteOpenTime DATETIME
• VoteCloseTime DATETIME
• NumAffirmative INTEGER
• NumNegative INTEGER
• NumAbstain INTEGER
• WasApproved INTEGER (0 = hasn't been approved, 1 = has been approved)

SectionVersion

• SectionVersionId INTEGER PRIMARY KEY AUTO_INCREMENT
• SectionId INTEGER FOREIGN KEY REFERENCES Section.SectionId
• CreatorId INTEGER FOREIGN KEY REFERENCES User.UserId
• VersionNumber INTEGER
• Title VARCHAR(255)
• Body TEXT
• LastModified DATETIME

User

• UserId INTEGER PRIMARY KEY AUTO_INCREMENT
• FirstName VARCHAR(100)
• LastName VARCHAR(100)
• EMail VARCHAR(100)
• UserName VARCHAR(100)
• Password VARCHAR(100)
• AccountCreated DATETIME
• LastSuccessfulLogin DATETIME
• Role INTEGER (1 = member, 2 = admin)
• IsChair INTEGER

UserCommittee

• UserCommitteeId INTEGER PRIMARY KEY AUTO_INCREMENT
• UserId INTEGER FOREIGN KEY REFERENCES User.UserId
• CommitteeId INTEGER FOREIGN KEY REFERENCES Committee.CommitteeId

UserSectionProposal

• UserSectionProposalId INTEGER PRIMARY KEY AUTO_INCREMENT
• UserId INTEGER FOREIGN KEY REFERENCES User.UserId
• SectionProposalId INTEGER FOREIGN KEY REFERENCES SectionProposal.SectionProposalId
• Vote INTEGER (-1 = no, 0 = abstain, 1 = yes)
• HasVoted INTEGER (0 = user has not voted on this proposal, 1 = user has voted on this proposal)

Comments:

• when a vote is opened on a proposal, all of the users that can vote on a proposal will have UserSectionProposal rows created for them
• regardless of whether or not the document voting permissions are changed before the vote is closed again, only those people who were allowed to vote upon vote opening will be allowed to vote on that proposal, as determined by the existence of a UserSectionProposal row for each authorized user
• once a user votes on a proposal, they cannot change their vote