论坛管理系统数据库设计
- 数据库
- 数据表
- 设计索引
- 设计视图
- 设计触发器
数据库
create database bbs;
use bbs;
数据表
create table user(
uID INT PRIMARY KEY UNIQUE NOT NULL,
userName VARCHAR(20) NOT NULL,
userPassword VARCHAR(20) NOT NULL,
userEmail VARCHAR(10) NOT NULL,
userBirthday DATE NOT NULL,
userSex BIT NOT NULL,
userClass INT NOT NULL,
userStatement VARCHAR(150) NOT NULL,
userRegDate TIMESTAMP NOT NULL,
userPoint INT NOT NULL
);
create table admin(
adminID INT PRIMARY KEY UNIQUE NOT NULL,
adminName VARCHAR(20) NOT NULL,
adminPassword VARCHAR(20) NOT NULL
);
create table section(
sID INT PRIMARY KEY UNIQUE NOT NULL,
sName VARCHAR(20) NOT NULL,
sMasterID INT NOT NULL,
sStatement VARCHAR(150) NOT NULL,
sClickCount INT NOT NULL,
sTopicCount INT NOT NULL
);
create table topic(
tID INT PRIMARY KEY UNIQUE NOT NULL,
sID INT NOT NULL,
uID INT NOT NULL,
tReplyCount INT NOT NULL,
tEmotion VARCHAR(20) NOT NULL,
tTopic VARCHAR(20) NOT NULL,
tContents TEXT NOT NULL,
tTime TIMESTAMP NOT NULL,
tClickCount INT NOT NULL,
tLastClickT TIMESTAMP NOT NULL
);
create table reply(
rID INT PRIMARY KEY UNIQUE NOT NULL,
tID INT NOT NULL,
uID INT NOT NULL,
rEmotion VARCHAR(20) NOT NULL,
rToptic VARCHAR(20) NOT NULL,
rContents TEXT NOT NULL,
rTime TIMESTAMP NOT NULL,
rClickCount INT NOT NULL
);
注:
MySQL不允许在BLOB/TEXT,TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, LONGTEXT,VARCHAR建索引,因为前面那些列类型都是可变长的,MySQL无法保证列的唯一性,只能在BLOB/TEXT前n个字节上建索引
设计索引
create index index_topic_topic on topic(tTopic);
create index index_topic_time on topic(tTime);
create index index_topic_contents on topic(tContents);
alter table topic add index index_topic_contents (tContents);
create index index_section_name on section(sName);
create index index_reply_time on reply(rTime);
create index index_reply_topic on reply(rTopic);
create index index_reply_id on reply(rID);
设计视图
#展示版块及版块下面的主贴
create view topic_view
as select s.sID,s.sName,t.tTopic,t.tContents,t.tTime
from section s,topic t
where s.sID=t.sID;
设计触发器
#新增版块后,更新主题数
delimiter &&
create trigger section_count after insert
on section for each row
begin update section set sTopicCount = sTopicCount+1 where sID=new.sID;
end
&&
delimiter ; #user表中的uID更新,同时更新reply中的uID
delimiter &&
create trigger update_uID after update
on user for each row
begin update reply set uID=new.userID;
end
&&
delimiter ;