数据库设计建模（重点）

一、确定业务中的实体、属性和方法

1、通过了解熟悉业务流程，将业务流程中的事务抽象成类、属性和方法，通过编程逻辑把问题解决。

确定业务中的类：收集业务资料，罗列业务中的所有名词，通过“找名词”的方法，确定业务中的类，注意区分开类和属性

2、区分类和属性（重点）

区分类和属性的技巧：确定该名词是否有方法（即是否拥有动作）。

一般描述为xx的xx是属性。

对于类和方法的封装，要根据业务逻辑来封装，虽然可以有很多方法和属性，但是与业务逻辑无关，也无需封装。

例如：小明的身高2米，他喜欢打篮球。

分析：类有：小明和篮球      身高是属性

（一）数据库设计

1、数据库设计三范式

• 第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
• 第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；（同一表中消除部分依赖）
• 第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。（同一表中消除传递依赖）

要正确理解三范式，尤其是第三条字段没有冗余，没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。提倡：提倡高级冗余（派生性冗 余），反对低级冗余（重复性冗余）。注意：主键与外键在多表中的重复出现，不属于数据冗余，这个概念必须清楚，事实上有许多人还不清楚。非键字段的重复出现，才是数据冗余！而且是一种低级冗余，即重复性的冗余。高级冗余不是字段的重复出现，而是字段的派生出现。

创建表语句：CREATE TABLE table_name (column_name column_type);

（2）视图

视图是一种虚表，它依赖数据源的实表而存在。视图是供程序员使用数据库的一个窗口，是基表数据综合的一种形 式, 是数据处理的一种方法，是用户数据保密的一种手段。为了进行复杂处理、提高运算速度和节省存储空间, 视图的定义深度一般不得超过三层。 若三层视图仍不够用, 则应在视图上定义临时表, 在临时表上再定义视图。视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。

CREATE VIEW view_name AS  SELECT column_name(s) FROM table_name WHERE condition

（3）中间表

中间表是解决多对多关系的连接表，通过中间表将多对多关系转化为两个一对多关系实现表的设计。

例如：学生和课程是多对多的关系，通过选课来连接学生和课程，即选课表连接学生表和课程表。即对于多对多关系，需要转换成1对多关系，那么就需要一张中间表来转换，这张中间表里面需要存放学生表里面的主键和课程表里面的主键，此时学生与中间表示1对多关系，课程与中间表是1对多关系，学生与课程是多对多关系。

注意：学生表的某个学生通过中间表选课表和课程表的多条课程记录关联起来，反之亦然。一个学生对应中间表中多条记录，中间表中的一条记录对应学生表中的一个学生；课程和中间表同理，课程和中间表是一对多的关系。

中间表是存放统计数据的表，它是为数据仓库、输出报表或查询结果而设计的，有时它没有主键与外键（数据仓库除外）。

中间表参考文档：https://blog.csdn.net/vainfanfan/article/details/80568784

（4）临时表

临时表是程序员个人设计的，存放临时记录，为个人所用。基表和中间表由DBA维护，临时表由程序员自己用程序自动维护。oracle中临时表分为会话级临时表和事务级临时表。

注意：这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。

临时表知识学习：https://blog.csdn.net/sinat_26342009/article/details/45288695

（5）完整性约束

域的完整性：用Check来实现约束，在数据库设计工具中，对字段的取值范围进行定义时，有一个Check按钮，通过它定义字段的值城。

参照完整性：用PK、FK、表级触发器来实现。

用户定义完整性：它是一些业务规则，用存储过程和触发器来实现。

（6）提高数据库的运行效率

在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：

• 在数据库物理设计时，降低范式，增加冗余，少用触发器, 多用存储过程。
• 当计算非常复杂、而且记录条数非常巨大时（例如一千万条），复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。
• 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。
• 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
• 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。

（7）表之间的三种关系

一对一关系：可以设计成两张张表

一对多关系：可以设计两张表，在多的一方设计外键。（一对一是一对多的一个特例）

多对多关系：可设计成三张表，将关系抽象成一张表。

关联映射：一对多/多对一
存在最普遍的映射关系，简单来讲就如球员与球队的关系；
一对多：从球队角度来说一个球队拥有多个球员 即为一对多
多对一：从球员角度来说多个球员属于一个球队 即为多对一数据表间一对多关系如下图：

