[geeklog-devel] MySQL Schema

Thu Jan 23 01:28:47 EST 2003

Here is the first draft of geeklog2 schema for mysql. Basically it is loaded into a design tool and the relationships where made

A diagram can be viewed at http://www.tsystemscorp.com/geeklog/mysql.html
http://www.tsystemscorp.com/geeklog/geeklog2mysql.sql is the link for the file.

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

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

CREATE TABLE Categories(
catCategoryId 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,
catOwnerId MEDIUMINT NOT NULL,
catSortNum TINYINT DEFAULT NULL,
catImageUrl VARCHAR(128) DEFAULT NULL,
PRIMARY KEY (catCategoryId)) TYPE = InnoDB;

CREATE TABLE Users(
uUserId MEDIUMINT 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,
uRegDate INT NOT NULL,
uCommentOrderId TINYINT,
uCommentLimit MEDIUMINT DEFAULT NULL,
uCookieTimeout MEDIUMINT,
uLocale VARCHAR(3),
uDateFormatId TINYINT,
uBlocksEnabled TINYINT DEFAULT 1,
uCommentModeId TINYINT,
PRIMARY KEY (uUserId)) TYPE = InnoDB;

CREATE TABLE Items(
itItemId MEDIUMINT 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,
itPermMembers TINYINT NOT NULL,
itParentItemId MEDIUMINT DEFAULT NULL,
itExpireDate INT 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(
ucCategoryId MEDIUMINT NOT NULL,
ucUserId MEDIUMINT NOT NULL,
FOREIGN KEY (ucCategoryId) REFERENCES Categories (catCategoryId),
FOREIGN KEY (ucUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ucCategoryId,ucUserId)) TYPE = InnoDB;

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

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

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

CREATE TABLE UserBuddies(
ubBuddyUserId MEDIUMINT NOT NULL,
ubUserId MEDIUMINT NOT NULL,
FOREIGN KEY (ubBuddyUserId) REFERENCES Users (uUserId),
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ubBuddyUserId,ubUserId)) 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,
banReasonId MEDIUMINT NOT NULL,
banUserId MEDIUMINT NOT NULL,
banEnd INT DEFAULT NULL,
banStart INT DEFAULT NULL,
banIp VARCHAR(15) 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,
aaText TEXT NOT NULL,
aaTitle VARCHAR(128) NOT NULL,
PRIMARY KEY (aaItemId)) TYPE = InnoDB;

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

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

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

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

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

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

CREATE TABLE Blocks(
blkBlockItemId MEDIUMINT 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,
blkLocationId TINYINT NOT NULL,
blkRdfUpdated INT,
blkBlockFn VARCHAR(64),
blkMaxItems TINYINT DEFAULT NULL,
blkSortNum TINYINT,
blkContent TEXT,
blkRdfUrl VARCHAR(128),
PRIMARY KEY (blkBlockItemId)) TYPE = InnoDB;

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

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

CREATE TABLE PollVotes(
pvVoteId MEDIUMINT NOT NULL,
pvPollId MEDIUMINT NOT NULL,
pvIpAddress VARCHAR(15) NOT NULL,
pvUserId MEDIUMINT 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(
ubBlockItemId MEDIUMINT NOT NULL,
ubUserId MEDIUMINT NOT NULL,
FOREIGN KEY (ubBlockItemId) REFERENCES Blocks (blkBlockItemId),
FOREIGN KEY (ubUserId) REFERENCES Users (uUserId),
PRIMARY KEY (ubBlockItemId,ubUserId)) 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,
pmStateId TINYINT NOT NULL,
pmText TEXT NOT NULL,
pmToUserId MEDIUMINT NOT NULL,
pmDate INT NOT NULL,
pmFromUserId MEDIUMINT,
pmSubject VARCHAR(128),
FOREIGN KEY (pmStateId) REFERENCES PrivateMessageStates (pmsStateId),
FOREIGN KEY (pmFromUserId) REFERENCES Users (uUserId),
PRIMARY KEY (pmMessageId)) TYPE = InnoDB;

CREATE TABLE Modules(
modModuleId MEDIUMINT NOT NULL,
modEnabled TINYINT DEFAULT 1 NOT NULL,
modName VARCHAR(30) NOT NULL,
modUserSortNum TINYINT,
modAdminSortNum 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(
apPrivilegeCd VARCHAR(20) NOT NULL,
apPrivilegeDesc VARCHAR(128) NOT NULL,
apAppId VARCHAR(30) NOT NULL,
FOREIGN KEY (apAppId) REFERENCES aaApplications (appId),
PRIMARY KEY (apPrivilegeCd,apAppId)) TYPE = InnoDB;

CREATE TABLE aaGroups(
gGroupId INT NOT NULL AUTO_INCREMENT,
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,
gAppId VARCHAR(30) NOT NULL,
FOREIGN KEY (gAppId) REFERENCES aaApplications (appId),
PRIMARY KEY (gGroupId)) TYPE = InnoDB;

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

CREATE TABLE aaUsers(
uUserId INT NOT NULL AUTO_INCREMENT,
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,
uChangeDate INT DEFAULT 0 NOT NULL,
uChangeUserId MEDIUMINT DEFAULT NULL,
PRIMARY KEY (uUserId)) TYPE = InnoDB;

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

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

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist8.pair.net/pipermail/geeklog-devel/attachments/20030123/0ae410e6/attachment.html>