浅谈数据库设计技巧

浅浅谈谈数据数据库设计库设计技巧技巧 说到数据库 我认为不能不先谈数据结构 1996 年 在我初入大学学习计算机编程时 当时的老师就告诉我们说 计算机程序 数据结构 算法 尽管现在的程序开发已由面向 过程为主逐步过渡到面向对象为主 但我还是深深赞同 8 年前老师的告诉我们的公式 计 算机程序 数据结构 算法 面向对象的程序开发 要做的第一件事就是 先分析整个程 序中需处理的数据 从中提取出抽象模板 以这个抽象模板设计类 再在其中逐步添加处 理其数据的函数 即算法 最后 再给类中的数据成员和函数划分访问权限 从而实现封 装 数据库的最初雏形据说源自美国一个奶牛场的记账薄 纸质的 由此可见 数据库并不 一定是存储在电脑里的数据 里面记录的是该奶牛场的收支账目 程序员在将其整理 录入到电脑中时从中受到启发 当按照规定好的数据结构所采集到的数据量大到一定程度 后 出于程序执行效率的考虑 程序员将其中的检索 更新维护等功能分离出来 做成单 独调用的模块 这个模块后来就慢慢发展 演变成现在我们所接触到的数据库管理系统 DBMS 程序开发中的一个重要分支 下面进入正题 首先按我个人所接触过的程序给数据库设计人员的功底分一下类 没有系统学习过数据结构的程序员 这类程序员的作品往往只是他们的即兴玩具 他们往往习惯只设计有限的几个表 实现某类功能的数据全部塞在一个表中 各表之间几 乎毫无关联 网上不少的免费管理软件都是这样的东西 当程序功能有限 数据量不多的 时候 其程序运行起来没有什么问题 但是如果用其管理比较重要的数据 风险性非常大 系统学习过数据结构 但是还没有开发过对程序效率要求比较高的管理软件的程 序员 这类人多半刚从学校毕业不久 他们在设计数据库表结构时 严格按照教科书上的 规定 死扣 E R 图和 3NF 别灰心 所有的数据库设计高手都是从这一步开始的 他们的 作品 对于一般的 access 型轻量级的管理软件 已经够用 但是一旦该系统需要添加新功 能 原有的数据库表差不多得进行大换血 第二类程序员 在经历过数次程序效率的提升 以及功能升级的折腾后 终于升 级成为数据库设计的老鸟 第一类程序员眼中的高人 这类程序员可以胜任二十个表以上 的中型商业数据管理系统的开发工作 他们知道该在什么样的情况下保留一定的冗余数据 来提高程序效率 而且其设计的数据库可拓展性较好 当用户需要添加新功能时 原有数 据库表只需做少量修改即可 在经历过上十个类似数据库管理软件的重复设计后 第三类程序员中坚持下来没 有转行 而是希望从中找出 偷懒 窍门的有心人会慢慢觉悟 从而完成量变到质变的转 换 他们所设计的数据库表结构有一定的远见 能够预测到未来功能升级所需要的数据 从而预先留下伏笔 这类程序员目前大多晋级成数据挖掘方面的高级软件开发人员 第三类程序员或第四类程序员 在对现有的各家数据库管理系统的原理和开发都 有一定的钻研后 要么在其基础上进行二次开发 要么自行开发一套有自主版权的通用数 据库管理系统 我个人正处于第三类的末期 所以下面所列出的一些设计技巧只适合第二类和部分第 三类数据库设计人员 同时 由于我很少碰到有兴趣在这方面深钻下去的同行 所以文中 难免出现错误和遗漏 在此先行声明 欢迎大家指正 不要藏私哦 8 一 树型关系的数据表 不少程序员在进行数据库设计的时候都遇到过树型关系的数据 例如常见的类别表 即一个大类 下面有若干个子类 某些子类又有子类这样的情况 当类别不确定 用户希 望可以在任意类别下添加新的子类 或者删除某个类别和其下的所有子类 而且预计以后 其数量会逐步增长 此时我们就会考虑用一个数据表来保存这些数据 按照教科书上的教 导 第二类程序员大概会设计出类似这样的数据表结构 类别表 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 程序 其中 我非常赞同敏捷软件开发这个观 点 在最初几乎不进行预先设计 但是一旦需求发生变化 此时作为一名追求卓越的程序 员 应该从头审查整个架构设计 在此次修改中设计出能够满足日后类似修改的系统架构 下面是我在需要添加 长度 的属性时所提供的修改方案 去掉商品信息表 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 中添 加一条记录即可 不要害怕变化 被第一颗子弹击中并不是坏事 坏的是被相同轨道飞来 的第二颗 第三颗子弹击中 第一颗子弹来得越早 所受的伤越重 之后的抵抗力也越强 8 待续 三 多用户及其权限管理的设计 开发数据库管理类的软件 不可能不考虑多用户和用户权限设置的问题 尽管目前市 面上的大 中型的后台数据库系统软件都提供了多用户 以及细至某个数据库内某张表的 权限设置的功能 我个人建议 一套成熟的数据库管理软件 还是应该自行设计用户管理 这块功能 原因有二 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