关联映射：一对一
一对一关系就如球队与球队所在地址之间的关系，一支球队仅有一个地址，而一个地址区也仅有一支球队。
数据表间一对一关系的表现有两种，一种是外键关联，一种是主键关联。

一对一外键关联，图示如下：

一对一主键关联：要求两个表的主键必须完全一致，通过两个表的主键建立关联关系。图示如下：

关联映射：多对多
多对多关系也很常见，例如学生与选修课之间的关系，一个学生可以选择多门选修课，而每个选修课又可以被多名学生选择。
数据库中的多对多关联关系一般需采用中间表的方式处理，将多对多转化为两个一对多。
数据表间多对多关系如下图：

（8）添加必要的（冗余）字段

像“创建时间”、“修改时间”、“备注”、“操作用户IP”和一些用于其他需求（如统计）的字段等，在每张表中必须都要有，不是说只有系统中用到的数据才会存到数据库中，一些冗余字段是为了便于日后维护、分析、拓展而添加的，这点是非常重要的，比如黑客攻击，篡改了数据，我们便就可以根据修改时间和操作用户IP来查找定位。

设计表时就要事先预留出可变通的字段，正所谓“有备无患”。

常用的存取方法有三类：1、索引方法，目前主要是B+树索引方法；2、聚簇方法；3、HASH方法。

标定联系（Identify Relationship）、非标定联系（Non-Identify RelationShip）和递归联系（Recursive Relationship）

联系还有另外三个可以设置的属 性：mandatory（强制性联系）, dependent（依赖性联系/标定关联） 和dominant（统制联系）。

1、标定联系：实体E和实体F都必须有标识符，一个实体类型的标识符进入另一个实体类型并与该实体类型中的标识符共同组成其标识符。

dependent：依赖性关联（dependent relationship）：就是标定关联，就是主-从表关 系，从表要依赖于主表。比如在我们系统里要记录教师休假的情况，有一个实体型Holiday，其属性包括休假的开始时间和天数，每次有教师休假的时候，都 要在这个表留下记录。从我们的场景描述中可以看到，实体型假期必须依附于实体型教师，即对于每一个假期实例，必须指向某一个教师实例。（依赖关系：表示实体是否必须依赖于另一个实体，而不能单独存在）（是否能单独存在）。
  对于依赖型联系，必须注意它不可能是一个多对多联系，在这个联系中，必须有一个作为主体的实体型。一个dependent联系的从实体可以没有自己的identifier.

2、非标定联系：实体E和实体F都必须有自己的标识符（即每个实体都必须有自己的主键）。

3、递归联系：实体集内部实例之间的一种联系。在“职工”实体集中存在很多的职工，这些职工之间必须存在一种领导与被领导的关系。又如“学生”实体信中的实体包含“班长”子实体集与“普通学生”子 实体集，这两个子实体集之间的联系就是一种递归联系。创建递归联系时，只需要单击“实体间建立联系”工具从实体的一部分拖至该实体的别一个部分即可。

4.dominant：作用于一对一联系，并指明这种联系中的主从表关系。老师和班级之间的联系，因为每个班级都有一个老师做班主任，每个老师也最多只能做一个班级的班主任，所以是一个一对一关系。同时，我们可以将老 师作为主表，用老师的工号来唯一确定一个班主任联系，即老师的工号作为班级的外键。

二、树型关系的数据表设计

设计	表数量	查询子	查询树	插入	删除	引用完整性
邻接表	1	简单	简单	简单	简单	是
枚举路径	1	简单	简单	简单	简单	否
嵌套集	1	困难	简单	困难	困难	否
闭包表	2	简单	简单	简单	简单	是

四种模型实例：http://www.cnblogs.com/kissdodog/p/3297894.html

　　1、邻接表是最方便的设计，并且很多软件开发者都了解它。并且在递归查询的帮助下，使得邻接表的查询更加高效。

　　2、枚举路径能够很直观地展示出祖先到后代之间的路径，但由于不能确保引用完整性，使得这个设计比较脆弱。枚举路径也使得数据的存储变得冗余。

路径枚举实例：https://www.cnblogs.com/mfrank/p/8010973.html

　　3、嵌套集是一个聪明的解决方案，但不能确保引用完整性，并且只能使用于查询性能要求较高，而其他要求一般的场合使用它。

