[geeklog-devel] Updated mysql g2 schema

[Dwight Trumbower]

Database diagram - http://www.tsystemscorp.com/geeklog/mysql.html

MYSQL schema - http://www.tsystemscorp.com/geeklog/g2mysqlcreate.sql


CREATE TABLE Itemtypes(
ittTypeId MEDIUMINT NOT NULL,
ittTypeName VARCHAR(50) NOT NULL,
PRIMARY KEY (ittTypeId)) TYPE = INNODB;


CREATE TABLE ItemStates(
isStateId TINYINT NOT NULL,
isStateName VARCHAR(50) NOT NULL,
isDescription VARCHAR(255) NOT NULL,
PRIMARY KEY (isStateId)) TYPE = INNODB;


CREATE TABLE Categories(
catCategoryId MEDIUMINT NOT NULL,
catOwnerId MEDIUMINT NOT NULL,
catGroupId MEDIUMINT NOT NULL,
catPermOwner TINYINT NOT NULL,
catPermAnon TINYINT NOT NULL,
catName VARCHAR(50) NOT NULL,
catPermMembers TINYINT NOT NULL,
catPermGroup TINYINT NOT NULL,
catParentCategoryId MEDIUMINT NOT NULL,
catImageUrl VARCHAR(128) DEFAULT NULL,
catSortNum TINYINT DEFAULT NULL,
PRIMARY KEY (catCategoryId)) TYPE = INNODB;


CREATE TABLE Users(
uUserId MEDIUMINT NOT NULL,
uRegDate INT NOT NULL,
uProfileViews MEDIUMINT DEFAULT 0 NOT NULL,
uItemsPerPage TINYINT DEFAULT 10 NOT NULL,
uLangId TINYINT DEFAULT 1 NOT NULL,
uUserName VARCHAR(25) NOT NULL,
uPassWord VARCHAR(35) NOT NULL,
uEnabled TINYINT NOT NULL,
uEmail VARCHAR(128) NOT NULL,
uCommentModeId TINYINT,
uCommentOrderId TINYINT,
uCommentLimit MEDIUMINT DEFAULT NULL,
uCookieTimeout MEDIUMINT,
uLocale VARCHAR(3),
uDateFormatId TINYINT,
uBlocksEnabled TINYINT DEFAULT 1,
PRIMARY KEY (uUserId)) TYPE = INNODB;


CREATE TABLE Items(
itItemId MEDIUMINT NOT NULL,
itPermMembers TINYINT NOT NULL,
itPermAnon TINYINT NOT NULL,
itTypeId MEDIUMINT NOT NULL,
itUserId MEDIUMINT NOT NULL,
itCategoryId MEDIUMINT NOT NULL,
itDate INT NOT NULL,
itViews MEDIUMINT DEFAULT 0 NOT NULL,
itStateId TINYINT NOT NULL,
itEmails MEDIUMINT DEFAULT 0 NOT NULL,
itNumRatings MEDIUMINT DEFAULT 0 NOT NULL,
itRatingSum MEDIUMINT DEFAULT 0 NOT NULL,
itOwnerId MEDIUMINT NOT NULL,
itGroupId MEDIUMINT NOT NULL,
itPermOwner TINYINT NOT NULL,
itPermGroup TINYINT NOT NULL,
itExpireDate INT DEFAULT NULL,
itParentItemId MEDIUMINT DEFAULT NULL,
FOREIGN KEY (itCategoryId) REFERENCES Categories (catCategoryId),
FOREIGN KEY (itStateId) REFERENCES ItemStates (isStateId),
FOREIGN KEY (itTypeId) REFERENCES Itemtypes (ittTypeId),
FOREIGN KEY (itUserId) REFERENCES Users (uUserId),
PRIMARY KEY (itItemId)) TYPE = INNODB;


CREATE TABLE Usercategorys(
ucUserId MEDIUMINT NOT NULL,
ucCategoryId MEDIUMINT NOT NULL,
FOREIGN KEY (ucCategoryId) REFERENCES Categories (catCategoryId),
FOREIGN KEY (ucUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ucUserId,ucCategoryId)) TYPE = INNODB;


