数据库设计案例_系统数据库设计

MySQL实现—新闻发布系统数据库设计 　　系统概述 　　本章介绍的是一个小型新闻发布系统，管理员可以通过该系统发布新闻信息、管理新闻信息。 一个典型的新闻发布系统网站至少应该包含新闻信息管理、新闻信息显示和新闻信息查询 3 种功 能。 　　新闻发布系统所要实现的功能具体包括新闻信息添加、新闻信息修改、新闻信息删除、显示 全部新闻信息、按类别显示新闻信息、按关键字查询新闻信息、按关键字进行站内查询。 　　本站为一个简单的新闻信息发布系统，该系统具有以下特点。  实用：系统实现了一个完整的信息查询过程。简单易用：为使用户尽快掌握和使用整个系统，系统结构简单但功能齐全，简洁的页面设 计使操作起来非常简便。代码规范：作为一个实例，文中的代码规范简洁、清晰易懂。 　　本系统主要用于发布新闻信息、管理用户、管理权限、管理评论等功能。这些信息的录入、 查询、修改和删除等操作都是该系统重点解决的问题。 　　本系统的主要功能包括以下几点： 　　（1）具有用户注册及个人信息管理功能。 　　（2）管理员可以发布新闻、删除新闻。 　　（3）用户注册后可以对新闻进行评论、发表留言。 　　（4）管理员可以管理留言和对用户进行管理。 　　系统功能 　　新闻发布系统分为 5 个管理部分，即用户管理、管理员管理、权限管理、新闻管理和评论管 理。本系统的功能模块如下图 所示 　　
　　中模块的详细介绍如下： 　　（1）用户管理模块：实现新增用户、查看和修改用户信息功能。 　　（2）管理员管理模块：实现新增管理员，查看、修改和删除管理员信息功能。 　　（3）权限管理模块：实现对管理员、对管理的模块和管理的评论赋权功能。 　　（4）新闻管理模块：实现有相关权限的管理员对新闻的增加、查看、修改和删除功能。 　　（5）评论管理模块：实现有相关权限的管理员对评论的审核和删除功能。 　　通过上面的介绍，读者对这个新闻发布系统的主要功能有一定的了解，下面将向读者介绍 本系统所需要的数据库和表。 　　数据库设计和实现 　　数据库设计是开发管理系统最重要的一个步骤。如果数据库设计得不够合理，将会为后续的 开发工作带来很大的麻烦。本节为读者介绍新闻发布系统的数据库开发过程。 　　数据库设计时要确定设计哪些表、表中包含哪些字段、字段的数据类型和长度。通过本节的 学习，读者可以对 MySQL 数据库的知识有一个全面的了解。  设计表 　　本系统所有的表都放在 webnews 数据库下。创建和选择 webnews 数据库的 SQL 代码如下 　　在这个数据库下总共存放 9 张表，分别是 user、admin、roles、news、category、comment、 admin_Roles、news_Comment 和 users_Comment。 　　（1）user 表 　　user 表用于存储用户 ID、用户名、密码和用户 Email 地址，所以 user 表设计了 5 个字段。user 表每个字段的信息如下表 所示。 　　
　　根据表的内容创建 user 表，SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 user 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 user 表的详细信息。 　　（2）admin 表 　　管理员信息（admin）表主要用来存放用户账号信息，如下所示 　　
　　根据表的内容创建 admin 表。创建 admin 表的 SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 admin 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 admin 表的详细信息。 　　（3）roles 表 　　权限信息（roles）表主要用来存放权限信息，如下所示。 　　
　　根据表的内容创建 roles 表，SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 roles 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 roles 表的详细信息。 　　（4）news 表 　　新闻信息（news）表主要用来存放新闻信息，如下 、所示 　　
　　根据表的内容创建 news 表，SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 news 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 news 表的详细信息。 　　（5）category 表 　　栏目信息（categroy）表主要用来存放新闻栏目信息，如下所示。 　　
　　根据表的内容创建 categroy 表。创建 categroy 表的 SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 categroy 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 categroy 表的详细信息 　　（6）comment 表 　　评论信息（comment）表主要用来存放新闻评论信息，如下所示 　　
　　根据表的内容创建 comment 表，SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 comment 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 comment 表的详细信息。 　　（7） admin_Roles 表 　　管理员_权限（admin_Roles）表主要用来存放管理员和权限的关系，如下所示。 　　
　　根据表的内容创建 admin_Roles 表，SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 admin_Roles 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 admin_Roles 表的详细信息。 　　（8）news_Comment 表 　　新闻_评论（news_Comment）表主要用来存放新闻和评论的关系，如下 所示。 　　
　　根据表的内容创建 news_Comment 表，SQL 语句如下： 　　创建完成后，可以使用 DESC 语句查看 news_Comment 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 news_Comment 表的详细信息。 　　（9）users_Comment 表 　　用户_评论（users_Comment）表主要用来存放用户和评论的关系，如表所示。 　　
　　根据表的内容创建 users_Comment 表，SQL 语句如下 　　创建完成后，可以使用 DESC 语句查看 users_Comment 表的基本结构，也可以通过 SHOW CREATE TABLE 语句查看 users_Comment 表的详细信息。 　　设计索引 　　索引是创建在表上的，是对数据库中一列或者多列的值进行排序的一种结构。索引可以提高 查询的速度。新闻发布系统需要查询新闻的信息，这就需要在某些特定字段上建立索引，以便提高 查询速度。    在 news 表上建立索引 　　新闻发布系统中需要按照 newsTitle 字段、newsDate 字段和 newsRate 字段查询新闻信息。 　　本小节将使用 CREATE INDEX 语句和 ALTER TABLE 语句创建索引。 下面使用 CREATE INDEX 语句在 newsTitle 字段上创建名为 index_new_title 的索引。SQL 语 句如下： 　　然后，使用 CREATE INDEX 语句在 newsDate 字段上创建名为 index_new_date 的索引。SQL 语句如下： 　　最后，使用 ALTER TABLE 语句在 newsRate 字段上创建名为 index_new_rate 的索引。SQL 语句如下： 　　在 categroy 表上建立索引 　　在新闻发布系统中，需要通过栏目名称查询该栏目下的新闻，因此需要在这个字段上创建索 引。创建索引的语句如下： 　　代码执行完成后，读者可以使用 SHOW CREATE TABLE 语句查看 categroy 表的详细信息。 在 comment 表上建立索引 　　新闻发布系统需要通过 commentTitle 字段和 commentDate 字段查询评论内容，因此可以在这 两个字段上创建索引。创建索引的语句如下： 　　代码执行完成后，读者可以通过 SHOW CREATE TABLE 语句查看 comment 表的结构。 　　设计视图 　　视图是由数据库中一个表或者多个表导出的虚拟表，作用是方便用户对数据的操作。在这个 新闻发布系统中，也设计了一个视图改善查询操作。 　　在新闻发布系统中，如果直接查询 news_Comment 表，显示信息时会显示新闻编号和评论编 号。这种显示不直观，为了以后查询方便，可以建立一个视图 news_view。这个视图显示评论编号、 新闻编号、新闻级别、新闻标题、新闻内容和新闻发布时间。创建视图 news_view 的 SQL 代码如 下： 　　news_Comment 表的别名为 c，news 表的别名为 n，这个视图从这两个表中取出相应的字段。 视图创建完成后，可以使用 SHOW CREATE VIEW 语句查看 news_view 视图的详细信息。 　　设计触发器 　　触发器由 INSERT、UPDATE 和 DELETE 等事件来触发某种特定的操作。满足触发器的触发 条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。 为了使新闻发布系统的数据更新更加快速和合理，可以在数据库中设计几个触发器。  设计 UPDATE 触发器 　　在设计表时，news 表和 news_Comment 表的 newsID 字段的值是一样的。如果 news 表中的 newsID 字段的值更新了，那么 news_Comment 表中的 newsID 字段的值也必须同时更新。这可以通 过一个 UPDATE 触发器来实现。创建 UPDATE 触发器 update_newsID 的 SQL 代码如下： 　　其中，NEW.newsID 表示 news 表中更新的记录的 newsID 值。 设计 DELETE 触发器 　　如果从 user 表中删除一个用户的信息，那么这个用户在 users_Comment 表中的信息也必须同 时删除。这也可以通过触发器来实现。在 user 表上创建 delete_user 触发器，只要执行 DELETE 操 作，就删除 users_Comment 表中相应的记录。创建 delete_user 触发器的 SQL 语句如下： 　　其中，OLD.userID 表示新删除的记录的 userID 值