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;
无语,少支持一下吧,咔咔..