CREATE TABLE UserSupps(
usUserId MEDIUMINT NOT NULL,
usSignature VARCHAR(160) DEFAULT NULL,
usBiography TEXT DEFAULT NULL,
usFileQuoteAmb FLOAT,
usFirstName VARCHAR(40) DEFAULT NULL,
usLastName VARCHAR(40) DEFAULT NULL,
usHomepage VARCHAR(128) DEFAULT NULL,
FOREIGN KEY (usUserId) REFERENCES Users (uUserId),
PRIMARY KEY (usUserId)) TYPE = INNODB;


CREATE TABLE ItemWatchs(
iwItemId MEDIUMINT NOT NULL,
iwUserId MEDIUMINT NOT NULL,
FOREIGN KEY (iwItemId) REFERENCES Items (itItemId),
FOREIGN KEY (iwUserId) REFERENCES Users (uUserId),
PRIMARY KEY (iwItemId,iwUserId)) TYPE = INNODB;


CREATE TABLE UserWatchs(
uwUserId MEDIUMINT NOT NULL,
uwWatchUserId MEDIUMINT NOT NULL,
FOREIGN KEY (uwUserId) REFERENCES Users (uUserId),
FOREIGN KEY (uwWatchUserId) REFERENCES Users (uUserId),
PRIMARY KEY (uwUserId,uwWatchUserId)) TYPE = INNODB;


CREATE TABLE UserBuddies(
ubUserId MEDIUMINT NOT NULL,
ubBuddyUserId MEDIUMINT NOT NULL,
FOREIGN KEY (ubBuddyUserId) REFERENCES Users (uUserId),
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ubUserId,ubBuddyUserId)) TYPE = INNODB;


CREATE TABLE Languages(
lngLangId TINYINT DEFAULT 1 NOT NULL,
lngName VARCHAR(50),
PRIMARY KEY (lngLangId)) TYPE = INNODB;


CREATE TABLE BanReasons(
brReasonId MEDIUMINT NOT NULL,
brName VARCHAR(64) NOT NULL,
brDescription VARCHAR(255),
PRIMARY KEY (brReasonId)) TYPE = INNODB;


CREATE TABLE BanLists(
banId MEDIUMINT NOT NULL,
banUserId MEDIUMINT NOT NULL,
banReasonId MEDIUMINT NOT NULL,
banIp VARCHAR(15) DEFAULT NULL,
banEnd INT DEFAULT NULL,
banStart INT DEFAULT NULL,
FOREIGN KEY (banReasonId) REFERENCES BanReasons (brReasonId),
FOREIGN KEY (banUserId) REFERENCES Users (uUserId),
PRIMARY KEY (banId)) TYPE = INNODB;


CREATE TABLE Articlearchives(
aaItemId MEDIUMINT NOT NULL,
aaTitle VARCHAR(128) NOT NULL,
aaText TEXT NOT NULL,
PRIMARY KEY (aaItemId)) TYPE = INNODB;


CREATE TABLE Articles(
artArticleId MEDIUMINT NOT NULL,
artPages TINYINT DEFAULT 1 NOT NULL,
artTitle VARCHAR(128) NOT NULL,
artIntro TEXT,
FOREIGN KEY (artArticleId) REFERENCES Items (itItemId),
PRIMARY KEY (artArticleId)) TYPE = INNODB;


CREATE TABLE ArticlePages(
apPageNumber TINYINT NOT NULL,
apArticleId MEDIUMINT NOT NULL,
apText TEXT NOT NULL,
FOREIGN KEY (apArticleId) REFERENCES Articles (artArticleId),
PRIMARY KEY (apPageNumber,apArticleId)) TYPE = INNODB;


CREATE TABLE Ratings(
rtValue TINYINT NOT NULL,
rtLabel VARCHAR(25) NOT NULL,
PRIMARY KEY (rtValue)) TYPE = INNODB;


CREATE TABLE Comments(
cmtItemId MEDIUMINT NOT NULL,
cmtText TEXT NOT NULL,
cmtParentId MEDIUMINT,
cmtTitle VARCHAR(128),
FOREIGN KEY (cmtItemId) REFERENCES Items (itItemId),
PRIMARY KEY (cmtItemId)) TYPE = INNODB;


