求助：简单论坛的数据库设计

[天堂里的死神]

这是一个课程设计，功能非常简单，只用到了用户（用户ID，用户名，用户密码，用户简短介绍……）和主题（主题ID，主题名，主题内容，主题发布时间……）还有回帖（回帖ID，回帖名，回帖内容，回帖发布时间，所回贴的ID……）
想问的问题是，究竟如何组织这三个表并使之符合第三范式？？主键应该是哪些？有什么联系？？

[天堂里的死神]

up

[lynx1111]

Jive论坛有名的jsp论坛
/* ============================================================ */
/* $RCSfile: Jive_sqlserver_7.sql,v $ */
/* $RevisiON: 1.6 $ */
/* $Date: 2002/09/09 13:50:35 $ */
/* ============================================================ */

CREATE TABLE jiveForum (
forumID INT NOT NULL,
name VARCHAR(255) UNIQUE NOT NULL,
description TEXT NULL,
modDefaultThreadVal INT NOT NULL,
modMinThreadVal INT NOT NULL,
modDefaultMsgVal INT NOT NULL,
modMinMsgVal INT NOT NULL,
modifiedDate VARCHAR(15) NOT NULL,
creationDate VARCHAR(15) NOT NULL,
CONSTRAINT forum_pk PRIMARY KEY (forumID)
);


CREATE TABLE jiveForumProp (
forumID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
CONSTRAINT forumProp_pk PRIMARY KEY (forumID, name)
);


CREATE TABLE jiveThread (
threadID INT NOT NULL,
forumID INT NOT NULL,
rootMessageID INT NOT NULL,
modValue INT NOT NULL,
rewardPoints INT NOT NULL,
creationDate CHAR(15) NOT NULL,
modifiedDate CHAR(15) NOT NULL,
CONSTRAINT thread_pk PRIMARY KEY (threadID)
);
CREATE INDEX jiveThread_forumID_idx ON jiveThread (forumID);
CREATE INDEX jiveThread_modValue_idx ON jiveThread (modValue);
CREATE INDEX jiveThread_cDate_idx ON jiveThread (creationDate);
CREATE INDEX jiveThread_mDate_idx ON jiveThread (modifiedDate);


CREATE TABLE jiveThreadProp (
threadID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
CONSTRAINT jiveThreadProp_pk PRIMARY KEY (threadID, name)
);


CREATE TABLE jiveMessage (
messageID INT NOT NULL,
parentMessageID INT NULL,
threadID INT NOT NULL,
forumID INT NOT NULL,
userID INT NULL,
subject VARCHAR(255) NULL,
body TEXT NULL,
modValue INT NOT NULL,
rewardPoints INT NOT NULL,
creationDate CHAR(15) NOT NULL,
modifiedDate CHAR(15) NOT NULL,
CONSTRAINT message_pk PRIMARY KEY (messageID)
);
CREATE INDEX jiveMessage_threadID_idx ON jiveMessage (threadID);
CREATE INDEX jiveMessage_forumID_modVal_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jiveMessage_userID_idx ON jiveMessage (userID);
CREATE INDEX jiveMessage_cDate_idx ON jiveMessage (creationDate);
CREATE INDEX jiveMessage_mDate_idx ON jiveMessage (modifiedDate);


CREATE TABLE jiveMessageProp (
messageID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
CONSTRAINT messagePropPK PRIMARY KEY (messageID, name)
);


CREATE TABLE jiveUser (
userID INT NOT NULL,
username VARCHAR(30) UNIQUE NOT NULL,
passwordHash VARCHAR(32) NOT NULL,
name VARCHAR(100) NULL,
nameVisible INT NOT NULL,
email VARCHAR(100) NOT NULL,
emailVisible INT NOT NULL,
rewardPoints INT NOT NULL,
creationDate CHAR(15) NOT NULL,
modifiedDate CHAR(15) NOT NULL,
CONSTRAINT jiveUser_pk PRIMARY KEY (userID)
);
CREATE INDEX jiveUser_cDate_idx ON jiveUser (creationDate);


CREATE TABLE jiveUserPerm (
forumID INT NULL,
userID INT NULL,
userType INT NOT NULL,
permission INT NOT NULL,
);
CREATE INDEX jiveUserPerm_forumID_idx ON jiveUserPerm (forumID);
CREATE INDEX jiveUserPerm_userID_idx ON jiveUserPerm (userID);


