数据库建模步骤

数据库建模

工具：powerdesigner

1. 确定产品需求
2. 建立对应概念模型(CDM)
3. 建立逻辑模型(LDM)
4. 建立物理模型(PDM)
5. 优化和确定最终物理模型，并导出sql脚本

示例

同理blog（文章）实体也可以如图建立

注意，在建立概念模型是只关心实体本身的属性，暂且不要考虑与其他任何实体的联系

概念模型完成后，转换为逻辑模型，工具->Generate Logical Data Model

生成后如图

我们看到，多对多的关系PD自动生成了一个新实体，那么，针对网站的功能，我们还需要知道的一些信息比如，关注时间，评论的时间/内容等等，所以只需要在相应的关系实体上添加属性即可
如：评论时间，评论内容，关注时间等等

最后根据实际的业务需要调整一些属性，比如用户量大小等…

/*==============================================================*/ /* DBMS name: MySQL 5.0 */ /* Created on: 2019/8/19 11:39:57 */ /*==============================================================*/ drop table if exists blog; drop table if exists diss; drop table if exists user; drop table if exists 关注; drop table if exists 点赞; drop table if exists 评论; /*==============================================================*/ /* Table: blog */ /*==============================================================*/ create table blog ( blog_id bigint not null auto_increment, author_uid bigint, title varchar(100), content longtext, post_time datetime, total_zan int, total_diss int, total_read int, total_comment int, primary key (blog_id) ); /*==============================================================*/ /* Table: diss */ /*==============================================================*/ create table diss ( blog_id bigint not null, uid bigint not null, diss_time datetime, primary key (blog_id, uid) ); /*==============================================================*/ /* Table: user */ /*==============================================================*/ create table user ( uid bigint not null auto_increment, email varchar(255), phone_num varchar(20), login_name varchar(20), nick_name varchar(50), password varchar(255), reg_time datetime, head_photo varchar(500), fans_num int, follow_num int, primary key (uid) ); /*==============================================================*/ /* Table: 关注 */ /*==============================================================*/ create table 关注 ( user1_id bigint not null, user2_id bigint not null, follow_time datetime, primary key (user1_id, user2_id) ); /*==============================================================*/ /* Table: 点赞 */ /*==============================================================*/ create table 点赞 ( uid bigint not null, blog_id bigint not null, dz_time datetime, primary key (blog_id, uid) ); /*==============================================================*/ /* Table: 评论 */ /*==============================================================*/ create table 评论 ( cmt_id bigint not null auto_increment, uid bigint, blog_id bigint, cmt_time datetime, cmt_content text, primary key (cmt_id) ); /* alter table blog add constraint FK_发表 foreign key (author_uid) references user (uid) on delete restrict on update restrict; alter table diss add constraint FK_diss foreign key (blog_id) references blog (blog_id) on delete restrict on update restrict; alter table diss add constraint FK_diss2 foreign key (uid) references user (uid) on delete restrict on update restrict; alter table 关注 add constraint FK_关注 foreign key (user1_id) references user (uid) on delete restrict on update restrict; alter table 关注 add constraint FK_关注2 foreign key (user2_id) references user (uid) on delete restrict on update restrict; alter table 点赞 add constraint FK_点赞 foreign key (blog_id) references blog (blog_id) on delete restrict on update restrict; alter table 点赞 add constraint FK_点赞2 foreign key (uid) references user (uid) on delete restrict on update restrict; alter table 评论 add constraint FK_评论 foreign key (blog_id) references blog (blog_id) on delete restrict on update restrict; alter table 评论 add constraint FK_评论2 foreign key (uid) references user (uid) on delete restrict on update restrict; */