　　4、闭包表是最通用的设计，并且最灵活，易扩展，并且一个节点能属于多棵树，能减少冗余的计算时间。但它要求一张额外的表来存储关系，是一个空间换取时间的方案。

注意：oracle实现递归查询的语法：https://www.cnblogs.com/benbenduo/p/4588612.html

mysql和oracle不同，mysql没有函数支持递归查询，需要自定义函数进行实现。

mysql实现递归参考文档：https://www.cnblogs.com/duanrantao/p/9359137.html

树形结构设计思路：https://www.cnblogs.com/cathsfz/archive/2006/12/12/589464.html

（一）通常在设计表示树结构的数据表时，我们会增加两个字段：

• Depth - 表示当前节点的深度的整数
• Path - 表示从根节点到当前节点的路径的字符串，采用节点名称不可能出现的字符作为分隔符

在对树进行操作时，我们还是如平常一样对表执行CRUD操作，要维护这两个字段并不需要费多少力气，然而在查询时却会为我们带来极大的便利。例如要查询第3层的节点，则只需要使用"WHERE Depth = 3"；又例如要查询A1节点下B3节点下C2节点下的所有子节点，则可以使用"WHERE Path LIKE 'A1/B3/C2%'"。通过增加冗余信息来提高检索速度。

（二）数据库表连接查询

数据库表连接查询参考文档：https://blog.csdn.net/plg17/article/details/78758593

 

类别表设计具体事例一：

类别表设计及查询参考：https://www.cnblogs.com/zhanganju/p/7016289.html

类别表设计及查询参考：https://blog.csdn.net/YYZZHC999/article/details/79953674

树形关系的数据表：例如常见的类别表，即一个大类，下面有若干子类，某些子类又拥有子类的情况。

当类别不确定，用户希望可以在任意类别下添加新的子类，或者删除某个类别和其下的所有子类，而且预计以后其数量会逐步增长，此时我们就会考虑用一个数据表来保存这些数据。

类别表_1(Type_table_1)
名称　　　　　类型　　　　约束条件　　　说明
type_id     int      无重复　　  类别标识，主键
type_name char(50)   不允许为空   类型名称，不允许重复
type_father   int         不允许为空  该类别的父类别标识，如果是顶节点的话设定为某个唯一值

　　这样的设计短小精悍，完全满足3NF，而且可以满足用户的所有要求。是不是这样就行呢？答案是NO！Why？

　　我们来估计一下用户希望如何罗列出这个表的数据的。对用户而言，他当然期望按他所设定的层次关系一次罗列出所有的类别，例如这样：
总类别
　　类别1
　　　　类别1.1
　　　　　　类别1.1.1
　　　　类别1.2
　　类别2
　　　　类别2.1
　　类别3
　　　　类别3.1
　　　　类别3.2
……

　　看看为了实现这样的列表显示(树的先序遍历)，要对上面的表进行多少次检索？注意，尽管类别1.1.1可能是在类别3.2之后添加的记录，答案仍然是N次。这样的效率对于少量的数据没什么影响，但是日后类型扩充到数十条甚至上百条记录后，单单列一次类型就要检索数十次该表，整个程序的运行效率就不敢恭维了。或许第二类程序员会说，那我再建一个临时数组或临时表，专门保存类型表的先序遍历结果，这样只在第一次运行时检索数十次，再次罗列所有的类型关系时就直接读那个临时数组或临时表就行了。其实，用不着再去分配一块新的内存来保存这些数据，只要对数据表进行一定的扩充，再对添加类型的数量进行一下约束就行了，要完成上面的列表只需一次检索就行了。下面是扩充后的数据表结构：

类别表_2(Type_table_2)
名称　　　　　类型　　　　约束条件　　　                   说明
type_id     int      无重复　　                  类别标识，主键
type_name char(50)   不允许为空                  类型名称，不允许重复
type_father   int         不允许为空                  该类别的父类别标识，如果是顶节点的话设定为某个唯一值
type_layer    char(6)     限定3层,初始值为000000      类别的先序遍历，主要为减少检索数据库的次数

　　按照这样的表结构，我们来看看上面例子记录在表中的数据是怎样的：