CREATE TABLE jiveUserProp (
userID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
CONSTRAINT jiveUserProp_pk PRIMARY KEY (userID, name)
);


CREATE TABLE jiveGroup (
groupID INT NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(255) NULL,
modifiedDate CHAR(15) NOT NULL,
creationDate CHAR(15) NOT NULL,
CONSTRAINT group_pk PRIMARY KEY (groupID)
);
CREATE INDEX jiveGroup_cDate_idx ON jiveGroup (creationDate);

[lynx1111]

CREATE TABLE jiveGroupPerm (
forumID INT NOT NULL,
groupID INT NOT NULL,
permission INT NOT NULL,
CONSTRAINT groupPerm_pk PRIMARY KEY (forumID, groupID, permission)
);
CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID);


CREATE TABLE jiveGroupProp (
groupID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupID, name)
);


CREATE TABLE jiveGroupUser (
groupID INT NOT NULL,
userID INT NOT NULL,
administrator INT NOT NULL,
CONSTRAINT groupUserPK PRIMARY KEY (groupID, userID)
);
CREATE INDEX jiveGroupUser_userID_idx ON jiveGroupUser (userID);


CREATE TABLE jiveID (
idType INT NOT NULL,
id INT NOT NULL,
constraint jiveID_pk primary key (idType)
);


CREATE TABLE jiveModeration (
objectID INT NOT NULL,
objectType INT NOT NULL,
userID INT NULL,
modDate VARCHAR(15) NOT NULL,
modValue INT NOT NULL,
);
CREATE INDEX jiveModeration_objectID_idx ON jiveModeration (objectID);
CREATE INDEX jiveModeration_userID_idx ON JiveModeration (userID);
CREATE INDEX jiveModeration_objectType_idx ON JiveModeration (objectType);


CREATE TABLE jiveWatch (
userID INT NOT NULL,
forumID INT NOT NULL,
threadID INT NOT NULL,
watchType INT NOT NULL,
expirable INT NOT NULL,
CONSTRAINT jiveWatch_pk PRIMARY KEY (userID, threadID, watchType)
);
CREATE INDEX jiveWatch_userID_idx ON jiveWatch (userID);
CREATE INDEX jiveWatch_forumID_idx ON jiveWatch (forumID);
CREATE INDEX jiveWatch_threadID_idx ON jiveWatch (threadID);
CREATE INDEX jiveWatch_type_idx ON jiveWatch (watchType);


CREATE TABLE jiveReward (
userID INT NOT NULL,
creationDate VARCHAR(15) NOT NULL,
rewardPoints INT NOT NULL,
messageID INT NULL,
threadID INT NULL
);
CREATE INDEX jiveReward_userID_idx ON jiveReward (userID);
CREATE INDEX jiveReward_creationDate_idx ON jiveReward (creationDate);
CREATE INDEX jiveReward_messageID_idx ON jiveReward (messageID);
CREATE INDEX jiveReward_threadID_idx ON jiveReward (threadID);


ALTER TABLE jiveForumProp ADD CONSTRAINT jiveForumProp_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

/*
Note: need to figure out how to make constraints deferrable
under SQL Server. Until then, disable certain fk's.

ALTER TABLE jiveThread ADD CONSTRAINT jiveThread_rootMsgID_fk
FOREIGN KEY (rootMessageID) REFERENCES jiveMessage(messageID);*/

ALTER TABLE jiveThreadProp ADD CONSTRAINT jiveThreadProp_threadID_fk
FOREIGN KEY (threadID) REFERENCES jiveThread;

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_parentMsgID_fk
FOREIGN KEY (parentMessageID) REFERENCES jiveMessage(messageID);

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_threadID_fk
FOREIGN KEY (threadID) REFERENCES jiveThread;

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveMessage ADD CONSTRAINT jiveMessage_userID_fk
FOREIGN KEY (userID) REFERENCES jiveUser;

ALTER TABLE jiveMessageProp ADD CONSTRAINT jiveMessageProp_msgID_fk
FOREIGN KEY (messageID) REFERENCES jiveMessage;

ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveUserPerm ADD CONSTRAINT jiveUserPerm_userID_fk
FOREIGN KEY (userID) REFERENCES jiveUser;

