簡單的BBS論壇 數(shù)據(jù)庫設計

最近幫一個練手的項目組設計了一個bbs論壇的數(shù)據(jù)庫。記錄一下，同時也免費分享給大家。

大概包括這么幾個表：

  • admin用戶表
  • 文章表
  • 文章類型表/標簽表'
  • 關(guān)注表
  • 文章收藏表
  • 一級評論表
  • 多級評論表
  • 用戶信息表
大家不要全部復制sql去跑，我建議大家一個一個表復制去創(chuàng)建。注意外鍵關(guān)聯(lián)關(guān)系的去創(chuàng)建。每個字段幾乎都有注釋。

/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Version : 50624
Source Host : localhost:3306
Source Database : yunding_bbs

Target Server Type : MYSQL
Target Server Version : 50624
File Encoding : 65001

Date: 2018-03-23 12:29:24
*/

SET FOREIGN_KEY_CHECKS=0;

-- Table structure for bbs_admin

DROP TABLE IF EXISTS bbs_admin;
CREATE TABLE bbs_admin (
admin_id int(11) NOT NULL,
admin_login_name varchar(50) DEFAULT NULL,
admin_login_pwd varchar(50) DEFAULT NULL,
PRIMARY KEY (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='admin用戶表';

-- Records of bbs_admin

INSERT INTO bbs_admin VALUES ('1', 'jiao', 'jiao');

-- Table structure for bbs_article

DROP TABLE IF EXISTS bbs_article;
CREATE TABLE bbs_article (
art_id int(11) NOT NULL,
art_user_id int(11) DEFAULT NULL,
art_title varchar(255) DEFAULT NULL COMMENT '標題',
art_type_id int(11) DEFAULT NULL COMMENT '類型id',
art_content text COMMENT '正文',
art_comment_id int(11) DEFAULT NULL COMMENT '評論id',
art_cre_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
art_view int(11) DEFAULT NULL COMMENT '瀏覽量',
art_com_num int(11) DEFAULT NULL COMMENT '評論數(shù)',
art_hot_num int(11) DEFAULT NULL COMMENT '當日瀏覽量/熱度',
art_like_num int(11) DEFAULT NULL COMMENT '點贊數(shù)',
PRIMARY KEY (art_id),
KEY type_index (art_type_id),
KEY com_index (art_comment_id),
KEY art_index (art_user_id),
CONSTRAINT art_index FOREIGN KEY (art_user_id) REFERENCES bbs_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT type_index FOREIGN KEY (art_type_id) REFERENCES bbs_article_type (type_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章表';

-- Records of bbs_article

INSERT INTO bbs_article VALUES ('1', '1', '第一篇文章', '1', '這里是內(nèi)容', '1', '2018-03-23 11:26:29', '999', '9', '9', '9');

-- Table structure for bbs_article_type

DROP TABLE IF EXISTS bbs_article_type;
CREATE TABLE bbs_article_type (
type_id int(11) NOT NULL,
type_name varchar(255) DEFAULT NULL COMMENT '標簽/類型',
type_create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
PRIMARY KEY (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章類型表/標簽表';

-- Records of bbs_article_type

INSERT INTO bbs_article_type VALUES ('1', '標簽1', '2018-03-23 11:25:51');

-- Table structure for bbs_attention

DROP TABLE IF EXISTS bbs_attention;
CREATE TABLE bbs_attention (
att_id int(11) NOT NULL,
att_author_id int(11) DEFAULT NULL COMMENT '關(guān)注人id',
att_user_id int(11) DEFAULT NULL,
PRIMARY KEY (att_id),
KEY attention_index (att_user_id) USING BTREE,
KEY atten_author_index (att_author_id),
CONSTRAINT atten_author_index FOREIGN KEY (att_author_id) REFERENCES bbs_user (user_id) ON DELETE SET NULL ON UPDATE NO ACTION,
CONSTRAINT atten_user_index FOREIGN KEY (att_user_id) REFERENCES bbs_user (user_id) ON DELETE SET NULL ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='關(guān)注表';

-- Records of bbs_attention

INSERT INTO bbs_attention VALUES ('1', '1', '1');

-- Table structure for bbs_collect

DROP TABLE IF EXISTS bbs_collect;
CREATE TABLE bbs_collect (
col_id int(11) NOT NULL,
col_art_id int(11) DEFAULT NULL COMMENT '收藏文章id',
col_user_id int(11) DEFAULT NULL COMMENT '收藏用戶的id/誰收藏了文章',
PRIMARY KEY (col_id),
KEY col_index (col_user_id),
KEY col_art_index (col_art_id),
CONSTRAINT col_art_index FOREIGN KEY (col_art_id) REFERENCES bbs_article (art_id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT col_index FOREIGN KEY (col_user_id) REFERENCES bbs_user (user_id) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章收藏表';

-- Records of bbs_collect

INSERT INTO bbs_collect VALUES ('1', '1', '1');

-- Table structure for bbs_comment

DROP TABLE IF EXISTS bbs_comment;
CREATE TABLE bbs_comment (
com_id int(11) NOT NULL,
com_content varchar(255) DEFAULT NULL COMMENT '評論正文',
com_art_id int(11) DEFAULT NULL COMMENT '文章id',
com_user_id int(11) DEFAULT NULL COMMENT '評論用戶的id',
com_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '評論時間',
PRIMARY KEY (com_id),
KEY com_user_index (com_user_id),
KEY com_art_index (com_art_id),
CONSTRAINT com_art_index FOREIGN KEY (com_art_id) REFERENCES bbs_article (art_id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT com_user_index FOREIGN KEY (com_user_id) REFERENCES bbs_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='一級評論表';

-- Records of bbs_comment

INSERT INTO bbs_comment VALUES ('1', '評論正文', '1', '1', '2018-03-23 12:24:06');

-- Table structure for bbs_comment_multi

DROP TABLE IF EXISTS bbs_comment_multi;
CREATE TABLE bbs_comment_multi (
com_multi_id int(11) NOT NULL,
com_id int(11) NOT NULL COMMENT '一級評論id',
com_multi_content varchar(255) DEFAULT NULL,
com_multi_user_id int(11) NOT NULL COMMENT '多級評論用戶id',
com_multi_time datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (com_multi_id),
KEY multi_user_index (com_multi_user_id),
KEY multi_com_index (com_id),
CONSTRAINT multi_com_index FOREIGN KEY (com_id) REFERENCES bbs_comment (com_id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT multi_user_index FOREIGN KEY (com_multi_user_id) REFERENCES bbs_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='多級評論表';

-- Records of bbs_comment_multi

INSERT INTO bbs_comment_multi VALUES ('1', '1', '多級評論', '1', '2018-03-23 12:24:21');

-- Table structure for bbs_user

DROP TABLE IF EXISTS bbs_user;
CREATE TABLE bbs_user (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(50) DEFAULT NULL COMMENT '用戶昵稱',
user_email varchar(50) DEFAULT NULL,
user_sex varchar(2) DEFAULT NULL COMMENT '用戶性別',
user_phone int(11) DEFAULT NULL COMMENT '電話',
user_status int(1) DEFAULT NULL COMMENT '用戶狀態(tài) 0：未激活 1：激活',
user_ex varchar(255) DEFAULT NULL COMMENT '用戶經(jīng)驗',
user_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注冊時間/更改時間',
user_show varchar(255) DEFAULT NULL COMMENT '用戶簽名',
user_blog varchar(255) DEFAULT NULL COMMENT '用戶主頁鏈接',
user_img varchar(255) DEFAULT NULL COMMENT '用戶頭像',
user_fans int(11) DEFAULT NULL COMMENT '用戶粉絲數(shù)',
user_concern int(11) DEFAULT NULL COMMENT '用戶關(guān)注別人的數(shù)量',
PRIMARY KEY (user_id),
CONSTRAINT user_admin_index FOREIGN KEY (user_id) REFERENCES bbs_admin (admin_id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用戶信息表';

-- Records of bbs_user

INSERT INTO bbs_user VALUES ('1', 'FantJ', 'xxx', '男', '123123123', '1', '1', '2018-03-23 11:20:46', '這是我的個性簽名', 'www.baidu.com', 'https://4f95-8639-e69e8c636570?imageMogr2/auto-orient/strip|imageView2/1/w/240/h/240', '9999', '9999');

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容