type_id      type_name          type_father          type_layer
1             总类别               0                 000000
2             类别1                1                 010000
3             类别1.1              2                 010100
4             类别1.2              2                 010200
5             类别2                1                 020000
6             类别2.1              5                 020100
7             类别3                1                 030000
8             类别3.1              7                 030100
9             类别3.2              7                 030200
10            类别1.1.1            3                 010101
……

　　现在按type_layer的大小来检索一下：SELECT * FROM Type_table_2 ORDER BY type_layer

列出记录集如下：

type_id      type_name          type_father          type_layer
1             总类别               0                 000000
2             类别1                1                 010000
3             类别1.1              2                 010100
10            类别1.1.1            3                 010101
4             类别1.2              2                 010200
5             类别2                1                 020000
6             类别2.1              5                 020100
7             类别3                1                 030000
8             类别3.1              7                 030100
9             类别3.2              7                 030200
……

　　现在列出的记录顺序正好是先序遍历的结果。在控制显示类别的层次时，只要对type_layer字段中的数值进行判断，每2位一组，如大于0则向右移2个空格。当然，我这个例子中设定的限制条件是最多3层，每层最多可设99个子类别，只要按用户的需求情况修改一下type_layer的长度和位数，即可更改限制层数和子类别数。其实，上面的设计不单单只在类别表中用到，网上某些可按树型列表显示的论坛程序大多采用类似的设计。

　　或许有人认为，Type_table_2中的type_father字段是冗余数据，可以除去。如果这样，在插入、删除某个类别的时候，就得对type_layer的内容进行比较繁琐的判定，所以我并没有消去type_father字段，这也正符合数据库设计中适当保留冗余数据的来降低程序复杂度的原则，后面我会举一个故意增加数据冗余的案例。

三、商品信息表的设计（数据库表设计的预见性）

　　假设你是一家百货公司电脑部的开发人员，某天老板要求你为公司开发一套网上电子商务平台，该百货公司有数千种商品出售，不过目前仅打算先在网上销售数十种方便运输的商品，当然，以后可能会陆续在该电子商务平台上增加新的商品出售。现在开始进行该平台数据库的商品信息表的设计。每种出售的商品都会有相同的属性，如商品编号，商品名称，商品所属类别，相关信息，供货厂商，内含件数，库存，进货价，销售价，优惠价。你很快就设计出4个表：商品类型表(Wares_type)，供货厂商表(Wares_provider)，商品信息表(Wares_info)：

商品类型表(Wares_type)
名称　　　　　类型　　　　约束条件　　　                   说明
type_id     int      无重复　　                  类别标识，主键
type_name char(50)   不允许为空                  类型名称，不允许重复
type_father   int         不允许为空                  该类别的父类别标识，如果是顶节点的话设定为某个唯一值
type_layer    char(6)     限定3层,初始值为000000      类别的先序遍历，主要为减少检索数据库的次数

供货厂商表(Wares_provider)
名称　　　　　类型　　　　约束条件　　　                   说明
provider_id   int      无重复　　                  供货商标识，主键
provider_name char(100)   不允许为空                  供货商名称

商品信息表(Wares_info)
名称　　　　  类型　　　　约束条件　　　                   说明
wares_id       int     无重复　　                    商品标识，主键
wares_name     char(100)  不允许为空                    商品名称
wares_type int       不允许为空　　　　　　　　　　商品类型标识，和Wares_type.type_id关联
wares_info     char(200)  允许为空                      相关信息
provider       int        不允许为空                    供货厂商标识，和Wares_provider.provider_id关联
setnum         int        初始值为1                     内含件数，默认为1
stock          int        初始值为0                     库存，默认为0
buy_price      money      不允许为空                    进货价
sell_price     money      不允许为空                    销售价
discount       money      不允许为空                    优惠价

　　你拿着这3个表给老板检查，老板希望能够再添加一个商品图片的字段，不过只有一部分商品有图片。OK，你在商品信息表(Wares_info)中增加了一个haspic的BOOL型字段（布尔类型，即是否有图片），然后再建了一个新表——商品图片表(Wares_pic)：

商品图片表(Wares_pic)
名称　　　　  类型　　　　约束条件　　　                   说明
pic_id        int      无重复　　                    商品图片标识，主键
wares_id      int         不允许为空                    所属商品标识，和Wares_info.wares_id关联
pic_address char(200)  不允许为空　　　　　　　　　　图片存放路径

