数据库的MYSQL代码脚本

DROP DATABASE IF EXISTS BCCNBBS;
CREATE DATABASE BCCNBBS;
USE BCCNBBS;



CREATE TABLE USER (
ID INT NOT NULL auto_increment,
USERNAME CHAR(20) NOT NULL UNIQUE,
PASSWORD CHAR(20) NOT NULL,
NAME CHAR(20) NOT NULL,
SEX CHAR(2) NOT NULL,
EMAIL CHAR(50) NULL,
SCORE INT NOT NULL,
ENROLL_DATE DATE,
LOGIN_TIMES INT,
LAST_LOGIN DATE,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE PAGE (
ID INT NOT NULL auto_increment,
NAME CHAR(20) NOT NULL UNIQUE,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE THEME (
ID INT NOT NULL auto_increment,
PAGE_ID INT NOT NULL,
AUTHOR INT NOT NULL,
PUBLIC_DATE DATE NOT NULL,
PUBLIC_TIME TIME NOT NULL,
CONTENT TEXT NOT NULL,
VIEW_STATUS CHAR(10) NOT NULL,
LOCK_STATUS CHAR(10) NOT NULL,
SCORE INT NOT NULL,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE REPLY (
ID INT NOT NULL auto_increment,
THEME_ID INT NOT NULL,
AUTHOR INT NOT NULL,
PUBLIC_DATE DATE NOT NULL,
PUBLIC_TIME TIME NOT NULL,
CONTENT TEXT NOT NULL,
VIEW_STATUS CHAR(10) NOT NULL,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE MANAGER (
USER_ID INT NOT NULL,
POWER CHAR(10) NOT NULL,
PAGE_ID INT NOT NULL,
PRIMARY KEY (USER_ID,POWER,PAGE_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE SCORE_OPERATE (
ID INT NOT NULL auto_increment,
SENDUSER_ID INT NOT NULL,
RECEIVEUSER_ID INT NOT NULL,
SCORE_VALUE INT NOT NULL,
OPERATE_DATE DATE NOT NULL,
OPERATE_TIME TIME NOT NULL,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE THEME ADD CONSTRAINT FK_THEME_PAGE
FOREIGN KEY(PAGE_ID) REFERENCES PAGE(ID) ON DELETE CASCADE;

ALTER TABLE THEME ADD CONSTRAINT FK_THEMEL_USER
FOREIGN KEY(AUTHOR) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE REPLY ADD CONSTRAINT FK_REPLY_THEME
FOREIGN KEY(THEME_ID) REFERENCES THEME(ID) ON DELETE CASCADE;

ALTER TABLE REPLY ADD CONSTRAINT FK_REPLY_USER
FOREIGN KEY(AUTHOR) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE MANAGER ADD CONSTRAINT FK_MANAGER_USER
FOREIGN KEY(USER_ID) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE MANAGER ADD CONSTRAINT FK_MANAGER_PAGE
FOREIGN KEY(PAGE_ID) REFERENCES PAGE(ID) ON DELETE CASCADE;

ALTER TABLE SCORE_OPERATE ADD CONSTRAINT FK_SCORE_OPERATE_USER_SENDUSER
FOREIGN KEY(SENDUSER_ID) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE SCORE_OPERATE ADD CONSTRAINT FK_SCORE_OPERATE_USER_RECEIVEUSER
FOREIGN KEY(RECEIVEUSER_ID) REFERENCES USER(ID) ON DELETE CASCADE;



恩，一旦写完脚本，就意味着应该转到技术论坛了

to 牧羊：本来就是个小项目，没必要再拖延下去啦～而且快开学了，我的时间也挺紧张的

to chenjin145:
1. 这个这个是个人习惯啦～呵呵，外键的字段名如果有实际意义：比如说作者AUTHOR，那么我一般都直接用它的实际意义作为字段名。
如果没有什么实际意义，那么就只好用表名称+_ID来作为字段名了：比如PAGE_ID。


2. 版块表的确没有分下去的拓展性。谢谢提醒，现在在PAGE表中新添加一个FATHER字段，表明这个版块从属于那个版块

USER TABLE:用户表
------------------------------------------
ID INT, id
USERNAME CHAR(20), 帐号
PASSWORD CHAR(20), 密码
NAME CHAR(20), 姓名
SEX CHAR(2), 性别
EMAIL CHAR(50), 电子邮件
SCORE INT, 积分
ENROLL_DATE DATE, 注册日期
LOGIN_TIMES INT, 登录次数
LAST_LOGIN DATE, 最后一次登录日期






PAGE TABLE:版块表
------------------------------------------
ID INT, id
FATHER INT, 从属于那一版块,如果FATHER为NULL，或者等于自身版块ID，那么表明这个是根版块。
NAME CHAR(50), 版块名称







THEME TABLE:主题表
------------------------------------------
ID INT, id
PAGE_ID INT, 主题所在版块
AUTHOR INT, 主题作者－－用户ID
PUBLIC_DATE DATE, 主题发表日期
PUBLIC_TIME TIME, 主题发表时间
CONTENT TEXT, 主题内容
VIEW_STATUS CHAR(10), 主题浏览状态public－公开、shield-屏蔽、prime-精华
LOCK_STATUS CHAR(10), 主题回复状态lock-锁定、unlock-非锁定
SCORE INT, 主题分值




REPLY TABLE:回复表
------------------------------------------
ID INT, id
THEME_ID INT, 回复所在主题
AUTHOR INT, 回复作者
PUBLIC_DATE DATE, 回复发表日期
PUBLIC_TIME TIME, 回复发表时间
CONTENT TEXT, 回复内容
VIEW_STATUS CHAR(10), 回复浏览状态public－公开、shield-屏蔽、prime-精华






MANAGER TABLE:管理人员表
------------------------------------------
USER_ID INT, 用户ID
POWER CHAR(10) 权力master-版主、super-超级版主、admin-管理员
PAGE_ID INT, 如果POWER=master这个字段才有意义：版主所负责的版块，一个版主只可以负责一个版块








SCORE_OPERATE TABLE:分值操作记录表
------------------------------------------
ID INT,
SENDUSER_ID INT,
RECEIVEUSER_ID INT,
SCORE_VALUE INT,
OPERATE_DATE DATE,
OPERATE_TIME TIME,






DROP DATABASE IF EXISTS BCCNBBS;
CREATE DATABASE BCCNBBS;
USE BCCNBBS;



CREATE TABLE USER (
ID INT NOT NULL auto_increment,
USERNAME CHAR(20) NOT NULL UNIQUE,
PASSWORD CHAR(20) NOT NULL,
NAME CHAR(20) NOT NULL,
SEX CHAR(2) NOT NULL,
EMAIL CHAR(50) NULL,
SCORE INT NOT NULL,
ENROLL_DATE DATE,
LOGIN_TIMES INT,
LAST_LOGIN DATE,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE PAGE (
ID INT NOT NULL auto_increment,
FATHER INT NULL,
NAME CHAR(20) NOT NULL UNIQUE,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE THEME (
ID INT NOT NULL auto_increment,
PAGE_ID INT NOT NULL,
AUTHOR INT NOT NULL,
PUBLIC_DATE DATE NOT NULL,
PUBLIC_TIME TIME NOT NULL,
CONTENT TEXT NOT NULL,
VIEW_STATUS CHAR(10) NOT NULL,
LOCK_STATUS CHAR(10) NOT NULL,
SCORE INT NOT NULL,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE REPLY (
ID INT NOT NULL auto_increment,
THEME_ID INT NOT NULL,
AUTHOR INT NOT NULL,
PUBLIC_DATE DATE NOT NULL,
PUBLIC_TIME TIME NOT NULL,
CONTENT TEXT NOT NULL,
VIEW_STATUS CHAR(10) NOT NULL,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE MANAGER (
USER_ID INT NOT NULL,
POWER CHAR(10) NOT NULL,
PAGE_ID INT NOT NULL,
PRIMARY KEY (USER_ID,POWER,PAGE_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE SCORE_OPERATE (
ID INT NOT NULL auto_increment,
SENDUSER_ID INT NOT NULL,
RECEIVEUSER_ID INT NOT NULL,
SCORE_VALUE INT NOT NULL,
OPERATE_DATE DATE NOT NULL,
OPERATE_TIME TIME NOT NULL,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;








ALTER TABLE PAGE ADD CONSTRAINT FK_PAGE_PAGE_FATHER
FOREIGN KEY(FATHER) REFERENCES PAGE(ID) ON DELETE CASCADE;

ALTER TABLE THEME ADD CONSTRAINT FK_THEME_PAGE
FOREIGN KEY(PAGE_ID) REFERENCES PAGE(ID) ON DELETE CASCADE;

ALTER TABLE THEME ADD CONSTRAINT FK_THEMEL_USER_AUTHOR
FOREIGN KEY(AUTHOR) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE REPLY ADD CONSTRAINT FK_REPLY_THEME
FOREIGN KEY(THEME_ID) REFERENCES THEME(ID) ON DELETE CASCADE;

ALTER TABLE REPLY ADD CONSTRAINT FK_REPLY_USER_AUTHOR
FOREIGN KEY(AUTHOR) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE MANAGER ADD CONSTRAINT FK_MANAGER_USER
FOREIGN KEY(USER_ID) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE MANAGER ADD CONSTRAINT FK_MANAGER_PAGE
FOREIGN KEY(PAGE_ID) REFERENCES PAGE(ID) ON DELETE CASCADE;

ALTER TABLE SCORE_OPERATE ADD CONSTRAINT FK_SCORE_OPERATE_USER_SENDUSER
FOREIGN KEY(SENDUSER_ID) REFERENCES USER(ID) ON DELETE CASCADE;

ALTER TABLE SCORE_OPERATE ADD CONSTRAINT FK_SCORE_OPERATE_USER_RECEIVEUSER
FOREIGN KEY(RECEIVEUSER_ID) REFERENCES USER(ID) ON DELETE CASCADE;

汗，你们高估我来着
我纯粹是想为J2EE版块写一个论坛教程什么的，然后好比较详细的解释一下所采用的技术，比如Struts、HIBERNATE、SPRING等
并不是真正想做一个论坛，只是觉得有个实际的例子好说话点。

至于什么数据流和UML等，这样小的项目我自己会在草稿纸上写，不会弄上来的。
大家不要把这个小项目想得太大，之所以想让大家参与，是方便以后大家理解这个论坛的工作原理什么的。

我现在的所有知识，从需求分析到具体代码的实现，中间还参杂着数据库设计、设计模式、软件工程等等，完完全全是自学的，大学到底教会了我什么啊！对自己学的知识还不是很有信心，因此也想让大家帮忙参考一下。

管理员使用马甲我觉得很合理呀，有什么不妥的地方呢？

如果有软件工程版块，那么能在软件工程版块称雄的，必定是能写代码的。

当然，这不是充分必要关系

不爽，被数据库老师骂了～

struts spring hibernate
问题是数据库设计被我老师骂了，说我白当她学生
哎，再多看点数据库设计的书籍再说吧

你们不在场，不见到我被骂得有多惨～设么愧对恩师啊，愧对父母啊。。就差愧对GF了