CREATE TABLE FileTypes(
ftFileTypeId TINYINT NOT NULL,
ftDescription VARCHAR(255) NOT NULL,
ftEextensions VARCHAR(255),
PRIMARY KEY (ftFileTypeId)) TYPE = INNODB;


CREATE TABLE Files(
fItemId MEDIUMINT NOT NULL,
ftFileTypeId TINYINT NOT NULL,
fFileTypeId TINYINT NOT NULL,
fSize VARCHAR(10) NOT NULL,
fDescription VARCHAR(255),
FOREIGN KEY (ftFileTypeId) REFERENCES FileTypes (ftFileTypeId),
FOREIGN KEY (fItemId) REFERENCES Items (itItemId),
PRIMARY KEY (fItemId)) TYPE = INNODB;


CREATE TABLE Blocks(
blkBlockItemId MEDIUMINT NOT NULL,
blkLocationId TINYINT NOT NULL,
blkUserConfigurable TINYINT DEFAULT 0 NOT NULL,
blkCollapsable TINYINT DEFAULT 0 NOT NULL,
blkNewWindows TINYINT DEFAULT 1 NOT NULL,
blkName VARCHAR(50) NOT NULL,
blkBlockType TINYINT NOT NULL,
blkTitle VARCHAR(50) NOT NULL,
blkCategoryId MEDIUMINT NOT NULL,
blkRdfUrl VARCHAR(128),
blkRdfUpdated INT,
blkBlockFn VARCHAR(64),
blkMaxItems TINYINT DEFAULT NULL,
blkSortNum TINYINT,
blkContent TEXT,
PRIMARY KEY (blkBlockItemId)) TYPE = INNODB;


CREATE TABLE Polls(
pPollId MEDIUMINT NOT NULL,
pVotes MEDIUMINT NOT NULL,
pQuestion VARCHAR(255) NOT NULL,
FOREIGN KEY (pPollId) REFERENCES Items (itItemId),
PRIMARY KEY (pPollId)) TYPE = INNODB;


CREATE TABLE PollAnswers(
paAnswerId MEDIUMINT NOT NULL,
paItemId MEDIUMINT NOT NULL,
paAnswer VARCHAR(255) NOT NULL,
paVotes MEDIUMINT NOT NULL,
FOREIGN KEY (paAnswerId) REFERENCES Polls (pPollId),
PRIMARY KEY (paAnswerId)) TYPE = INNODB;


CREATE TABLE PollVotes(
pvVoteId MEDIUMINT NOT NULL,
pvUserId MEDIUMINT NOT NULL,
pvPollId MEDIUMINT NOT NULL,
pvIpAddress VARCHAR(15) NOT NULL,
FOREIGN KEY (pvPollId) REFERENCES Polls (pPollId),
FOREIGN KEY (pvUserId) REFERENCES Users (uUserId),
PRIMARY KEY (pvVoteId)) TYPE = INNODB;


CREATE TABLE Vars(
varName VARCHAR(20) NOT NULL,
varValue VARCHAR(128) NOT NULL,
varDescription VARCHAR(255),
PRIMARY KEY (varName)) TYPE = INNODB;


CREATE TABLE UserBlocks(
ubUserId MEDIUMINT NOT NULL,
ubBlockItemId MEDIUMINT NOT NULL,
FOREIGN KEY (ubBlockItemId) REFERENCES Blocks (blkBlockItemId),
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ubUserId,ubBlockItemId)) TYPE = INNODB;


CREATE TABLE PrivateMessageStates(
pmsStateId TINYINT NOT NULL,
pmsName VARCHAR(128) NOT NULL,
PRIMARY KEY (pmsStateId)) TYPE = INNODB;


CREATE TABLE PrivateMessages(
pmMessageId MEDIUMINT NOT NULL,
pmDate INT NOT NULL,
pmStateId TINYINT NOT NULL,
pmText TEXT NOT NULL,
pmToUserId MEDIUMINT NOT NULL,
pmSubject VARCHAR(128),
pmFromUserId MEDIUMINT,
FOREIGN KEY (pmStateId) REFERENCES PrivateMessageStates (pmsStateId),
FOREIGN KEY (pmFromUserId) REFERENCES Users (uUserId),
PRIMARY KEY (pmMessageId)) TYPE = INNODB;