　　程序开发完成后，完全满足老板目前的要求，于是正式启用。一段时间后，老板打算在这套平台上推出新的商品销售，其中，某类商品全部都需添加“长度”的属性。第一轮折腾来了……当然，你按照添加商品图片表的老方法，在商品信息表(Wares_info)中增加了一个haslength的BOOL型字段（布尔类型，即是否有长度），又建了一个新表——商品长度表(Wares_length)：

商品长度表(Wares_length)
名称　　　　  类型　　　　约束条件　　　                   说明
length_id     int      无重复　　                    商品图片标识，主键
wares_id      int         不允许为空                    所属商品标识，和Wares_info.wares_id关联
length      char(20)   不允许为空　　　　　　　　　　商品长度说明

　　刚刚改完没多久，老板又打算上一批新的商品，这次某类商品全部需要添加“宽度”的属性。你咬了咬牙，又照方抓药，添加了商品宽度表(Wares_width)。又过了一段时间，老板新上的商品中有一些需要添加“高度”的属性，你是不是开始觉得你所设计的数据库按照这种方式增长下去，很快就能变成一个迷宫呢？那么，有没有什么办法遏制这种不可预见性，但却类似重复的数据库膨胀呢？我在阅读《敏捷软件开发：原则、模式与实践》中发现作者举过类似的例子：7.3“Copy”程序。其中，我非常赞同敏捷软件开发这个观点：在最初几乎不进行预先设计，但是一旦需求发生变化，此时作为一名追求卓越的程序员，应该从头审查整个架构设计，在此次修改中设计出能够满足日后类似修改的系统架构。下面是我在需要添加“长度”的属性时所提供的修改方案：

（一）数据库设计重点：修改方案

在做数据库设计时，要有高度的预见性和敏锐的洞察力：当需求改变，要从头审查整个架构设计，是否需要优化完善，修改之后能否满足日后类似的修改。（可以从数据的查询和增加删除考虑表设计是否合理，能否简化SQL语句编写）

　　解决方法：去掉商品信息表(Wares_info)中的haspic字段，添加商品额外属性表(Wares_ex_property)和商品额外信息表(Wares_ex_info)2个表来完成添加新属性的功能。

商品额外属性表(Wares_ex_property)
名称　　　　  类型　　　　约束条件　　　                   说明
ex_pid        int      无重复　　                    商品额外属性标识，主键
p_name        char(20)    不允许为空                    额外属性名称

商品额外信息表(Wares_ex_info)
名称　　　　    类型　　　　约束条件　　　                   说明
ex_iid          int      无重复　　                    商品额外信息标识，主键
wares_id        int         不允许为空                    所属商品标识，和Wares_info.wares_id（商品信息表）关联
property_id   int        不允许为空　　　商品额外属性标识，和Wares_ex_property.ex_pid（商品额外属性表）关联
property_value  char(200)   不允许为空                    商品额外属性值

　　在商品额外属性表(Wares_ex_property)中添加2条记录：
ex_pid            p_name
1                商品图片
2                商品长度

　　优化结果：再在整个电子商务平台的后台管理功能中追加一项商品额外属性管理的功能，以后添加新的商品时出现新的属性，只需利用该功能往商品额外属性表(Wares_ex_property)中添加一条记录即可。

四、多用户及其权限管理的设计

开发数据库管理类的软件，不可能不考虑多用户和用户权限设置的问题。尽管目前市面上的大、中型的后台数据库系统软件都提供了多用户，以及细至某个数据库内某张表的权限设置的功能，我个人建议：一套成熟的数据库管理软件，还是应该自行设计用户管理这块功能，原因有二：
1.那些大、中型后台数据库系统软件所提供的多用户及其权限设置都是针对数据库的共有属性，并不一定能完全满足某些特例的需求；
2.不要过多的依赖后台数据库系统软件的某些特殊功能，多种大、中型后台数据库系统软件之间并不完全兼容。否则一旦日后需要转换数据库平台或后台数据库系统软件版本升级，之前的架构设计很可能无法重用。

　　下面看看如何自行设计一套比较灵活的多用户管理模块，即该数据库管理软件的系统管理员可以自行添加新用户，修改已有用户的权限，删除已有用户。首先，分析用户需求，列出该数据库管理软件所有需要实现的功能；然后，根据一定的联系对这些功能进行分类，即把某类用户需使用的功能归为一类；最后开始建表：

