聊天室系统数据库设计案例

聊天室系统数据库设计一、数据字典

用户表(users)

角色表(roles)

好友关系表(friendship)

分组表(user_group)

聊天记录表(chat_log)

二、E-R图

三、物理模型图

用户

角色

聊天记录分组

好友关系

CREATE DATABASE chatingroom

CREATE TABLE roles

(

roleid int CONSTRAINT r_PRIM PRIMARY KEY,

rolename varchar(20) NOT NULL,

)

CREATE TABLE users

(userid int CONSTRAINT u_PRIM PRIMARY KEY,

username varchar (20) NOT NULL,

nickname varchar(20) NOT NULL,

telephone varchar(20) NOT NULL,

email varchar (20),

age int check(age between 18 and 60),

sex varchar(4) check(sex='男'or sex='女'),

address varchar(200) DEFAULT'地址不详',

remark varchar (200),

roleid int CONSTRAINT r_FORE FOREIGN KEY REFERENCES roles(roleid),

status int DEFAULT'1',

password varchar(10) DEFAULT'123',

)

CREATE TABLE user_group

(

groupid int CONSTRAINT g_PRIM PRIMARY KEY,

groupname varchar(20) NOT NULL,

)

CREATE TABLE friendship

(

userid int CONSTRAINT u_FORE FOREIGN KEY REFERENCES users(userid), friendid int CONSTRAINT f_FORE FOREIGN KEY REFERENCES users(userid), gruopid int CONSTRAINT g_FORE FOREIGN KEY REFERENCES user_group(groupid), )

CREATE TABLE chat_log

(

logid int CONSTRAINT l_PRIM PRIMARY KEY,

senderid int CONSTRAINT s_FORE FOREIGN KEY REFERENCES users(userid), recriverid int CONSTRAINT m_FORE FOREIGN KEY REFERENCES users(userid), sendtime varchar (20) not null,

sendcontent varchar(200) not null,

remark varchar (200),

)

-- ----------------------------

-- Records of roles

-- ----------------------------

INSERT INTO roles VALUES ('1', '管理员');

INSERT INTO roles VALUES ('2', '普通用户');

-- ----------------------------

-- Records of users

-- ----------------------------

INSERT INTO users VALUES ('1', 'admin', '管理员', '', '', '32', '男', '青岛市市北区辽宁路1000号', '系统管理员', '1',123);

INSERT INTO users VALUES ('2', 'testuser1', '张小三', '', '', '23', '男', '青岛市市南区山东路1200号', '普通用户', '2', 123);

INSERT INTO users VALUES ('3', 'testuser2', '王小五', '', '', '28', '男', '青岛市崂山区苗岭路1300号', '普通用户', '2', 123);

INSERT INTO users VALUES ('4', 'testuser3', '凤飞飞', '', '', '21', '女', '青岛市黄岛区长江路1400号', '普通用户', '2', 123);

-- ----------------------------

-- Records of user_group

-- ----------------------------

INSERT INTO user_group VALUES ('1', '我的好友');

INSERT INTO user_group VALUES ('2', '陌生人');

INSERT INTO user_group VALUES ('3', '黑名单');

INSERT INTO user_group VALUES ('4', '我的网友');

-- ----------------------------

-- Records of friendship

-- ----------------------------

INSERT INTO friendship VALUES ('1', '2', '1');

INSERT INTO friendship VALUES ('1', '3', '1');

INSERT INTO friendship VALUES ('1', '4', '1');

INSERT INTO friendship VALUES ('2', '3', '1');

INSERT INTO friendship VALUES ('2', '4', '2');

INSERT INTO friendship VALUES ('3', '2', '1');

INSERT INTO friendship VALUES ('3', '4', '4');

-- --------------------------------

-- 1、根据用户名与密码查询用户信息(需要角色名称)

-- 2、插入用户信息

-- 3、修改用户表添加一个状态列:status int default 1

-- 4、更新用户状态=0或1

-- 5、查瞧所有聊天记录信息

-- --------------------------------

-- 6、根据发送人、接收人与时间来查询聊天记录