CREATE TABLE Modules(
modModuleId MEDIUMINT NOT NULL,
modName VARCHAR(30) NOT NULL,
modEnabled TINYINT DEFAULT 1 NOT NULL,
modAdminSortNum TINYINT,
modUserSortNum TINYINT,
PRIMARY KEY (modModuleId)) TYPE = INNODB;


CREATE TABLE aaApplications(
appId VARCHAR(30) NOT NULL,
appName VARCHAR(50) NOT NULL,
appContactEmail VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (appId)) TYPE = INNODB;


CREATE TABLE aaAppPrivileges(
apAppId VARCHAR(30) NOT NULL,
apPrivilegeCd VARCHAR(20) NOT NULL,
apPrivilegeDesc VARCHAR(128) NOT NULL,
FOREIGN KEY (apAppId) REFERENCES aaApplications (appId),
PRIMARY KEY (apAppId,apPrivilegeCd)) TYPE = INNODB;


CREATE TABLE aaGroups(
gGroupId INT NOT NULL AUTO_INCREMENT,
gAppId VARCHAR(30) NOT NULL,
gGroupLogicaName VARCHAR(50) NOT NULL,
gGroupDisplayName VARCHAR(50) NOT NULL,
gGroupDescr VARCHAR(255) NOT NULL,
gCreationDate INT DEFAULT 0 NOT NULL,
gChangeDate INT DEFAULT NULL,
gChangeUserId MEDIUMINT DEFAULT NULL,
FOREIGN KEY (gAppId) REFERENCES aaApplications (appId),
PRIMARY KEY (gGroupId)) TYPE = INNODB;


CREATE TABLE aaPrivilegeAccesses(
paPrivilegeCd VARCHAR(20) NOT NULL,
paAppId VARCHAR(30) NOT NULL,
paUserId MEDIUMINT DEFAULT NULL,
paGroupId MEDIUMINT DEFAULT NULL,
FOREIGN KEY (paPrivilegeCd,paAppId) REFERENCES aaAppPrivileges 
(apPrivilegeCd,apAppId),
PRIMARY KEY (paPrivilegeCd,paAppId)) TYPE = INNODB;


CREATE TABLE aaUsers(
uUserId INT NOT NULL AUTO_INCREMENT,
uChangeDate INT DEFAULT 0 NOT NULL,
uUserName VARCHAR(75) NOT NULL,
uPassword VARCHAR(20) NOT NULL,
uEmployeId VARCHAR(20) NOT NULL,
uAccountLocked TINYINT DEFAULT 0 NOT NULL,
uFailedAttempts TINYINT DEFAULT 0 NOT NULL,
uPwdLastSet INT DEFAULT 0 NOT NULL,
uCreationDate INT DEFAULT 0 NOT NULL,
uChangeUserId MEDIUMINT DEFAULT NULL,
PRIMARY KEY (uUserId)) TYPE = INNODB;


CREATE TABLE aaUserPasswords(
upUserId INT NOT NULL,
upPassword VARCHAR(20) NOT NULL,
upInsertDate VARCHAR(20) NOT NULL,
FOREIGN KEY (upUserId) REFERENCES aaUsers (uUserId),
PRIMARY KEY (upUserId,upPassword,upInsertDate)) TYPE = INNODB;


CREATE TABLE Links(
lnkItemId MEDIUMINT NOT NULL,
lnkTitle VARCHAR(128) NOT NULL,
lnkUrl VARCHAR(128) NOT NULL,
lnkDescription TEXT,
FOREIGN KEY (lnkItemId) REFERENCES Items (itItemId),
PRIMARY KEY (lnkItemId)) TYPE = INNODB;


CREATE TABLE aaGroupAssignments(
gaMainGroupId INT NOT NULL,
gaUserId INT NOT NULL,
gaAssignedGroupId MEDIUMINT DEFAULT NULL,
gaPrivilegeCache VARCHAR(255) DEFAULT NULL,
FOREIGN KEY (gaMainGroupId) REFERENCES aaGroups (gGroupId),
FOREIGN KEY (gaUserId) REFERENCES aaUsers (uUserId)) TYPE = INNODB;



Dwight
dwight at trumbower.com