功能表(Function_table)
名称　　　　　类型　　　　约束条件　　　说明
f_id          int      无重复　　   功能标识，主键
f_name        char(20)    不允许为空  功能名称，不允许重复
f_desc        char(50)    允许为空    功能描述

用户组表(User_group)
名称　　　　　类型　　　　约束条件　　　说明
group_id      int         无重复       用户组标识，主键
group_name    char(20)    不允许为空   用户组名称
group_power   char(100)   不允许为空   用户组权限表，内容为功能表f_id的集合

用户表(User_table)
名称　　　　　类型　　　　约束条件　　　说明
user_id       int         无重复       用户标识，主键
user_name     char(20)    无重复       用户名
user_pwd      char(20)    不允许为空   用户密码
user_type     int         不允许为空   所属用户组标识，和User_group.group_id关联

　　采用这种用户组的架构设计，当需要添加新用户时，只需指定新用户所属的用户组；当以后系统需要添加新功能或对旧有功能权限进行修改时，只用操作功能表和用户组表的记录，原有用户的功能即可相应随之变化。当然，这种架构设计把数据库管理软件的功能判定移到了前台，使得前台开发相对复杂一些。但是，当用户数较大(10人以上)，或日后软件升级的概率较大时，这个代价是值得的。

 

 五、简洁的批量m:n设计（重点）

　碰到m:n的关系，一般都是建立3个表，m一个，n一个，m:n一个。但是，m:n有时会遇到批量处理的情况，例如到图书馆借书，一般都是允许用户同时借阅n本书，如果要求按批查询借阅记录，即列出某个用户某次借阅的所有书籍，该如何设计呢？让我们建好必须的3个表先：

书籍表(Book_table)
名称　　　　　类型　　　　约束条件　　　说明
book_id       int         无重复       书籍标识，主键
book_no       char(20)    无重复       书籍编号
book_name     char(100)   不允许为空   书籍名称
……

借阅用户表(Renter_table)
名称　　　　　类型　　　　约束条件　　　说明
renter_id     int         无重复       用户标识，主键
renter_name   char(20)    不允许为空   用户姓名
……

借阅记录表(Rent_log)
名称　　　　　类型　　　　约束条件　　　说明
rent_id       int         无重复       借阅记录标识，主键
r_id          int         不允许为空   用户标识，和Renter_table.renter_id关联
b_id          int         不允许为空   书籍标识，和Book_table.book_id关联
rent_date     datetime    不允许为空   借阅时间
……

　　为了实现按批查询借阅记录，我们可以再建一个表来保存批量借阅的信息，例如：

批量借阅表(Batch_rent)
名称　　　　　类型　　　　约束条件　　　说明
batch_id      int         无重复       批量借阅标识，主键
batch_no      int         不允许为空   批量借阅编号，同一批借阅的batch_no相同
rent_id       int         不允许为空   借阅记录标识，和Rent_log.rent_id关联
batch_date    datetime    不允许为空   批量借阅时间

　　这样的设计好吗？我们来看看为了列出某个用户某次借阅的所有书籍，需要如何查询？首先检索批量借阅表(Batch_rent)，把符合条件的的所有记录的rent_id字段的数据保存起来，再用这些数据作为查询条件带入到借阅记录表(Rent_log)中去查询。那么，有没有什么办法改进呢？下面给出一种简洁的批量设计方案，不需添加新表，只需修改一下借阅记录表(Rent_log)即可。修改后的记录表(Rent_log)如下：

借阅记录表(Rent_log)
名称　　　　　类型　　　　约束条件　　　说明
rent_id       int         无重复       借阅记录标识，主键
r_id          int         不允许为空   用户标识，和Renter_table.renter_id关联
b_id          int         不允许为空   书籍标识，和Book_table.book_id关联
batch_no      int         不允许为空   批量借阅编号，同一批借阅的batch_no相同
rent_date     datetime    不允许为空   借阅时间
……

　　其中，同一次借阅的batch_no和该批第一条入库的rent_id相同。举例：假设当前最大rent_id是64，接着某用户一次借阅了3本书，则批量插入的3条借阅记录的batch_no都是65。之后另外一个用户租了一套碟，再插入出租记录的rent_id是68。采用这种设计，查询批量借阅的信息时，只需使用一条标准T_SQL的嵌套查询即可。当然，这种设计不符合3NF，但是和上面标准的3NF设计比起来，哪一种更好呢？