ALTER TABLE jiveUserProp ADD CONSTRAINT jiveUserProp_userID_fk
FOREIGN KEY (userID) REFERENCES jiveUser;

ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_forumID_fk
FOREIGN KEY (forumID) REFERENCES jiveForum;

ALTER TABLE jiveGroupPerm ADD CONSTRAINT jiveGroupPerm_groupID_fk
FOREIGN KEY (groupID) REFERENCES jiveGroup;

ALTER TABLE jiveGroupProp ADD CONSTRAINT jiveGroupProp_groupID_fk
FOREIGN KEY (groupID) REFERENCES jiveGroup;

ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_groupID_fk
FOREIGN KEY (groupID) REFERENCES jiveGroup;

ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser_userID_fk
FOREIGN KEY (userID) REFERENCES jiveUser;

[lynx1111]

/* Unique ID entry for forum, thread, messages, user, group. */
/* The User ID entry starts at 2 (after admin user entry). */
INSERT INTO jiveID VALUES (0, 1);
INSERT INTO jiveID VALUES (1, 1);
INSERT INTO jiveID VALUES (2, 1);
INSERT INTO jiveID VALUES (3, 2);
INSERT INTO jiveID VALUES (4, 1);


/* Entry for admin user -- password is 'admin' */
INSERT INTO jiveUser (userID,username,passwordHash,name,nameVisible,email,emailVisible,rewardPoints,creationDate,modifiedDate)
VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3','Administrator',1,'[e-mail]',1,0,'0','0');
INSERT INTO jiveUserPerm(forumID,userID,userType,permission)
VALUES (NULL,1,3,1);


/*
* These are commented out to avoid running them and inadverently
* trashing our tables. Hilight within isqlw and press F5 to run.
*
* The following constraint was disabled above for the time being:
* alter table jiveThread drop constraint jiveThread_rootMsgID_fk ;
*/
/*
alter table jiveForumProp drop constraint jiveForumProp_forumID_fk;
alter table jiveThread drop constraint jiveThread_forumID_fk ;
alter table jiveThreadProp drop constraint jiveThreadProp_threadID_fk ;
alter table jiveMessage drop constraint jiveMessage_parentMsgID_fk;
alter table jiveMessage drop constraint jiveMessage_threadID_fk;
alter table jiveMessage drop constraint jiveMessage_forumID_fk;
alter table jiveMessage drop constraint jiveMessage_userID_fk;
alter table jiveMessageProp drop constraint jiveMessageProp_msgID_fk;
alter table jiveUserPerm drop constraint jiveUserPerm_forumID_fk;
alter table jiveUserPerm drop constraint jiveUserPerm_userID_fk;
alter table jiveUserProp drop constraint jiveUserProp_userID_fk;
alter table jiveGroupPerm drop constraint jiveGroupPerm_forumID_fk;
alter table jiveGroupPerm drop constraint jiveGroupPerm_groupID_fk;
alter table jiveGroupProp drop constraint jiveGroupProp_groupID_fk;
alter table jiveGroupUser drop constraint jiveGroupUser_groupID_fk;
alter table jiveGroupUser drop constraint jiveGroupUser_userID_fk;

drop table jiveForum;
drop table jiveForumProp;
drop table jiveThread;
drop table jiveThreadProp;
drop table jiveMessage;
drop table jiveMessageProp;
drop table jiveUser;
drop table jiveUserPerm;
drop table jiveUserProp;
drop table jiveGroup;
drop table jiveGroupPerm;
drop table jiveGroupProp;
drop table jiveGroupUser;
drop table jiveID;
drop table jiveModeration;
drop table jiveWatch;
drop table jiveReward;
*/

[flashsj]

up

[hjb111]

用户（用户ID，用户名，用户密码，用户简短介绍……）
主题（主题ID，用户id,主题名，主题内容，主题发布时间……）
回贴（回帖ID，用户id,回帖名，回帖内容，回帖发布时间，所回贴的ID……）

[hjb111]

回贴还应加主题id列

[tttzms]

up

[天堂里的死神]

多谢诸位的回复，还有一个问题：
如果按照目前的划分，用户将与两个表都发生联系，并且主题和回贴之间也有联系，
那么，这算不算出现了传递依赖呢？？

[天堂里的死神]

哗
多谢lynx1111(任我行)了，我先研究一下。