六、冗余数据的取舍

上篇的“树型关系的数据表”中保留了一个冗余字段，这里的例子更进一步——添加了一个冗余表。先看看例子：我原先所在的公司为了解决员工的工作餐，和附近的一家小餐馆联系，每天吃饭记账，费用按人数平摊，月底由公司现金结算，每个人每个月的工作餐费从工资中扣除。当然，每天吃饭的人员和人数都不是固定的，而且，由于每顿工作餐的所点的菜色不同，每顿的花费也不相同。例如，星期一中餐5人花费40元，晚餐2人花费20，星期二中餐6人花费36元，晚餐3人花费18元。为了方便计算每个人每个月的工作餐费，我写了一个简陋的就餐记账管理程序，数据库里有3个表：

员工表(Clerk_table)
名称　　　　　类型　　　　约束条件　　　说明
clerk_id      int         无重复       员工标识，主键
clerk_name    char(10)    不允许为空   员工姓名

每餐总表(Eatdata1)
名称　　　　　类型　　　　约束条件　　　说明
totle_id      int         无重复       每餐总表标识，主键
persons       char(100)   不允许为空   就餐员工的员工标识集合
eat_date      datetime    不允许为空   就餐日期
eat_type      char(1)     不允许为空   就餐类型，用来区分中、晚餐
totle_price   money       不允许为空   每餐总花费
persons_num   int         不允许为空   就餐人数

就餐计费细表(Eatdata2)
名称　　　　　类型　　　　约束条件　　　说明
id            int         无重复       就餐计费细表标识，主键
t_id          int         不允许为空   每餐总表标识，和Eatdata1.totle_id关联
c_id          int         不允许为空   员工标识标识，和Clerk_table.clerk_id关联
price         money       不允许为空   每人每餐花费

　　其中，就餐计费细表(Eatdata2)的记录就是把每餐总表(Eatdata1)的一条记录按就餐员工平摊拆开，是个不折不扣的冗余表。当然，也可以把每餐总表(Eatdata1)的部分字段合并到就餐计费细表(Eatdata2)中，这样每餐总表(Eatdata1)就成了冗余表，不过这样所设计出来的就餐计费细表重复数据更多，相比来说还是上面的方案好些。但是，就是就餐计费细表(Eatdata2)这个冗余表，在做每月每人餐费统计的时候，大大简化了编程的复杂度，只用类似这么一条查询语句即可统计出每人每月的寄餐次数和餐费总帐：

SELECT clerk_name AS personname,COUNT(c_id) as eattimes,SUM(price) AS ptprice FROM Eatdata2 JOIN Clerk_tabsle ON (c_id=clerk_id) JOIN eatdata1 ON (totleid=tid) WHERE eat_date>=CONVERT(datetime,'"&the_date&"') AND eat_date<DATEADD(month,1,CONVERT(datetime,'"&the_date&"')) GROUP BY c_id

　　想象一下，如果不用这个冗余表，每次统计每人每月的餐费总帐时会多麻烦，程序效率也够呛。那么，到底什么时候可以增加一定的冗余数据呢？我认为有2个原则：

　　１、用户的整体需求。当用户更多的关注于，对数据库的规范记录按一定的算法进行处理后，再列出的数据。如果该算法可以直接利用后台数据库系统的内嵌函数来完成，此时可以适当的增加冗余字段，甚至冗余表来保存这些经过算法处理后的数据。要知道，对于大批量数据的查询，修改或删除，后台数据库系统的效率远远高于我们自己编写的代码。（表设计中冗余字段的取舍：要根据功能应用中数据增删改查的需要，通过增加字段、增加表来简化查询等功能是完全有必要的）
　　２、简化开发的复杂度。现代软件开发，实现同样的功能，方法有很多。尽管不必要求程序员精通绝大部分的开发工具和平台，但是还是需要了解哪种方法搭配哪种开发工具的程序更简洁，效率更高一些。冗余数据的本质就是用空间换时间，尤其是目前硬件的发展远远高于软件，所以适当的冗余是可以接受的。不过我还是在最后再强调一下：不要过多的依赖平台和开发工具的特性来简化开发，这个度要是没把握好的话，后期维护升级会栽大跟头的。

感谢博主分享文章：http://www.knowsky.com/4937.html