长治市太行中学火箭班:数据库设计

数据库设计 Step by Step (1)
2011-03-27 00:47 by DBFocus, 8434 visits,收藏,编辑
引言：一直在从事数据库开发和设计工作，也看了一些书籍，算是略有心得。很久之前就想针对关系数据库设计进行整理、总结，但因为种种原因迟迟没有动手，主要还是惰性使然。今天也算是痛下决心开始这项卓绝又令我兴奋的工作。这将是一个系列的文章，我将以讲座式的口吻展开讨论（个人偷懒，这里的总结直接拿去公司培训新人用）。
系列的第一讲我们先来回答下面几个问题

数据库是大楼的根基
大多数程序员都很急切，在了解基本需求之后希望很快的进入到编码阶段（可能只有产出代码才能反映工作量），对于数据库设计思考得比较少。
这给系统留下了许多隐患。许多软件系统的问题，如：输出错误的数据，性能差或后期维护繁杂等，都与前期数据库设计有着密切的关系。到了这个时候再想修改数据库设计或进行优化等同于推翻重来。
我经常把软件开发比作汽车制造。汽车制造会经过图纸设计，模型制作，样车制造，小批量试生产，最后是批量生产等步骤。整个过程环环相扣，后一过程是建立在前一过程正确的前提基础之上的。如果在图纸设计阶段发现了一个纰漏，我们可以重新进行图纸设计，如果到了样车制造阶段发现这个错误，那么我们就要把从图纸设计到样车制造的阶段重来，越到后面发现设计上的问题，所付出的代价越大，修改的难度也越大。
数据库是整个应用的根基，没有坚实的根基，整个应用也就岌岌可危了。
强大的数据库面对不良设计也无能为力
现代数据库管理系统（DBMS）提供了方便的图形化界面工具，通过这些工具可以很方便的创建表、定义列，但我们设计出的结构好吗？
关系数据库有许多非常好的特性，但设计不当会使这些特性部分或完全的丧失。
我们来看看以下几个数据库不良设计造成的场景：
1. 数据一致性的丧失
一个订单管理系统，维护着客户和客户下的订单信息。使用该系统的用户在接到客户修改收货地址的电话后，在系统的客户信息页面把该客户的收货地址进行了修改，但原先该客户的订单还是送错了地址。
2. 数据完整性的丧失
公司战略转移，准备撤出某地区。系统操作人员顺手把该地区的配置信息在系统中进行删除，系统提示删除成功。随后问题就来了，客服人员发现该地区的历史订单页面一打开就出错。
3. 性能的丧失
一个库存管理系统，仓库管理员使用该系统记录每一笔进出货情况，并能查看当前各货物的库存情况。在系统运行几个月后，仓库管理员发现打开当前库存页面变得非常慢，而且整个趋势是越来越慢。
上面这些场景都是由于数据库设计不当造成的，根源包括：设计时引入了冗余字段，没有设计合理的约束，对性能没有进行充足设计等，上面的例子也只是沧海一粟。

数据库平台无关性
我在这个系列博客里讨论的数据库设计不针对任何一个关系数据库产品。无论你使用的是Oracle，SQL Server，Sybase，亦或是开源数据库如：MySQL，SQLite等，都可以用来实践我们这里讨论的设计方法和设计理念，设计是这个系列博文的核心和灵魂。
注：在文中我会选用一个数据库产品来进行演示，大家可以选用自己熟悉的数据库产品来实验。本文最后会给出一些免费数据库产品的链接，大家可以下载学习。
一起学习共同进步
无论你是数据库设计师，应用架构师，软件工程师，数据库管理员（DBA），软件项目经理，软件测试工程师等项目组成员，都能从该系列博文中有所收获。大家一起讨论，共同进步。
内容涉及领域
我对这一系列博文现在的设想是涉及数据库设计的整个过程。从需求分析开始，到数据库建模（概念数据建模），进行范式化，直至转化为SQL语句。

在我们一头扎进数据库设计之前，我们先了解一下除了关系型数据库之外的数据存储方式。
平面文件（Flat File）
包括以.txt和.ini结尾的文件。
eg: 一个.ini文件的内容：
------------------------------------------------------------
[WebSites]
MyBlog=http://www.cnblogs.com/DBFocus
[Directorys]
Image=E:\DBFocus Project\Img
Text=E:\DBFocus Project\Documents
Data=E:\DBFocus Project\DB
------------------------------------------------------------
优点：
文件的存储形式非常简单，普通的编辑器都能对其进行打开、修改
缺点：
无法支持复杂的查询
没有任何验证功能
对平面文件中间的内容进行插入、删除操作其实是重新生成了一个新文件
适用场景：
存放小量，修改不频繁的数据，如应用配置信息
Windows注册表
错误的修改Windows注册表会引起系统的紊乱，故不建议把很多数据存放在注册表中。
Windows注册表为树形结构，存放着一些系统配置信息和应用配置信息。
通过把不同的配置存放在注册表的不同分支上，使得应用程序公共配置信息与用户个人配置信息分离。
eg：某文档版本管理系统，能通过配置与本主机上安装的文件比较器建立关联进行文档比较。这是一个公共配置信息，文件比较器路径可以存放在注册表的HKEY_LOCAL_MACHINE\SOFTWARE分支下。
同时该文档版本管理系统能记录用户最近打开的10个文档路径。这是用户个人配置信息，对于不同的Windows用户最近打开的10个文档可以不同，这些配置信息可存放在注册表的HKEY_CURRENT_USER\Software分支下。
Excel表单（Spreadsheets）
优点：
Excel 非常普及，用户对于Spreadsheet的表现形式非常熟悉
可以进行简单统计，方便出各种图表
缺点：
不适用于许多Spreadsheet之间关系复杂的情况
无法应对复杂查询
数据验证功能弱
适用场景：
数据量不是非常大的办公自动化环境
XML
XML是一种半结构化的数据。相比于超文本标记语言（HTML），其标签是可以自行定义的，即可扩展的。
eg：一个XML文件内容
-----------------------------------------------------



Richard Storm


Ben
Breaker


Carol
Enflame
Candy




-----------------------------------------------------
XML文件有几个特点。
首先，XML标签要求严格对应，且不能出现交错的现象。
其次，XML文件必须有一个根节点，该节点包含所有其他元素。
第三，同级别的不同节点内不必包含相同的元素，如上例中第二个学生Carol有一个特别的节点NickName。这个特性使得在某些场景中XML比关系数据库更能应对变化。
优点：
自然的层次型结构
文本内容通过标签是自解释的
通过XSD（XML Schema语言）可以验证XML的结构
有许多辅助型技术如：XPath, XQuery, XSL, XSLT等
一些商业数据库（如Oracle，SQL Server）已支持XML数据的存储与操作
缺点：
数据的冗余信息较多
无法支持复杂的查询
验证功能有限
对XML中间的内容进行插入、删除操作其实是重新生成一个新文件
适用场景：
适合存放数据量不大，具有层次型结构的数据，如树形配置信息
NoSQL数据库
非关系型数据库我接触的不是很多，除了给出一些产品名称之外不做很多展开。园子里已有一些文章，本文最后也给出了链接供大家学习、研究。
1. Key-Value数据库
Redis, Tokyo Cabinet, Flare
2. 面向文档的数据库
MongoDB, CouchDB
3. 面向分布式计算的数据库
Cassandra, Voldemort
这几年NoSQL非常热。我认为NoSQL并不是“银弹”，在某些SNS应用场景中NoSQL显示了其优越性，但在如金融行业等对数据的一致性、完整性、可用性、事务性高要求的场景下，现在的NoSQL就未必适用。我们应充分分析应用的需求，非常谨慎地选择技术和产品。

主要内容回顾
1.数据库设计对于软件项目成功的关键作用
2.本课程与数据库产品无关，核心是设计的理念和方法
3.各种数据存储所适用的场景
参考资料
1.Oracle Database 10g Express Edition
2.SQL Server 2008 R2 Express – Overview
3.SQLite Home Page
4.NoSQL数据库笔谈
数据库设计 Step by Step (2)
2011-04-09 22:51 by DBFocus, 4335 visits,收藏,编辑
引言：数据库设计 Step by Step (1)得到这么多朋友的关注着实出乎了我的意外。这也坚定了我把这一系列的博文写好的决心。近来工作上的事务比较繁重，加之我期望这个系列的文章能尽可能的系统、完整，需要花很多时间整理、思考数据库设计的各种资料，所以文章的更新速度可能会慢一些，也希望大家能够谅解。
系列的第二讲我们将站在高处俯瞰一下数据库的生命周期，了解数据库设计的整体流程

数据库生命周期
大家对软件生命周期较为熟悉，数据库也有其生命周期，如下图所示。
图（1）数据库生命周期
数据库的生命周期主要分为四个阶段：需求分析、逻辑设计、物理设计、实现维护。
这个系列的博文将主要关注数据库生命周期中的前两个阶段（需求分析、逻辑设计），还会涉及反范式化设计的一些内容。如图中高亮圈出的部分。
数据库的物理设计，包括索引的选择与优化、数据分区等内容。这些内容也非常丰富，而且可以自成体系，园子里也有很多好文章，故在本系列中不作主要关注。本文最后将给出一些链接供大家参考。
数据库生命周期的四个阶段又能细分为多个小步骤，我们配合图（1）来看看每一小步包含的内容。
阶段1 需求分析
数据库设计与软件设计一样首先需要进行需求分析。
我们需要与数据的创造者和使用者进行访谈。对访谈获得的信息进行整理、分析，并撰写正式的需求文档。
需求文档中需包含：需要处理的数据；数据的自然关系；数据库实现的硬件环境、软件平台等；
图（2）阶段1 需求分析
阶段2 逻辑设计
使用ER或UML建模技术，创建概念数据模型图，展示所有数据以及数据间关系。最终概念数据模型必须被转化为范式化的表。
数据库逻辑设计主要步骤包括：
a) 概念数据建模
在需求分析完成后，使用ER图或UML图对数据进行建模。使用ER图或UML图描述需求中的语义，即得到了数据概念模型（Conceptual Data Model），例如：三元关系（ternary relationships）、超类（supertypes）、子类（subtypes）等。
eg:  零售商视角，产品/客户数据库的ER模型简图
注：ER图的含义，以及详细标记方法将在该系列的下一篇博文中进行讨论
图（3）阶段2(a) 概念数据建模
b) 多视图集成
当在大型项目设计或多人参与设计的情况下，会产生数据和关系的多个视图。这些视图必须进行化简与集成，消除模型中的冗余与不一致，最终形成一个全局的模型。多视图集成可以使用ER建模语义中的同义词(synonyms)、聚合(aggregation)、泛化(generalization)等方法。多视图集成在整合多个应用的场景中也非常重要。
eg: 集成零售商ER图与客户ER图
零售商ER图如图（3）所示。客户视角，产品/客户数据库的ER模型简图如下：
图（4）以客户为关注点绘制的ER图
注：现在市面上有许多辅助建模工具可以绘制ER图。使用Sybase的PowerDesigner绘制与图（4）相同语义的ER图如下：

其标记法与图（4）中略有不同，这将在今后的博文中加以说明。
这里需要指出的是辅助软件的使用不是设计的核心，大家不要被这些工具迷惑。所以后文中我们将主要使用手绘。只要掌握了ER图的语义，使用这些软件都不会是件难事。
集成零售商ER图与客户ER图
图（5） 阶段2(b) 多视图集成
c) 转化概念数据模型为SQL表
根据映射规则，把ER图中的实体与关系转化为SQL表结构。在这一过程中我们将识别冗余的表，并去除这些表。
eg: 把图（5）中的customer, product, salesperson实体转化为SQL表
图（6） 阶段2(c)转化概念数据模型为SQL表
d) 范式化
范式化是数据库逻辑设计中的重要一步。范式化的目标是尽可能去除模型中的冗余信息，从而消除关系模型更新、插入、删除异常（anomalies）。
讲到范式化就会引出函数依赖（Functional Dependency）这一概念。函数依赖(FDs)源自于概念数据模型图，反映了需求分析中的数据关系语义。不同实体之间的函数依赖表示各个实体唯一键之间的依赖。实体内部也有函数依赖，反映了实体中键属性与非键属性之间的依赖。在保证数据完整性约束的前提下，基于函数依赖对候选表进行范式化（分解、降低数据冗余）。
eg: 对图（6）中的Salesperson表进行范式化，消除更新异常（update anomalies）
图（7） 阶段2(d)范式化
阶段3 物理设计
数据库物理设计包括选择索引，数据分区与分组等。
逻辑设计方法学通过减少需要分析的数据依赖，简化了大型关系数据库的设计，这也减轻了数据库物理设计阶段的压力。
1. 概念数据建模和多视图集成准确地反映了现实需求场景
2. 范式化在模型转化为SQL表的过程中保留了数据完整性
数据库物理设计的目标是尽可能优化性能。
物理设计阶段，全局表结构可能需要进行重构来满足性能上的需求，这被称为反范式化。
反范式化的步骤包括：
1. 辨别关键性流程，如频繁运行、大容量、高优先级的处理操作
2. 通过增加冗余来提高关键性流程的性能
3. 评估所造成的代价（对查询、修改、存储的影响）和可能损失的数据一致性
阶段4 数据库的实现维护
当设计完成之后，使用数据库管理系统（DBMS）中的数据定义语言（DDL）来创建数据结构。
数据库创建完成后，应用程序或用户可以使用数据操作语言（DML）来使用（查询、修改等）该数据库。
一旦数据库开始运行，就需要对其性能进行监视。当数据库性能无法满足要求或用户提出新的功能需求时，就需要对该数据库进行再设计与修改。这形成了一个循环：监视 –> 再设计 –>  修改 –> 监视…。

在进行数据库设计之前，我们先回顾一下关系数据库的相关基本概念。
这里只做一个提纲挈领的简介，大家可以根据相应的线索进行扩展。
表、行、列
关系数据库可以想象成表的集合，每个表包含行与列。（可以想象成一个Excel workbook，包含多个worksheet）。
表在关系代数中被称为关系，这也是关系数据库名称的起源（不要与表之间的外键关系混淆）。
列在关系代数中被称为属性（attribute）。列中允许存放的值的集合称为列的域（域与数据类型密切相关，但并不完全相同）。
行在关系代数中的学名是元组（tuple）。
关系数据库的理论基础来自于“关系代数”。但在关系代数中，一个集合的各个元组没有次序的概念，在关系数据库中为了方便使用，定义了行的次序。
键、索引
键是一种约束，目的是保证数据完整性
1. 复合键（Compound key）：由多个数据列组成的键
2. 超键（Superkey）：列的集合，其中任何两行都不会完全相同
3. 候选键（Candidate key）：首先是一个超键，同时这个超键中的任何列的缺失都会破坏行的唯一性
4. 主键（Primary key）：指定的某个候选键
索引是数据的物理组织形式，目的是提高查询的性能
约束
基本约束
not null constraint, domain constraint
检查约束（Check Constraints）
eg: Salary > 0
主键约束（Primary Key Constraints）
实体完整性（entity integrity），没有两条记录是完全相同的，组成主键的字段不能为null
唯一性约束（Unique Constraints）
外键约束（Foreign Key Constraints）
也被称为引用完整性约束，eg:

关系数据库操作
1.选择（Selection）
2.映射（Projection）
3.联合（Union）
4.交集（Intersection）
5.差集（Difference）
6.笛卡尔积（Cartesian Product）
7.连接（Join）
上述7种是最基本的关系数据库操作，对应于集合论中的关系运算。
有些书籍中还会加入改名（Rename），除（Divide）等关系操作。

主要内容回顾
1. 数据库生命周期的四个阶段：需求分析、逻辑设计、物理设计、实现维护。
2. 关系数据库的理论基础是关系代数。
数据库物理设计参考资料
第一个链接是我针对查询优化作的读书笔记，后三个链接是SQLServerCentral中几篇关于索引的文章（需要简单注册后才能看到全文）
1. 查询优化系列（查询优化（1），查询优化（2），查询优化（3），查询优化（4），查询优化（5）——总结）
2.Part 1 - The basics of indexes
3.Part 2 - The Clustered Index
4.Part 3 - The Non-clustered index
数据库设计 Step by Step (3)
2011-04-24 11:46 by DBFocus, 3554 visits,收藏,编辑
引言：数据库设计 Step by Step (2)在园子里发表之后，收到了一些邮件，还有朋友直接电话我询问为什么不包含数据库物理设计方面的内容。我在这里解释一下，数据库物理设计与数据库产品是密切相关的，本系列的专注点是较为通用的数据库设计理念与方法，这也是国内软件项目中容易被忽视的一块。今天我们将学习实体关系（ER）模型构件及其语义，这是数据库逻辑设计的基础。内容可能有些枯燥，但却非常重要和有用。
由于内容比较多，我们将分两讲来学习实体关系模型构件。
今天我们先来学习基本实体关系模型。

实体关系（ER）模型的目标是捕获现实世界的数据需求，并以简单、易理解的方式表现出来。ER模型可用于项目组内部交流或用于与用户讨论系统数据需求。
ER模型中的基本元素
基本的ER模型包含三类元素：实体、关系、属性
图1 实体、关系、属性的ER构图
实体（Entities）：实体是首要的数据对象，常用于表示一个人、地方、某样事物或某个事件。一个特定的实体被称为实体实例（entity instance或entity occurrence）。实体用长方形框表示，实体的名称标识在框内。一般名称单词的首字母大写。
关系（Relationships）：关系表示一个或多个实体之间的联系。关系依赖于实体，一般没有物理概念上的存在。关系最常用来表示实体之间，一对一，一对多，多对多的对应。关系的构图是一个菱形，关系的名称一般为动词。关系的端点联系着角色（role）。一般情况下角色名可以省略，因为实体名和关系名已经能清楚的反应角色的概念，但有些情况下我们需标出角色名来避免歧义。
属性（Attributes）：属性为实体提供详细的描述信息。一个特定实体的某个属性被称为属性值。Employee实体的属性可能有：emp-id, emp-name, emp-address, phone-no……。属性一般以椭圆形表示，并与描述的实体连接。属性可被分为两类：标识符（identifiers），描述符（descriptors）。Identifiers可以唯一标识实体的一个实例（key），可以由多个属性组成。ER图中通过在属性名下加上下划线来标识。多值属性（multivalued attributes）用两条线与实体连接，eg：hobbies属性（一个人可能有多个hobby，如reading，movies…）。复合属性（Complex attributes）本身还有其它属性。
辨别强实体与弱实体：强实体内部有唯一的标识符。弱实体（weak entities）的标识符来自于一个或多个其它强实体。弱实体用双线长方形框表示，依赖于强实体而存在。
深入理解关系
关系在ER模型中扮演了非常重要的角色。通过ER图可以描述实体间关系的度、连通数、存在性信息。
我们一一来解释这些概念。首先我们来看一下关系在ER图中的各种语义。
图2 关系的度、连通数、存在性
关系的度（Degree of a Relationship）
表示关系所关联的实体数量。二元关系与三元关系的度分别为2和3，以此可以类推至n元。二元关系是最常见的关系。
一个Employee与另一个Employee之间的领导关系称为二元回归关系。如图2中所示，Employee实体通过关系manages与自身连接。由于Employee在这一关系中扮演两个角色，故标出了角色名（manager和subordinate）。
三元关系联系三个实体。当二元关系无法准确描述关联的语义时，就需要使用三元关系。我们来看下面这个例子，下图（1）能反映出一个Employee在某个Project中使用了什么Skill。下图（2）只能看出Employee有什么Skill，参与了哪些Project，但无法知道在某个Project中使用的特定Skill。
图3 三元关系蕴含的语义
需要注意的是有些情况下会错误的定义三元关系。这些三元关系可分解为2个或3个二元关系，来达到化简与语义的纯净。以后的博文中会进一步详细讨论三元关系。
一个实体可以参与到任意多个关系中。每个关系可以联系任意多个元（实体），而且两个实体之间也能有任意多个二元关系。
关系的连通数（Connectivity of a Relationship）
表示关系所关联的实例数量的约束。
连通数的值可以是“一”或“多”。“一”这一端，在ER图中通过在实体与关系间标记“1”表示。“多”一端标记“N”表示。如图2中关系连通数部分，“一”对“一”：Department is managed by Employee；“一”对“多”：Department has Employees；“多”对“多”：Employee may work on many Projects and each Project may have many Employees。
有些情况下最大连通数是确定的，可以用数值代替N。如：田径队队员有12人。
关系的属性
关系也能有属性。如下图4所示，某员工参与某项目的起始日期，某员工在某项目中被分配的任务只有放在关系works-on上才有意义。
图4 关系的属性
需要注意的是关系的属性一般出现在“多”对“多”的二元关系或三元关系上。一般“一”对“一”或“一”对“多”关系上不会放属性（会引起歧义）。而且这些属性可以移至一端的实体中。如下图5所示，如果部门与员工（经理）之间是“一”对“一”关系，在建模中可能把start-date作为关系is managed by的属性（表示被接管的时间），这个属性可以移至Department或Employee实体中。
图5 部门与经理之间的一对一管理关系
大家可以思考一下如果部门和经理之间是“多”对“多”关系，即交叉管理，那又会怎样？
关系中实体的存在性（Existence of an Entity in a Relationship）
关系中实体的存在性可以是强制的或可选的。当关系中的某一边实体（无论是“一”或“多”端）必须总是存在，则该实体为强制的。反之，该实体为可选的。
在实体与关系之间的连接线上标识“0”来表示可选存在性。含义是最小连通数为0。
强制存在性表示最小连通数为1。在存在性不确定或不可知的情况下，默认最小连通数为1。
在ER图中最大连通数显式地标识在实体旁边。如图6所示，其蕴含的语义为一个Department有且只有一个Employee来当经理，一个Employee可能是一个Department的经理，也可能不是。
图6 关系中实体的存在性
其他概念数据模型标记法
前文中使用的ER构图方法是Peter Chen 1976年提出的。在现代数据库设计领域，还有其他多种ER模型标记法。
我们来看一下另一种使用较多的标记法，“crow’s-foot”（鱼尾纹）标记法，并与前面介绍的标记法进行一个简单对比。
学习每一种标记法没有意义。在你的组织中推广应用一种标记法，使其成为大家共通的“语言”。
图7 Chen式标记法与crow’s-foot标记法对照

主要内容回顾
1. 组成ER模型的基本元素包括：实体、关系、属性
2. 深入理解关系中包含的语义：关系的度、关系的连通数、关系的存在性
3. 了解ER模型的不同标记法，掌握其中一种标记法，并在你的项目中推广使用
实体关系模型参考
1. Entity-relationship model（http://en.wikipedia.org/wiki/Entity-relationship_model）
2.  Entity-relationship modelling（http://www.inf.unibz.it/~franconi/teaching/2000/ct481/er-modelling/）
数据库设计 Step by Step (4)
2011-05-07 11:44 by DBFocus, 2258 visits,收藏,编辑
引言：数据库设计 Step by Step (3)中我们讨论了基本实体关系模型构件及其语义。这些概念非常重要，是今天这一讲的基础，在开始本文内容之前建议大家可以再回顾一下上一篇的内容。今天我们将讨论高级实体关系模型构件，与上一篇一起涵盖了ER模型构图的大部分内容。三元关系是今天这一讲的难点，大家可以重点关注。

泛化（Generalization）：超类型与子类型
原始的ER模型已经能描述基本的数据和关系，但泛化（Generalization）概念的引入能方便多个概念数据模型的集成。
泛化关系是指抽取多个实体的共同属性作为超类实体。泛化层次关系中的低层次实体——子类型，对超类实体中的属性进行继承与添加，子类型特殊化了超类型。
ER模型中的泛化与面向对象编程中的继承概念相似，但其标记法（构图方式）有些差异。
下图表示员工与经理、工程师、技术员、秘书之间的泛化关系。Employee为超类实体，并包含共同属性，Manager、Engineer、Technician、Secretary都是Employee的子类实体，它们能包含自身特有的属性。
图1  Employee与Manager、Engineer、Technician、Secretary之间的泛化关系
泛化可以表达子类型的两种重要约束，重叠性约束（disjointness）与完备性约束（completeness）。
重叠性约束表示各个子类型之间是否是排他的。若为排他的则用字母“d”标识，否则用“o”标识（o -> overlap）。图1中各子类实体概念上是排他的。
对员工、客户实体进行泛化，抽象出超类实体个人，得到如下关系图。由于部分Employee也可能是Customer，故子类实体Employee与Customer之间概念是重叠的。
图2  Individual与Employee、Customer之间的泛化关系
完备性约束表示所有子类型在当前系统中是否能完全覆盖超类型。若能完全覆盖则在超类型与圆圈之间用双线标识（可以把双线理解为等号）。在图2中子类实体Employee与Customer能完全覆盖超类Individual实体。
聚合（Aggregation）
聚合是与泛化抽象不同的另一种超类型与子类型间的抽象。
泛化表示“is-a”语义，聚合表示“part-of”语义。聚合中子类型与超类型间没有继承关系。
聚合关系的标记法是在圆圈中标识字母“A”来表示。
下图表示软件产品由程序与用户手册组成。
图3  Software-product与Program、User’s Guide之间的聚合关系
三元关系（Ternary Relationships）
当通过二元关系无法准确描述三个实体间的联系时，我们需要使用三元关系。
三元关系中“连通数”的确定方法：
a) 以三元关系中的一个实体作为中心，假设另两个实体都只有一个实例
b) 若中心实体只有一个实例能与另两个实体的一个实例进行关联，则中心实体的连通数为“一”
c) 若中心实体有多于一个实例能与另两个实体实例进行关联，则中心实体的连通数为“多”
注：什么时候需要使用三元关系的实例请参看：数据库设计 Step by Step (3)中的“关系的度（Degree of a Relationship）”小节。关系的“连通数”概念请参看：数据库设计 Step by Step (3)中的“关系的连通数（Connectivity of a Relationship）”小节。
我们来看几个三元关系的实例，注意各个图中关系的度，并理解其中的语义。
图4  技术员在项目中使用手册的关系
图4中蕴含的语义为：
a) 一名技术员对于每一个项目使用一本手册
b) 每一本手册对于每一个项目属于一名技术员
c) 一名技术员可能在做多个项目，对于不同的项目维护不同的手册
用数学中的函数依赖表示图4的关系：
a) emp-id, project-name -> notebook-no
b) emp-id, notebook-no -> project-name
c) project-name, notebook-no -> emp-id
图5  员工被分配不同地点的项目之间的关系
图5中蕴含的语义为：
a) 每一个员工在一个地点只能被分配一个项目，但可以在不同地点做不同的项目
b) 在一个特定的地点，一个员工只能做一个项目
c) 在一个特定的地点，一个项目可以由多个员工来做
用数学中的函数依赖表示图5的关系：
a) emp-id, loc-name -> project-name
b) emp-id, project-name -> loc-name
图6  经理管理项目与工程师的关系
图6中蕴含的语义为：
a) 一名经理手下的一名工程师可能参与多个项目
b) 一名经理管理的一个项目可能会有多名工程师
c) 做某一个项目的一名工程师只会有一名经理
用数学中的函数依赖表示图6的关系：
a) project-name, emp-id -> mgr-id
图7  员工在项目中使用技能的关系
图7中蕴含的语义为：
a) 一名员工在一个项目中可以使用多种技能
b) 一名员工的一种技能可以在多个项目中使用
c) 一种技能在一个项目中可以被多名员工使用
图7各实体之间没有函数依赖
上述4种形式的三元关系，连通数为“一”的实体数量与该三元关系反映的函数依赖语义的数目一致。
三元关系也能有属性。属性值由三个实体的键的组合唯一确定。
n元关系（General n-ary Relationships）
三元关系可以扩展到n元关系，描述n个实体之间的关系。
一般而言，n元关系中每一个连通数为“一”的实体的键都会出现在一个函数依赖表达式的右侧。
对于n元关系，使用语言来表达其中的约束相对较为困难。建议使用数学形式即函数依赖（FD）来表现。
n元关系的函数依赖条目数量与关系图中“一”端实体的数量相同（0～n条）。
n元关系的函数依赖表达式包含n个元素，n-1个元素出现在表达式左侧，1个元素出现在右侧。
图8  n元关系图例
排他性约束（Exclusion Constraint）
一般（默认）情况下，多种关系之间是兼容的“或”关系，即允许任意或所有实体参与这些关系。
在某些情况下，多种关系之间是非兼容性“或”关系，即参与关系的实体只能选择其中一种关系，不能同时选择多种关系。
下图表示的语义为：一项工作任务要么被归为外部项目中，要么被归为内部项目中，不可能同时属于外部项目和内部项目。
图9  排他性约束关系图例

我们对上一篇数据库设计 Step by Step (3)与本篇的重点内容做一个总的回顾
1. 我们讨论了ER模型及构图的基本概念
2. 一个实体可以是一个人，地方，东西或事件
3. 属性是实体的描述信息
4. 属性可以是唯一标识或非唯一的描述
5. 关系描述了实体之间“一对一”，“一对多”，“多对多”的联系
6. 关系的度反映了参与关系的实体数量，如二元关系，三元关系，n元关系
7. 角色（名）定义了一个实体在一个关系中所具有的功能
8. 关系的存在概念表示一个实体在关系中是强制存在还是可选的
9. 泛化允许把实体抽象成超类与子类
10. 三元关系可使用函数依赖来定义
数据库设计 Step by Step (5)
2011-05-28 21:49 by DBFocus, 2871 visits,收藏,编辑
引言：数据库设计 Step by Step (4)中我们讨论了泛化关系、聚合关系、三元关系等高级实体关系模型构件及其语义。从本次讲座开始我将引领大家开始数据库设计之旅，我们将从需求分析开始，途中将经过概念数据建模、多视图集成、ER模型转化为SQL、范式化等过程，最终得到完整、可用的SQL表。
需求分析在数据库生命周期中至关重要，通常也是涉及人员最多的步骤。数据库设计师在这个阶段必须走访最终用户，与他们进行访谈，从而确定用户想在系统中存储什么数据以及想怎样使用这些数据。我们将需求分析分为两个步骤：1.理解用户需求；2.提取业务规则。这次我们先讨论“理解用户需求”。

设计定制化产品——无论是一个数据库、一幅平面广告或一个玩具，都是一个“翻译”的过程。我们需要把浮现在客户脑海中的模糊想法、愿望挖掘出来，并“翻译”成满足他们需求的现实产品。
这个“翻译”过程的第一步就是理解用户的需求。设计最好的订单处理系统对于需要一个电路设计工具的客户来说毫无意义。对客户需求理解的不完全会造成错误或无用的设计与开发，这浪费了你、你的团队还有客户的时间与金钱。（牢记数据库是整个应用开发的根基）
制定一个计划
我们首先制定了一个计划，其中包含挖掘客户需求的一系列步骤。遵循这些步骤能更好地理解客户需求，但在一些项目中我们不需要遵循所有的步骤。举例来说，如果客户是单个人且需求很明确时，我们就不需要进行“搞清谁是谁”与“头脑风暴”了。当客户的数据需要保密时，我们就不能“尝试客户的工作”了。在另一些项目中，调整这些步骤的顺序会更为合适。例如我们可能在去拜访客户和观察他们工作之前先进行“头脑风暴”。
以下按照最普遍的顺序列出了各个步骤。大家根据不同项目的情况可进行灵活调整，目标只有一个就是更好地理解用户需求。
列出问题清单 拜访客户 搞清谁是谁 挖掘客户大脑 尝试客户的工作 学习现有操作 头脑风暴 展望未来 理解客户的质疑 弄清客户的真正需求 优先级 确认你的理解 撰写需求文档
下面我们将一一解释每一个步骤。
列出问题清单
我们需要思考，向客户问些什么问题可以帮助我们了解项目的目标和范畴（scope）。以下几个方面的问题可以作为起始点。
功能：
以下问题主要涉及系统应完成的功能与目标。
系统应该做些什么？ 为什么你想建这个系统？ 系统看上去应该是怎样的？ 需要些什么报表？ 用户需要自己定义新报表吗？ 系统的操作者会是谁？
数据需求：
这些问题是为了弄清项目的数据需求。了解需要些什么数据能帮助我们定义数据库表。
系统界面上需要展现哪些数据？ 这些数据应该由谁来提供？ 这些数据是如何关联的？ 这些工作现在是如何处理的？数据来自哪里？
数据完整性：
这些问题能帮助我们在构建数据库时定义完整性约束。
哪些数据是必须填写的？(eg: 一条客户记录必须有电话信息吗？) 数据的有效域是什么？(eg: 电话号码是否有格式规定？地址数据应有多长？) 系统是否需要根据邮编来检验城市的有效性？ 系统中是否必须在定义了客户之后才能下订单？ 系统要求多高的可用性等级？(系统需要7×24的可用性吗？数据的备份频率要多高？)
安全性：
这些问题能帮助我们了解客户对权限控制与审计方面的需求。
是否每个用户都需要一个不同的密码？ 是否需要控制不同的用户所能访问的数据？(eg: 销售代表有权限看到客户的信用卡账号，但订单录入专员却不能) 存储在数据库中的数据是否需要加密？ 谁做了什么操作是否需要记录以便于审计？(eg: 记录销售代表提高客户级别的操作，在需要时可以追溯操作的原因) 系统中的客户分成几个级别？每个级别的客户有多少？ 是否已有文档记录了用户的工作与权责？
环境：
这些问题能帮助我们了解当前项目将代替其他什么系统或流程，以及项目将与其他哪些系统进行交互。
当前项目是要代替或升级现有的某系统吗？
•是否有描述现有系统的文档？
•现有系统的哪些功能是需要的？哪些是不需要的？
•现有系统处理些什么数据？这些数据是如何存储的？数据之间是如何关联的？
•是否有关于现有系统数据的文档？
当前项目必须与其他哪些系统交互？
•项目与其他系统之间如何交互？
•新项目是否需要向现有系统提供数据？如何提供？
•新项目是否需要接收现有系统的数据？如何接收？
•是否有关于其他系统的文档？
客户的整个业务流程是怎样的？(了解在整个业务流程中当前项目的作用)
拜访客户
了解我们要设计和搭建的系统的最好方式是询问客户。拿着我们在上一步中准备的问题清单安排与客户进行会面。这不会像闲聊那么轻松，向客户了解需求是一个冗长且折磨人的过程。
有时我们的穷追猛问会使客户筋疲力竭感到不快。在这些时候我们必须更为耐心，可以分几次多次会议来了解需求，每次针对几个问题或流程。我们的目标是对我们要解决的问题有一个完全且彻底的理解。
即使我们的项目只是去解决整个业务中的一小部分问题，我们也要试图去了解客户的整体业务流程，这可能会给我们带来意想不到的收获。
搞清谁是谁
意识到不同的客户可能对项目有不同的愿景。我们需要分辨出谁是领导，谁是积极支持者，谁是旁观者，谁是唱反调者。
以下列出了一些常见的客户角色：
项目发起人——一般是管理层的某位领导，他是项目的最高推动者。他会为项目协调资源，解决项目遇到的一些障碍，但他不会参与到项目每天的事务中。 项目执行负责人——他对于客户的需求和整个业务最为了解。他是了解用户需求阶段最重要的人，他必须有足够的时间来帮助我们定义项目目标以及回答我们的问题。当别人对某业务环节迟疑不决时，我们需要向他请教。 客户代表——客户代表是回答我们问题的人，他们也可能成为系统的最终用户。他们可能是某一部分业务的专家，我们需要与多个客户代表进行访谈来了解业务全貌。 利益相关者——这是项目将影响到的人，其中某些人可能同时也是客户代表。这些人可能对项目也有兴趣，但未必对系统都有发言权。我们在进行系统设计时也需要考虑对这些人的影响(特别是附带损害)。 唱反调者——这是我们需要关注的一些人。如果唱反调者只是让其他人理性或现实地来看待项目，而并不是彻底反对这个项目的话，他将是我们非常好的资源，他将帮助我们说服其他对项目抱有不切实幻想的客户。而如果唱反调者对整个项目抱有抵触时，我们就必须非常小心，有时需要项目执行负责人出面来协调这些人。
挖掘客户大脑
一旦搞清楚谁是谁之后，我们就要与项目执行负责人讨论客户需要什么。客户希望的解决方案是怎样的，需要包含什么数据，怎样呈现，以及不同数据之间如何关联。
与尽可能多的利益相关者进行交流，我们需要考虑每个人的意见，但心中要牢记项目执行负责人最为理解客户的需求并具有最终决定权。
根据项目的规模，这一过程短则几个小时，长则需要几周才能完成。
尝试客户的工作
观察客户每日的工作能帮助我们更好的理解业务。如果我们能做一会儿客户的工作来了解其中包括的内容那就最好了。
即使我们不能实际尝试客户的工作，一般我们还是可以坐在他们身边近距离观察。告诉客户我们将稍稍降低他们的工作效率并问一些愚蠢且恼人的问题，之后我们就可以开问了。在这个过程中要进行记录，学习尽可能多的东西。有些时候外行者的一些看法可能转化为客户怎么也不会想到的好主意。
学习现有操作
在尝试客户的工作之后，我们还可以看一下是否有其他途径能了解现有流程。通常公司有描述客户角色和职责的操作手册或文档。
寻找客户现在使用的数据存储方式，可能是关系型数据库系统或是电子表格或是纸质的单据等等。了解这些数据是怎样使用的，之间是如何关联的。一般物理数据库之间是通过包含冗余信息来相互关联的，如：客户ID。
头脑风暴
此刻我们已经对客户的业务和需求较为了解了。为了确认没有什么遗漏，我们需要安排头脑风暴。召集项目执行负责人和尽可能多的客户代表与利益相关者，向他们描述前期了解到的需求情况，之后让他们畅所欲言谈谈其中有什么问题或还缺什么。
在这个过程中我们不急于答应或排除任何客户的要求，我们先把客户说到的东西记录下来，并确定这些方面我们已经考虑到了。在正式开发前，我们会与项目执行负责人一起根据项目的规模与交付期限确定需求的优先级。
展望未来
在头脑风暴过程中思考一下将来的需求。问问客户他们的业务在将来是否会变化或他们希望系统将来能包含什么功能。
我们可以把他们的一些想法放入当前的项目中，即使不能也可以使我们知道将来可能会有些什么扩展，在设计数据库时我们能预先留有余地。
理解客户的质疑
一些热心且懂些技术的用户会跑来建议我们如何设计系统，应该创建怎样结构的数据表。我们可能觉得这些建议毫无意义甚至可笑。但在忽视这些建议之前我们应谨慎思考用户提出这些建议或质疑的深层原因是什么。客户比我们更了解业务，他们的建议或质疑中可能蕴含着我们还未了解到的业务变化点或某些特殊业务情况。
弄清客户的真正需求
有时客户并不了解自己的真正需求。他们能看到问题的表象，但未必清楚其根源。我们需要帮助客户寻找到问题的根源并针对问题的源头提出解决方案。
有时客户认为数据库或新系统能神奇般的提高销售，减少成本。事实上一个设计精良的数据库能减少输入差错，提高操作效率，提供数据报表，帮助客户管理数据等等。我们在与客户沟通的过程中需要告诉他们新系统能做些什么，不能做些什么，让客户建立起正确的预期。
优先级
经过先前的步骤，我们已列出一张长长的期望功能列表。其中的某些功能可能不切实际或超出了当前项目的范畴。为了使项目规模可控，我们要与客户一起定义功能的优先级。
一般我们可以把功能分为三个等级。第一优先级是在本期开发中必须包含的功能，没有完成这些功能意味着项目的失败。第二优先级是可以放到下一期开发的功能，当第一优先级的功能完成后，我们可以把第二优先级的部分功能提到当期开发。第三优先级是那些相对不重要或超出项目范畴的功能，我们可以忽略这些功能。
有些情况下优先级是可能转化的。当第一优先级的某功能非常难实现时，我们可以与客户进行沟通，确认该功能是否如此重要，是否能移到第二优先级中以避免影响项目进度。当第二优先级中的某些功能很容易实现，我们可以把该功能调整到第一优先级列表中。但做这些调整之前必须与客户沟通，得到客户的认可。
验证你的理解
梳理我们对业务和需求的理解，并一一与客户进行确认。当客户说“但是”、“除了”、“有时”等词时，我们要特别当心，确认客户只是强调了我们已经知道的东西，而没有出现新的情况。在这个阶段客户可能会想到他们之前没有考虑到的例外情况。
例外情况是数据库设计的大害。在需求分析阶段把例外情况挖掘出来，我们才能在数据库设计时有所准备。例如，我们向客户确认退货流程说：“到这里收货员会输入RMA号并点击完成按钮是吗？”客户可能会说：“嗯…这是大多数情况，但有时没有RMA号，收货员会填入None。”这就是一个客户之前没有告诉我们的重要例外情况，我们必须立刻记录下来。再有一个例子，假设客户使用的纸质订单有配送地址与账单地址两个栏目。我们向客户确认时说：“订单需要有一个配送地址和一个账单地址。”客户打断说：“有时我们需要两个配送地址，因为订单不同部分可能要送到不同的地方。”，并找出一张订单，第二个配送地址被标注在订单的边沿处。这是一个重大例外，在纸上可以很容易的进行标注，但在数据库的一个表单元中增加一个地址是不可能的。只有知道这一例外，我们才能用设计的方法解决这一需求。
撰写需求文档
需求文档描述了我们要构建的系统，该文档也被称为需求规格说明。需求文档要讲清楚我们将构建怎样的系统，该系统会完成什么工作，包含哪些功能点，并描述客户如何使用该系统来解决他们的问题。需求文档明确了项目将完成的功能，这也避免了系统交付时出现争执的情况。
需求文档中应定义可交付成果，即里程碑。里程碑是可直观展现并能验证的中间成果。客户通过里程碑能衡量项目的进度。在需求文档中还需定义最终交付成果，这也是确定项目是否完成的标准。
用例图是一种非常好的需求分析工具，可以作为需求文档的一部分。用例图的最主要功能就是用来表达系统的功能性需求或行为。用例图从业务角度上体现谁来使用系统、用户希望系统提供什么样的服务，以及用户需要为系统提供的服务，也便于软件开发人员最终实现这些功能。在官方文档中用例图包含六个元素，分别是：参与者(Actor)、用例(Use Case)、关联关系(Association)、包含关系(Include)、扩展关系(Extend)以及泛化关系(Generalization)。但是有些UML的绘图工具多提供了一种直接关联关系(Directed Association)。
参与者：是指用户在系统中扮演的角色 用例：是指外部可见的系统功能，对系统提供的服务进行描述 关联关系：连接参与者和用例，表示该参与者代表的外部系统实体与该用例描述的系统需求有关 包含关系：是来自于用例的抽象，即从数个不同的Use Case中，分离出公共的部分，而成为可以复用的用例 扩展关系：表示某一个用例的对话流程中，可能会根据条件临时插入另外一个用例，而前者称为基础用例后者称为扩展用例 泛化关系：一个用例可以被特别列举为一个或多个用例，这被称为用例泛化
eg：用户管理的用例图如下所示，图中人形图标表示参与者，椭圆表示用例（图的出处请参见“总结与参考”）


主要内容回顾
1. 搞清哪个客户扮演哪个角色
2. 从客户的脑海中挖掘信息
3. 寻找关于用户角色、职责、现有流程和现有数据的文档
4. 观察客户的工作，学习他们的业务操作
5. 进行头脑风暴，把收集到的功能需求点按优先级分成第一、第二和第三级
6. 确认对客户需求的理解
7. 撰写需求文档，包含可验证的里程碑和用例
用例图参考
1. 初学UML之-------用例图（http://blog.csdn.net/dl88250/archive/2007/10/16/1826713.aspx）
2. UML用例图（http://www.alisdn.com/wordpress/?p=1161）
数据库设计 Step by Step (6) —— 提取业务规则
2011-06-08 22:57 by DBFocus, 2090 visits,收藏,编辑
引言：数据库设计 Step by Step (5)中我们通过多种方法来理解客户的需求并撰写了需求文档。本文我们将回答三个问题。1. 为什么业务规则非常重要。2. 怎样识别业务规则。3. 如何修改关系模型并隔离出业务规则。

什么是业务规则
业务规则描述了业务过程中重要的且值得记录的对象、关系和活动。其中包括业务操作中的流程、规范与策略。业务规则保证了业务能满足其目标和义务。
生活中的一些业务规则可能是：
当顾客进入店内，最近的员工须向顾客打招呼说：“欢迎来到×××”。 当客户兑换超过200元的奖券时，柜员须要求查看客户的身份证并复印。当兑换的奖券金额小于25元时，无需客户签字。 早上第一个进办公室的人需要把饮水机加热按钮打开。
本系列我们关注数据库相关的业务规则，一些例子如下：
只有当客户产生第一个订单时才创建该客户的记录。 若一名学生没有选任何一门课程，把他的状态字段设为Inactive。 若销售员在一个月中卖出10套沙发，奖励500元。 一个联系人必须至少有1个电话号码和1个email邮箱。 若一个订单的除税总额超过1000元则能有5%的折扣。 若一个订单的除税总额超过500元则免运费。 员工购买本公司商品能有5%的折扣。 若仓库中某货品的存量低于上月卖出的总量时，则需要进货。
从数据库的视角来看，业务规则是一种约束。简单的约束如：
所有订单必须有一个联系电话。
上述这类简单的规则可以很容易的映射到关系数据库定义中，为字段确定数据类型或设定某字段为必填（不能为NULL）。某些业务规则表达的约束会复杂些，如：
学生每天的上课时间加上项目时间必须在1至14小时之间。
我们可以通过check约束或外键约束来实现这类业务规则。对于一些非常复杂的业务规则，如：
一名教员每周不能少于30小时工作量，其中分为办公时间、实验时间和上课时间。每1小时的课需要0.5小时办公时间进行备课。每1小时实验需1小时办公准备。每周指导学生论文时间不少于2小时。
类似上述的业务规则需要从多个表中收集数据，故在程序代码中实现最为合适。
识别关键业务规则
记录所有的业务规则并对这些规则进行分类能帮助我们更好的在系统中实现业务逻辑。
如何实现业务规则不仅与当前的业务逻辑有关，而且与该业务逻辑将来如何变化有关。当一个规则在将来很可能变化时，我们需要使用更复杂但更灵活的方式构建该规则。
举例来说，假设公司只能向当地设有仓库的城市发货，这些城市包括：南京、长沙、西安、广州。业务规则要求订单中的发货城市字段必须为NJ、CS、XA、GZ之一。
我们可以把该规则简单的实现为check约束。但将来公司若在上海有了一个新仓库，就必须从后台数据库端修改该check约束。若公司随后设立更多新仓库或业务规则变化为可以向没有仓库的城市发货，每次我们都需要修改该约束。
考虑另一种实现该业务规则的方法——使用外键。我们创建一张ShippingCities表，其中存放值：NJ、CS、XA、GZ，并让订单表中的发货城市字段外键引用ShippingCities表中的主键。这样订单的发货城市列只能接受ShippingCities中存在的城市。当支持的发货城市增加或减少时，只需要在ShippingCities中插入或删除记录。
两种方式的实现难度差异不大，但前一种方式每次都需要修改数据库结构，后一种只需要修改数据。修改数据不仅更省力而且技术要求也更低。
上述业务规则实现为check约束可能如下：
ShippingCity = ‘NJ’ or ShippingCity = ‘CS’ or ShippingCity = ‘XA’ or ShippingCity = ‘GZ’
上述代码并不复杂，但只有熟悉数据库的程序员从后台才能修改。ShippingCitis表中的数据相对更易于理解，我们可以提供一个界面来让用户自己维护其中的城市。
要识别关键业务规则，我们可以问自己两个问题。
第一、修改规则会有多困难。越是复杂的规则，修改起来越困难且更容易出错。
第二、规则变化的可能性有多大。变化频繁的规则需要额外的设计来更好的应对将来的变化。
需要特别注意的规则（关键业务规则）：
枚举值。例如：有效的发货城市，订单状态（Pending, Approved, Shipped）等。 计算参数。例如：对500元以上的订单免运费。这一数值可能在将来会调整为300元或600元。 有效参数。例如：项目组可由2至5人组成。某些项目是否可能由1个人完成或有更多人参与。 交叉记录和交叉表检查。例如：订单中可订购的货品数量不能超过该货品的当前库存数。 可概括性约束。如果可预见到将来需应用一些类似的约束，我们可以考虑把这些约束抽象出来进行管理。例如：某保险公司最近主推保险产品A。对每月能卖出20份A产品的销售人员给予1000元奖金。对于不同的保险产品在不同的时间段可能有不同的推广奖励规则。我们可以把产品名称、编号、销售量、奖金数额、促销时间段提取出来放到一张独立的表中作为计算奖金的参数。 非常复杂的检查。有些检查规则非常复杂，把这些规则放到程序代码中实现更为容易和清晰。例如：学生选择理学院的谓词演算课程的前提是已通过理学院的命题演算课程或已通过社科院的逻辑I和II课程或者需要导师的允许。该规则在某些数据库产品中可以通过表级的check约束实现，但放到程序中更易于维护和理解。
一些直接可以在数据库中实现的业务规则：
固定枚举值。例如：性别（男、女），用手习惯（左撇子、右撇子）。 数据类型要求。每个字段具有确定的数据类型是关系型数据库的重要特性之一。滥用通用的数据类型（如string）对性能和数据防错都会带来损害。 必填值。例如：会员必须有手机联系方式。 合理性检查。合理性检查设定的范围基本不会变化。例如：商品的价格大于等于0。
作为软件从业人员不要拒绝或回避变化。世界上唯一不变的就是变化。在收集业务规则时多去了解该规则的业务背景与历史变化历程，而不是逼迫客户保证规则不会变化。尽可能发现所有的业务规则并记录下来。对这些业务规则按变化的可能性和修改难度进行分类，精心设计那些将来可能变化且修改困难的规则。
提取关键业务规则
识别并分类业务规则之后，我们需要在数据库中或数据库外来实现关键业务规则。我们可以参考如下方法：
1. 若规则为检验一组有效值时，把该规则转化为外键约束。先前举例中的有效发货城市就是一个很好的例子。创建ShippingCities表，填入允许的发货城市。然后把Orders表的ShippingCity列设为外键，引用ShippingCities表的主键。
2. 若规则为参数可能变化的计算式时，把这些参数提取到一张表中。例如：一个月内卖出总价超过100万元汽车的销售员能获得500元奖金。把参数100万元和500元提取到一张表中，如果需要甚至可以把一个月的时间段也作为参数提取出来。
我还见过一些软件系统在数据库中有一张通用的参数表。该通用参数表中存放系统需要的各种参数，一些是用于计算、一些是作为检验、另一些决定系统的行为。每一条记录有两个字段：Name和Value。例如需要确定一名销售员能获得多少奖金，我们先要查找Name字段为BonusSales的记录，检查该销售员的销售额是否达到了Value字段的金额，若答案是肯定的再查找Name字段为BonusAward的记录来确定奖金数额。这种设计另有一好处，在程序启动时可以把通用参数表读入内存的某集合中，此后使用参数值时就无需再次连接数据库。
3. 若逻辑或计算规则很复杂时，则提取到代码中进行实现。这里说的代码可以是应用程序端代码，还可以是数据库端存储过程。把规则放到代码中实现的意义在于业务规则与数据库表结构分离了，规则的变化不会影响到数据库表结构。通过结构化编程或面向对象编程来实现复杂的规则更易于维护。
举一个综合性的例子：
一本关于数据库设计的书籍卖出前5000本的版税为5%，5000本至10000本之间的版税为7%，超过10000本后的版税为10%，不同类型书籍的版税可能不同。
上述规则比较复杂且包含多个可能变化的参数，故使用第1、2条方法。我们可以通过存储过程来实现该规则，并把参数隔离到一张参数表中进行维护。创建的参数表为RoyaltyRates，并通过BookId与Books关联（如图1所示）。这样为不同书籍创建新的版税规则就非常容易了。
图1  参数表RoyaltyRates与Books表的关系
多层应用的概念大家都不会陌生。三层应用是最常见的分层方法。对于复杂的业务逻辑一般会在中间层（即业务层）中实现。对于一些基本的验证，如必填信息、数字有效区间等，需要在最上层用户界面以及最底层数据库端进行双重检验。数据库端的约束是阻隔脏数据进入系统的最后一道防线，而用户界面处的检验可以避免错误数据传输到系统后端才被拒绝，节省了系统资源。
注：关于多层应用的更多资料请参见最后的“总结与参考”部分。

主要内容回顾
1. 业务规则决定了业务如何运行，其涵盖从简单明了的入门打卡到复杂的奖金计算公式。
2. 对于数据库而言，业务规则将影响到数据模型。业务规则确定了每个字段的域（值的类型和范围），是否是必须的，以及该字段要满足的其他条件。
3. 理解业务规则并识别那些需要特别处理的关键规则至关重要。
4. 有些规则简单且基本不变，它们可以很容易的用数据库特性来实现。其他的一些规则可能复杂或时常变化，我们可以把它们从数据库中逻辑的或物理的隔离出来（隔离到参数表、存储过程或业务层中），使它们易于修改。
多层应用参考
1. 谈谈对于企业级系统架构的理解（http://www.cnblogs.com/liping13599168/archive/2011/05/11/2043127.html）
2. Multitier architecture（http://en.wikipedia.org/wiki/Multitier_architecture）
3. Software Architecture, Architects and Architecting（http://www.bredemeyer.com/）
数据库设计Step by Step (7)——概念数据建模
2011-06-26 11:29 by DBFocus, 1628 visits,收藏,编辑
引言：在前两篇博文（数据库设计 Step by Step (5)和数据库设计 Step by Step (6) —— 提取业务规则）中，我们进行了数据库需求分析，着重讨论了两个主题：1.理解用户需求；2.提取业务规则。当需求分析完成后，我们就要进入到概念数据建模环节。本篇文章将使用之前介绍过的“基本实体关系模型构件”和“高级实体关系模型构件”作为建模的基本元素，大家可以回顾数据库设计 Step by Step (3)和数据库设计 Step by Step (4)中的模型构件及语义。
逻辑数据库设计有多种实现方式，包括：自顶至底，自底至顶以及混合方式。传统数据库设计是一个自底至顶的过程，从分析需求中的单个数据元素开始，把相关多个数据元素组合在一起转化为数据库中的表。这种方式较难应对复杂的大型数据库设计，这就需要结合自顶至底的设计方式。
使用ER模型进行概念数据建模方便了项目团队内部及与最终用户之间的交流与沟通。ER建模的高效性还体现在它是一种自顶至底的设计方法。一个数据库中的实体数量比数据元素少很多，因为大部分数据元素表示的是属性。辨别实体并关注实体之间的关系能大大减少需要分析的对象数量。
概念数据建模连接了两端，一端是需求分析，其能辅助捕获需求中的实体及之间的关系，便于人们的交流。另一端是关系型数据库，模型可以很容易的转化为范式化或接近范式化的SQL表。

概念数据建模步骤
让我们进一步仔细观察应在需求分析和概念设计阶段定义的基本数据元素和关系。一般需求分析与概念设计是同步完成的。
使用ER模型进行概念设计的步骤包括：
辨识实体与属性 识别泛化层次结构 定义关系
下面我们对这三个步骤一一进行讨论。
辨识实体与属性
实体和属性的概念及ER构图都很简单，但要在需求中区分实体和属性不是一件易事。例如：需求描述中有句话，“项目地址位于某个城市”。这句话中的城市是一个实体还是一个属性呢？又如：每一名员工有一份简历。这里的简历是一个实体还是一个属性呢？
辨别实体与属性可参考如下准则：
实体应包含描述性信息 多值属性应作为实体来处理 属性应附着在其直接描述的实体上
这些准则能引导开发人员得到符合范式的关系数据库设计。
如何理解上述的三条准则呢？
实体内容：实体应包含描述信息。如果一个数据元素有描述型信息，该数据元素应被识别为实体。如果一个数据元素只有一个标识名，则其应被识别为属性。以前面的“城市”为例，如果对于“城市”有一些如所属国家、人口等描述信息，则“城市”应被识别为一个实体。如果需求中的“城市”只表示一个城市名，则把“城市”作为属性附属与其他实体，如附属Project实体。这一准则的例外是当值的标识是可枚举的有限集时，应作为实体来处理。例如把系统中有效的国家集合定义为实体。在现实世界中作为实体看待的数据元素有：Employee，Task，Project，Department，Customer等。
多值属性：把多值属性作为实体。如果一个实例的某个描述符包含多个对应值，则即使该描述符没有自己的描述信息也应作为实体进行建模。例如：一个人会有许多爱好，如：看电影、打游戏、大篮球等。爱好对于一个人来说就是多值属性，则爱好应作为实体来看待。
属性依附：把属性附加在其最直接描述的实体上。例如：“office-building-name”作为“Department”属性比作为“Employee”的属性合适。识别实体与属性，并把属性附加到实体中是一个循环迭代的过程。
识别泛化层次
如果实体之间有泛化层次关系，则把标识符和公共的描述符（属性）放在超类实体中，把相同的标识符和特有的描述符放在子类实体中。举例来说，在ER模型中有5个实体，分别是Employee、Manager、Engineer、Technician、Secretary。其中Employee可以作为Manager、Engineer、Technician、Secretary的超类实体。我们可以把标识符empno，公共描述符empname、address、date-of-birth放在超类实体中。子类实体Manager中放empno，特有描述符jobtitle。Engineer实体中放empno，特有描述符jobtitle，highest-degree等。
定义关系
在识别实体和属性之后我们可以处理代表实体之间联系的数据元素即关系。关系在需求描述中一般是一些动词如：works-in、works-for、purchases、drives，这些动词联系了不同的实体。
对于任何关系，需要明确以下几个方面。
关系的度（二元、三元等）； 关系的连通数（一对一、一对多等）； 关系是强制的还是可选的； 关系本身有些什么属性。
注：关系的这些概念可参看数据库设计 Step by Step (3)，这里不再赘述。
冗余关系
仔细分析冗余的关系。描述同一概念的两个或多个关系被认为是冗余的。当把ER模型转化为关系数据库中的表时，冗余的关系可能造成非范式化的表。需要注意的是两个实体间允许两个或更多关系的存在，只要这些关系具有不同的含义。在这种情况下这些关系不是冗余的。
举例来说，如下图1中Employee生活的City与该Employee所属的Professional-association的所在City可以不同（两种含义），故关系lives-in非冗余。
（图1  非冗余关系）
如下图2中的Employee工作的City与该Employee参与的Project的所在City在任何情况下都一致（同种含义），故关系works-in冗余。
（图2  传递性冗余关系）
三元关系
非常小心的定义三元关系，只有当使用多个二元关系也无法充分描述多个实体间的语义时，我们才会定义三元关系。以Technician、Project、Notebook为例。
例1：如果 一个Technician只做一个Project，一个Project只有一个Technician，每个Project会被独立记录在一本Notebook中。
（图3 例1二元关系图）
例2：如果一个Technician能同时做多个Project，一个Project可以有多个Technician同时参与，每个Project有一本Notebook（多个做同一个Project的Technician共用一本Notebook）。
（图4 例2二元关系图）
例3：如果一个Technician能同时做多个Project，一个Project可以有多个Technician同时参与，一个Technician在一个Project中使用独立的一本Notebook。
（图5 例3三元关系图）
注：三元关系的语义分析可参看数据库设计 Step by Step (4)，这里不再赘述。

我们假设要为一家工程项目公司设计一个数据库来跟踪所有的全职员工，包括员工被分配的项目，所拥有的技能，所在的部门和事业部，所属于的专业协会，被分配的电脑。
单个视图的ER建模
通过需求收集与分析过程，我们获得了数据库的3个视图。
第一个视图是人力资源管理视图。每一个员工属于一个部门。事业部是公司的基本单元，每个事业部包含多个部门。每一个部门和事业部都有一个经理，我们需要跟踪每一个经理。这一视图的ER模型如图6所示。
（图6  人力资源关系视图）
第二个视图定义了每个员工的头衔，如工程师、技术员、秘书、经理等。工程师一般属于某个专业协会，并可能被分配一台工作站。秘书和经理会被分配台式电脑。公司会储备一些台式电脑和工作站，以分配给新员工或当员工的电脑送修时进行出借。员工之间可能有夫妻关系，这也需要在系统中进行跟踪，以防止夫妻员工之间有直接领导关系。这一视图的ER模型如图7所示。
（图7  员工头衔及电脑分配视图）
第三个视图如图8所示，包含员工（工程师、技术员）分配项目的信息。员工可以同时参与多个项目，每一个项目可以在不同的地方（城市）设有总部。但一个员工在指定的地点只能做当地的一个项目。员工在不同的项目中可以选用不同的技能。
（图8  项目分配及技能使用视图）
全局ER图
对三个视图的简单集成可得到全局ER图，如图9所示，它是构造范式化表的基础。全局ER图中的每一个关系都是基于企业中实际数据的一个可验证断言。对这些断言进行分析导出了从ER图到关系数据库表的转化。
（图9  全局ER图）
从全局ER图中可以看到二元、三元和二元回归关系；可选和强制存在性关系；泛化的分解约束。图9中三元关系“skill-used”和“assigned-to”是必须的，因为使用二元关系无法描述相同的语义。
可选存在性的使用，Employee与Division或与Department之间是基于常识：大多数Employee不会是Division或Department的经理。另一个可选存在性的例子是desktop或workstation的分配，每一台desktop或workstation未必都会分配给一个人。总而言之，在把ER模型转化为SQL表之前，所有的关系、可选约束、泛化层次都需要与系统的最终用户进行确认。

总结来说，在关系数据库设计中应用ER模型会带来如下好处
1. 使用ER模型可帮助项目成员专注在讨论实体之间的重要关系上，而不受其他细节的干扰。
2. ER模型把大量复杂的语言描述转化为精简的、易理解的图形化描述。
3. 对原始ER模型的扩展，如可选和强制存在性关系，泛化关系等加强了ER模型对现实语义的描述能力。
4. 从ER模型转化为SQL表有完整的规则，且易于使用。
实体关系（ER）模型参考资料
1. 基本实体关系模型构件——实体、关系、属性、关系的度、关系的连通数、关系的属性、关系中实体的存在性（http://www.cnblogs.com/DBFocus/archive/2011/04/24/2026142.html）
2. 高级实体关系模型构件——泛化、聚合、三元关系（http://www.cnblogs.com/DBFocus/archive/2011/05/07/2039674.html）
数据库设计 Step by Step (8)——视图集成
2011-07-09 16:05 by DBFocus, 1561 visits,收藏,编辑
引言：在前文（数据库设计Step by Step (7)——概念数据建模）最后的案例中，我们通过集成多个局部的实体关系（ER）模型最终得到了全局ER图。在现实项目中视图集成可能并不会那么容易。
俯瞰整个数据库生命周期（如下图所示）。在前面的内容中，我们已完成了“确定需求”和“数据模型”（图中以灰色标出），本小节我们将详细讨论“视图集成”（图中以红色标出）

把基于不同用户视角的局部ER图集成为一个统一的、没有冗余的全局ER图在数据库设计流程中非常重要。单个局部ER图是通过分析用户需求进行概念数据建模得到的；全局ER图是通过对各个局部ER图进行分析，解决其中存在的视角和术语差异，最终进行组合得到的。

为什么会产生不一致的局部ER图
当不同的用户或用户组从各自的视角来看业务时就会产生各异的ER图。举例来说市场部趋向于把整个产品作为销售的基本单元，但工程部可能更关注组成产品的单个零件。另一个例子，一个用户可能关注项目的目标和产生的价值，而另一个用户则关心项目需要占用的资源和所涉及的人员。上述的这些差异造成了各个ER图之间不一致的关系和术语。ER图的不一致性会表现为：不同的泛化程度；不同的关系连通数（一对多、多对多等）；不同用户视角定义的实体、属性或关系（相同的概念，不同的人使用了不同的名称与建模形式）。
举例来说，同一个现实场景（客户下订单，订购产品），从三个不同视角建模得到的ER图如下。
（图1  把order看作实体）
（图2  把order看作关系）
（图3  把order看作属性）
图1中，Customer、Order、Product描述为实体，把“places”和“for-a”描述为关系。
图2中，“orders”定义为Customer和Product之间的关系。
图3中，“orders”关系被另一个关系“purchases”代替。“order-no”被作为关系“purchases”的一个属性。
同是订单（order），从不同视角出发在ER图中被表示为实体、关系、属性。
视图集成的步骤
局部ER图（概念数据模型）的集成一般有如下四个步骤。
集成策略选择 比较实体关系图 统一实体关系元素 合并、重构实体关系图
我们一一对这四个步骤进行讨论。
集成策略选择
通常的集成策略有：
1.每次集成2个局部ER图。
2.每次集成n个局部ER图（n大于2且小于等于总ER图数）。
相对来说第一种集成策略每次所涉及的实体、关系数量较少，也更容易掌控。
比较实体关系图
设计者需要仔细观察不同ER图中的对应实体，发现其中因视角不同而存在的冲突。
命名上的冲突包括“同物异名”和“异物同名”。“同物异名”是指同一个概念使用了不同的名称，可以通过检视数据字典（命名及其描述对应表）来发现。“异物同名”是指对不同的概念使用了相同的名称，需要通过检视不同ER图中相同的名称来发现。
结构性冲突的表现形式更多。类型冲突包括使用不同的构造方式建模同一概念。以图1、2、3为例，order这一概念可以建模为一个实体，一个关系或一个属性。依赖冲突是指类似或相同的关系在不同的局部ER图中被建模成不同的连通数。解决这种冲突的一种方法是使用最一般的连通数约束，如多对多。若这样做会造成语义上的错误，则说明两种关系概念不同不能合并，应进行改名并让每个关系保持各自的连通数。键冲突是指在不同的局部ER图中，同一概念的实体被分配了不同的键。举例来说，当一名员工的全名、员工号、员工身份证号在不同的局部ER图中被作为员工的键时，就出现了键冲突。
统一实体关系元素
基本目标是解决各局部ER图中的冲突，使这些元素一致化，为最终的ER图集成做准备。要解决各局部ER图之间的冲突通常需要设计开发人员与用户进行积极的沟通，了解、分析、理解冲突元素的真实语义。
我们可能需要对某些ER图中的实体及键属性进行改名。各局部ER图中被建模为实体、关系或属性的同一概念需要统一转化为三种形式之一。
集成具有相同的度、角色和连通数属性的关系相对较为容易，但集成上述特征不同的关系就较为困难。若同一关系在不同局部ER图中表现形式不一致，则必须进行统一。如：某一关系在一局部ER图中为泛化层次关系，在另一局部ER图中为排他性或（exclusive OR）关系，这种情况必须统一。
合并、重构实体关系图
合成和重构局部ER图，最终得到完整、最简约和可理解的全局ER图。
完整是要求在全局ER图中所有组件的语义完整。
最简约是要求去除全局ER图中的冗余。冗余的概念包括：重叠的实体、多余的语义关系等。例如“社会车辆”和“私家车”可能是重叠的两个实体；教授与学生之间的“指导”和“建议”关系可能代表了同一种活动，需要进一步确定是否存在冗余。
可理解要求全局ER图能被整个项目组成员和最终用户理解。
在进行ER图集成过程中，我们可以首先将相同概念的组件进行集成，之后对获得的初步全局ER图进行重构以使其满足上述三方面的要求。举例来说，集成后的ER图包含超类实体与子类实体的层次组合，若超类实体中的属性已涵盖子类实体中的某些属性，则子类实体的这些属性可以去除。

了解目标
让我们看一下两张具有重叠数据的局部ER图。这两张ER图是对两组不同用户访谈后画出的。
图4是一张以报表为关注点的ER图，其中包含发布报表的部门、报表中的主题和报表提交的对象。
（图4  关注报表）
图5的ER图以发布作为关注中心，把发布内容中的关键词建模为另一个实体。
（图5  关注发布）
我们的目标是整合这两张ER图，并保证合成后的ER图语义完整、形式最简约且易理解。
集成步骤
首先，在两张局部ER图中寻找是否存在“同物异名”与“异物同名”现象。图4中的实体Topic-area与图5中的实体Keyword为“同物异名”，虽然两个实体的属性不完全相同，但两者属性是兼容的，可以进行统一化。对图5进行修改，可得到图6。
（图6  Keyword换为Topic_area）
其次，再来看两张ER图之间的结构性冲突。图4中的实体Department与图5中的属性dept-name为类型冲突。解决该冲突的方法是保留强类型（实体Department），把属性dept-name移至实体Department中。解决该冲突，把ER图6转化为ER图7。
（图7  属性dept-name转化为一个实体和一个属性）
比较变化后的各局部ER图，寻找之间的“共同之处”进行合并。 在真正合并之前必须确认这些“共同之处”的语义概念完全等同，这也保证了合并后语义的完整性。在ER图4与ER图7中有两个共同实体：Department和Topic-area，且语义一致。初步合并后的全局ER图如图8所示。
（图8  初步合并图4和图7后的全局ER图）
图8中实体Publication和Report与实体Department和Topic-area之间的关系存在冗余。通过与用户的再次确认，了解到Publication是Report的泛化（报表只是发布材料中的一种），故不能简单的去除实体Publication及关系have和include来消除冗余，而可以引入泛化关系并去除冗余关系publish和contain。
图9展示了增加泛化关系后的ER图（Publication为超类型，Report为子类型）。
（图9  加入泛化关系）
图10中实体Report与实体Department和Topic-area之间的冗余关系publish和contain被去除了。Report中的属性title也被去除了，因为该属性已经出现在其超类型实体Publication中了。
（图10  去除冗余关系）
最终得到的ER图10达到了语义完整、最简约、易理解的目标。ER图集成是一个持续优化和评估的过程。需要注意的是“最简约”未必会最高效。如ER图10中去除的“publish”和“contain”关系，保留它们可能对性能有帮助。在后期的分析或测试过程中可根据需要重构ER图。

1. 不同的用户或用户组视角将产生不同的局部ER图
2. 局部ER图之间的冲突包括：命名冲突、类型冲突、依赖冲突、键冲突
3. 视图集成的目标是得到语义完整、形式简约且易于理解的全局ER图
4. 视图集成能进一步加强项目组对系统整体需求的理解与把握
数据库设计Step by Step (9)——ER-to-SQL转化
2011-07-25 21:06 by DBFocus, 1495 visits,收藏,编辑
引言：前文（数据库设计 Step by Step (8)——视图集成）讨论了如何把局部ER图集成为全局ER图。有了全局ER图后，我们就可以把ER图转化为关系数据库中的SQL表了。俯瞰整个数据库生命周期（如下图所示），找到我们的“坐标”。

把ER图转化为关系数据库中的表结构是一个非常自然的过程。许多ER建模工具除了辅助绘制ER图外，还能自动地把ER图转化为SQL表。

从ER模型到SQL表
从ER图转化得到关系数据库中的SQL表，一般可分为3类。
1. 转化得到的SQL表与原始实体包含相同信息内容。该类转化一般适用于：
二元“多对多”关系中，任何一端的实体
二元“一对多”关系中，“一”一端的实体
二元“一对一”关系中，某一端的实体
二元“多对多”回归关系中，任何一端的实体（注：关系两端都指向同一个实体）
三元或n元关系中，任何一端的实体
层次泛化关系中，超类实体
2. 转化得到的SQL表除了包含原始实体的信息内容之外，还包含原始实体父实体的外键。该类转化一般适用于：
二元“一对多”关系中，“多”一端的实体
二元“一对一”关系中，某一端的实体
二元“一对一”或“一对多”回归关系中，任何一端的实体
该转化是处理关系的常用方法之一，即在子表中增加指向父表中主键的外键信息。
3. 由“关系”转化得到的SQL表，该表包含“关系”所涉及的所有实体的外键，以及该“关系”自身的属性信息。该类转化一般适用于：
二元“多对多”关系
二元“多对多”回归关系
三元或n元关系
该转化是另一种常用的关系处理方法。对于“多对多”关系需要定义为一张包含两个相关实体主键的独立表，该表还能包含关系的属性信息。
转化过程中对于NULL值的处理规则
1. 当实体之间的关系是可选的，SQL表中的外键列允许为NULL。
2. 当实体之间的关系是强制的，SQL表中的外键列不允许为NULL。
3. 由“多对多”关系转化得到的SQL表，其中的任意外键列都不允许为NULL。
一般二元关系的转化
1. “一对一”，两实体都为强制存在
当两个实体都是强制存在的（如图1所示），每一个实体都对应转化为一张SQL表，并选择两个实体中任意一个作为主表，把它的主键放入另一个实体对应的SQL表中作为外键，该表称为从表。
（图1  “一对一”，两实体都为强制存在）
图1表示的语义为：每一张报表都有一个缩写，每一缩写只代表一张报表。转化得到的SQL表定义如下：
create table report(report_no integer,report_name varchar(256),primary key(report_no));create table abbreviation(abbr_no char(6),report_no integer not null unique,primary key(abbr_no),foreign key(report_no) references reporton delete cascade on update cascade);
注：本节中所有SQL代码在SQL Server 2008环境中测试通过。
2. “一对一”，一实体可选存在，另一实体强制存在
当两个实体中有一个为“可选的”，则“可选的”实体对应的SQL表一般作为从表，包含指向另一实体的外键（如图2所示）。
（图2  “一对一”，一实体可选存在，另一实体强制存在）
图2表示的语义为：每一个部门必须有一位经理，大部分员工不是经理，一名员工最多只能是一个部门的经理。转化得到的SQL表定义如下：
create table employee(emp_id char(10),emp_name char(20),primary key(emp_id));create table department(dept_no integer,dept_name char(20),mgr_id char(10) not null unique,primary key(dept_no),foreign key(mgr_id) references employeeon update cascade);
另一种转化方式是把“可选的”实体作为主表，让“强制存在的”实体作为从表，包含外键指向“可选的”实体，这种方式外键列允许为NULL。以图2为例，可把实体Employee转化为从表，包含外键列dept_no指向实体Department，该外键列将允许为NULL。因为Employee的数量远大于Department的数量，故会占用更多的存储空间。
3. “一对一”，两实体都为可选存在
当两个实体都是可选的（如图3所示），可选任意一个实体包含外键指向另一实体，外键列允许为NULL值。
（图3  “一对一”，两实体都为可选存在）
图3表示的语义为：部分台式电脑被分配给部分工程师，一台电脑只能分配给一名工程师，一名工程师最多只能分配到一台电脑。转化得到的SQL表定义如下：
create table engineer(emp_id char(10),emp_name char(20),primary key(emp_id));create table desktop(desktop_no integer,emp_id char(10),primary key(desktop_no),foreign key(emp_id) references engineeron delete set null on update cascade);
4. “一对多”，两实体都为强制存在
在“一对多”关系中，无论“多”端是强制存在的还是可选存在的都不会影响其转化形式，外键必须出现在“多”端，即“多”端转化为从表。当“一”端实体是可选存在时，“多”端实体表中的外键列允许为NULL。
（图4  “一对多”，两实体都为强制存在）
图4表示的语义为：每名员工都属于一个部门，每个部门至少有一名员工。转化得到的SQL表定义如下：
create table department(dept_no integer,dept_name char(20),primary key(dept_no));create table employee(emp_id char(10),emp_name char(20),dept_no integer not null,primary key(emp_id),foreign key(dept_no) references departmenton update cascade);
5. “一对多”，一实体可选存在，另一实体强制存在
（图5  “一对多”，一实体可选存在，另一实体强制存在）
图5表示的语义为：每个部门至少发布一张报表，一张报表不一定由某个部门来发布。转化得到的SQL表定义如下：
create table department(dept_no integer,dept_name char(20),primary key(dept_no));create table report(report_no integer,dept_no integer,primary key(report_no),foreign key(dept_no) references departmenton delete set null on update cascade);
注：解释一下report表创建脚本的最后一行“on delete set null on update cascade”的用处。当没有这一行时，更新department表中dept_no字段会失败，删除department中记录也会失败，报出与外键约束冲突的提示。如果有了最后一行，更新department表中dept_no字段，report表中对应记录的dept_no也会同步更改，删除department中记录，会使report表中对应记录的dept_no值变为NULL。
6. “多对多”，两实体都为可选存在
在“多对多”关系中，需要一张新关系表包含两个实体的主键。无论两边实体是否为可选存在的，其转化形式一致，关系表中的外键列不能为NULL。实体可选存在，在关系表中表现为是否存在对应记录，而与外键是否允许NULL值无关。
（图6  “多对多”，两实体都为可选存在）
图6表示的语义为：一名工程师可能是专业协会的会员且可参加多个专业协会。每一个专业协会可能有多位工程师参加。转化得到的SQL表定义如下：
create table engineer(emp_id char(10),primary key(emp_id));create table prof_assoc(assoc_name varchar(256),primary key(assoc_name));create table belongs_to(emp_id char(10),assoc_name varchar(256),primary key(emp_id, assoc_name),foreign key(emp_id) references engineeron delete cascade on update cascade,foreign key(assoc_name) references prof_assocon delete cascade on update cascade);
二元回归关系的转化
对于“一对一”或“一对多”回归关系的转化都是在SQL表中增加一列与主键列类型、长度相同的外键列指向实体本身。外键列的命名需与主键列不同，表明其用意。外键列的约束根据语义进行确定。
7. “一对一”，两实体都为可选存在
（图7  “一对一”，两实体都为可选存在）
图7表示的语义为：公司员工之间可能存在夫妻关系。转化得到的SQL表定义如下：
create table employee(emp_id char(10),emp_name char(20),spouse_id char(10),primary key(emp_id),foreign key(spouse_id) references employee);
8. “一对多”，“一”端为强制存在，“多”端为可选存在
（图8  “一对多”，“一”端为强制存在，“多”端为可选存在）
图8表示的语义为：工程师被分为多个组，每个组有一名组长。转化得到的SQL表定义如下：
create table engineer(emp_id char(10),leader_id char(10) not null,primary key(emp_id),foreign key(leader_id) references engineer);
“多对多”回归关系无论是可选存在的还是强制存在的都需新增一张关系表，表中的外键列须为NOT NULL。
9. “多对多”，两端都为可选存在
（图9  “多对多”，两端都为可选存在）
图9表示的语义为：社交网站中人之间的朋友关系，每个人都可能有很多朋友。转化得到的SQL表定义如下：
create table person(person_id char(10),person_name char(20),primary key(person_id));create table friend(person_id char(10),friend_id char(10),primary key(person_id, friend_id),foreign key(person_id) references person,foreign key(friend_id) references person,check(person_id < friend_id));
三元和n元关系的转化
无论哪种形式的三元关系在转化时都会创建一张关系表包含所有实体的主键。三元关系中，“一”端实体的个数决定了函数依赖的数量。因此，“一对一对一”关系有三个函数依赖式，“一对一对多”关系有两个函数依赖式，“一对多对多”关系有一个函数依赖式。“多对多对多”关系的主键为所有外键的联合。
10. “一对一对一”三元关系
（图10  “一对一对一”三元关系）
图10表示的语义为：
1名技术员在1个项目中使用特定的1本记事簿
1本记事簿在1个项目中只属于1名技术员
1名技术员的1本记事簿只用于记录1个项目
注：1名技术员仍可以做多个项目，对于不同的项目维护不同的记事簿。
转化得到的SQL表定义如下：
create table technician(emp_id char(10),primary key(emp_id));create table project(project_name char(20),primary key(project_name));create table notebook(notebook_no integer,primary key(notebook_no));create table uses_notebook(emp_id char(10),project_name char(20),notebook_no integer not null,primary key(emp_id, project_name),foreign key(emp_id) references technicianon delete cascade on update cascade,foreign key(project_name) references projecton delete cascade on update cascade,foreign key(notebook_no) references notebookon delete cascade on update cascade,unique(emp_id, notebook_no),unique(project_name, notebook_no));
函数依赖
emp_id, project_name -> notebook_no
emp_id, notebook_no -> project_name
project_name, notebook_no -> emp_id
11. “一对一对多”三元关系
（图11  “一对一对多”三元关系）
图11表示的语义为：
参与1个项目的1名员工只会在1个地点做该项目
1名员工在1个地点只能做1个项目
1个地点的1个项目可能有多名员工参与
注：1名员工可以在不同的地点做不同的项目
转化得到的SQL表定义如下：
create table employee(emp_id char(10),emp_name char(20),primary key(emp_id));create table project(project_name char(20),primary key(project_name));create table location(loc_name char(15),primary key(loc_name));create table assigned_to(emp_id char(10),project_name char(20),loc_name char(15) not null,primary key(emp_id, project_name),foreign key(emp_id) references employeeon delete cascade on update cascade,foreign key(project_name) references projecton delete cascade on update cascade,foreign key(loc_name) references locationon delete cascade on update cascade,unique(emp_id, loc_name));
函数依赖：
emp_id, loc_name -> project_name
emp_id, project_name -> loc_name
12. “一对多对多”三元关系
（图12  “一对多对多”三元关系）
图12表示的语义为：
1个项目中的1名工程师只会有1名经理
1个项目中的1名经理会带领多名工程师做该项目
1名经理和他手下的1名工程师可能参与多个项目
转化得到的SQL表定义如下：
create table project(project_name char(20),primary key(project_name));create table manager(mgr_id char(10),primary key(mgr_id));create table engineer(emp_id char(10),primary key(emp_id));create table manages(project_name char(20),mgr_id char(10) not null,emp_id char(10),primary key(project_name, emp_id),foreign key(project_name) references projecton delete cascade on update cascade,foreign key(mgr_id) references manageron delete cascade on update cascade,foreign key(emp_id) references engineeron delete cascade on update cascade);
函数依赖：
project_name, emp_id -> mgr_id
13. “多对多对多”三元关系
（图13 “多对多对多”三元关系）
图13表示的语义为：
1名员工在1个项目中可以运用多种技能
1名员工的1项技能可以在多个项目中运用
1个项目中的1项技能可以被参与该项目的多名员工运用
转化得到的SQL表定义如下：
create table employee(emp_id char(10),emp_name char(20),primary key(emp_id));create table skill(skill_type char(15),primary key(skill_type));create table project(project_name char(20),primary key(project_name));create table sill_used(emp_id char(10),skill_type char(15),project_name char(20),primary key(emp_id, skill_type, project_name),foreign key(emp_id) references employeeon delete cascade on update cascade,foreign key(skill_type) references skillon delete cascade on update cascade,foreign key(project_name) references projecton delete cascade on update cascade);
函数依赖：
无
泛化与聚合
泛化抽象结构中的超类实体和各子类实体分别转化为对应的SQL表。超类实体转化得到的表包含超类实体的键和所有公共属性。子类实体转化得到的表包含超类实体的键和子类实体特有的属性。
要保证泛化层次中数据的完整性就必须保证某些操作在超类表和子类表的之间的同步。若超类表的主键需做更新，则子类表中对应记录的外键必须一起更新。若需删除超类表中的记录，子类表中对应记录也需一起删除。我们可以在定义子类表时加入外键级联约束。这一规则对于覆盖与非覆盖的子类泛化都适用。
14. 泛化层次关系
（图14  泛化层次关系）
图14表示的语义为：
个人可能是一名员工，或是一位顾客，或同时是员工与顾客，或两者都不是
转化得到的SQL表定义如下：
create table individual(indiv_id char(10),indiv_name char(20),indiv_addr char(20),primary key(indiv_id));create table employee(emp_id char(10),job_title char(15),primary key(emp_id),foreign key(emp_id) references individualon delete cascade on update cascade);create table customer(cust_no char(10),cust_credit char(12),primary key(cust_no),foreign key(cust_no) references individualon delete cascade on update cascade);
有些数据库开发者还会在超类表中增加一个鉴别属性。鉴别属性对于每一种子类有不同的值，表示从哪一个子类中能获得进一步的信息。
聚合抽象的转化方式也是为超类实体和每一个子类实体生成SQL表，但聚合中的超类与子类没有公共属性和完整性约束。聚合的主要功能是提供一种抽象来辅助视图集成的过程。

转化步骤
以下总结了从ER图到SQL表的基本转化步骤
1. 把每一个实体转化为一张表，其中包含键和非键属性。
2. 把每一个“多对多”二元或二元回归关系转化为一张表，其中包含实体的键和关系的属性。
3. 把三元及更高元（n元）关系转化为一张表。
让我们一一对这三个步骤进行讨论。
实体转化
若两个实体之间是“一对多”关系，把“一”端实体的主键加入到“多”端实体表中作为外键。若两实体间是“一对一”关系，把某个“一”端实体的主键放入另一实体表中作为外键，加入外键的实体理论上可以任选，但一般会遵循如下原则：按照实体间最为自然的父子关系，把父实体的键放入子实体中；另一种策略是基于效率，把外键加入到具有较少行的表中。
把泛化层次中的每一个实体转化为一张表。每张表都会包含超类实体的键。事实上子类实体的主键同时也是外键。超类表中还包含所有相关实体的公共非键属性，其他表包含每一子类实体特有的非键属性。
转化得到的SQL表可能会包含not null, unique, foreign key等约束。每一张表必须有一个主键（primary key），主键隐含着not null和unique约束。
“多对多”二元关系转化
每一个“多对多”二元关系能转化为一张表，包含两个实体的键和关系的属性。
这一转化得到的SQL表可能包含not null约束。在这里没有使用unique约束的原因是关系表的主键是由各实体的外键复合组成的，unique约束已隐含。
三元关系转化
每一个三元（或n元）关系转化为一张表，包含相关实体的n个主键以及该关系的属性。
这一转化得到的表必须包含not null约束。关系表的主键由各实体的外键复合组成。n元关系表具有n个外键。除主键约束外，其他候选键(candidate key)也应加上unique约束。
ER-to-SQL转化步骤示例
把数据库设计Step by Step (7)——概念数据建模中最后得到的公司人事和项目数据库的全局ER图（图9）转化为SQL表。
1. 直接由实体生成的SQL表有：
Division          Department          Employee          Manager          Secretary          Engineer
Technician     Skill                          Project                Location           Prof_assoc       Desktop
Workstation
2. 由“多对多”二元关系及“多对多”二元回归关系生成的SQL表有：
belongs_to
3. 由三元关系生成的SQL表有：
skill_used     assigned_to

总结与回顾
1. 通过一些简单的规则就能把ER模型中的实体、属性和关系转化为SQL表。
2. 实体在转化为表的过程中，其中的属性一一被映射为表的属性。
3. “一对一”或“一对多”关系中的“子”端实体转化成的SQL表必须包含另一端实体的主键，作为外键。
4. “多对多”关系转化为一张表，包含相关实体的主键，复合组成其自身的主键。同时这些键在SQL中定义为外键分别指向各自的实体。
5. 三元或n元关系被转化为一张表，包含相关实体的主键。这些键在SQL中定义为外键。这些键中的子集定义为主键，其基于该关系的函数依赖。
6. 泛化层次的转化规则要求子类实体从超类实体继承主键。
7. ER图中的可选约束在转化为SQL时，表现为关系的某一端实体允许为null。在ER图中没有明确标识可选约束时，创建表时默认not null约束。
数据库设计中的五个范式
第一范式：
对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.
（第一范式是通过把重复的组放到每个独立的表中，把这些表通过一对多关联联系起来这种方式来消除重复组的。）
第二范式：
第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.
（第二范式处理冗余数据的删除问题。当某张表中的信息依赖于该表中其它的不是主键部分的列的时候，通常会违反第二范式。）
第三范式：
第三范式要求非主键列互不依赖.
（第三范式规则查找以消除没有直接依赖于第一范式和第二范式形成的表的主键的属性。我们为没有与表的主键关联的所有信息建立了一张新表。每张新表保存了来自源表的信息和它们所依赖的主键。）
第四范式：
第四范式禁止主键列和非主键列一对多关系不受约束
（）
第五范式：
第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余.
（）
在数据库设计时，大家应该时刻的注意到这几个范式。 其中第五范式是最难实现的。但是，还是需要尽量的去实现这些功能。
数据库设计的三大范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
在实际开发中最为常见的设计范式有三个：
1．第一范式
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。
用户信息表
编号
姓名
性别
年龄
联系电话
省份
城市
详细地址
1
张红欣
男
26
0378-23459876
河南
开封
朝阳区新华路23号
2
李四平
女
32
0751-65432584
广州
广东
白云区天明路148号
3
刘志国
男
21
0371-87659852
河南
郑州
二七区大学路198号
4
郭小明
女
27
0371-62556789
河南
郑州
新郑市薛店北街218号
上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。
2．第二范式
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。
订单信息表
订单编号
商品编号
商品名称
数量
单位
商品价格
001
1
挖掘机
1
台
1200000￥
002
2
冲击钻
8
个
230￥
003
3
铲车
2
辆
980000￥
这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，就非常完美了。如下面这两个所示。
订单信息表
订单编号
商品编号
数量
001
1
1
002
2
8
003
3
2
商品信息表
商品编号
商品名称
单位
商品价格
1
挖掘机
台
1200000￥
2
冲击钻
个
230￥
3
铲车
辆
980000￥
这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。
3．第三范式
第三范式在第二范式的基础上更进一层。第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
订单信息表
订单编号
订单项目
负责人
业务员
订单数量
客户编号
001
挖掘机
刘明
李东明
1台
1
002
冲击钻
李刚
霍新峰
8个
2
003
铲车
郭新一
艾美丽
2辆
1
客户信息表
客户编号
客户名称
所属公司
联系方式
1
李聪
五一建设
13253661015
2
刘新明
个体经营
13285746958
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。
数据库设计之“有时不得不违背的第三范式”
Posted on 2005-05-08 14:13听棠.NET 阅读(10230)评论(66)编辑收藏
在博客园上看到了一篇关于数据库范式的文章《数据库设计中的五个范式》：
第三范式规则查找以消除没有直接依赖于第一范式和第二范式形成的表的主键的属性。我们为没有与表的主键关联的所有信息建立了一张新表。每张新表保存了来自源表的信息和它们所依赖的主键。
关于第三范式的思想，我想有很多朋友都熟悉，在数据库设计时，也是我们尽可能采用的范式之一，第三范式的出发点是什么？就是尽可能的减小“数据冗余”、并也能得到“数据”的整洁性，提高维护性，不容怀疑，第三范式是我们努力、必须要去遵从的。
然而，有很多朋友把第三范式作为“不死的法宝”，但其实在实际的应用中，我们还是要从不同的业务出发，要合理的应用“第三范式”。下面我也就简单的举个例子：
一张订单会关联很多的基础资料，如：客户，付款条款，货运方式等，这些信息是有专门表进行维护的，在下订单时也是用下拉框选择的，在保存订单信息时,按照“第三范式”的要求，那应该只保存对应的主键值就OK了。因为这样可以避免数据冗余，但对我来说，我不会这样做，我会把客户的名称、联系电话、付款条款名称等订单上要求记录的信息直接COPY到订单的表中。
这样看来，我们违背了“第三范式”，是的，但在这里，我们违背“第三范式”也是有理由的：
1我不想在订单下达完以后，删除了某条付款条款，导致这些订单无法知道“真实的付款条款”了，这肯定不合理。
2我也不想，因为下了这张订单了，而“严格控制”付款条款的“删除”功能，这也不合理，凭啥不能删除了？下个月这个“条款”确实永远不会采用了。
3我也不想，付款条款修改后，导致以前所有采用此付款条款的订单都变成新的条款，那在系统中的订单如何与手头的纸张订单再对应，这肯定也不合理。
所以，我的设计原则是，对于这种订单我们应该采用“隔离”的方式来对待，让基础数据COPY到订单中，这当然会违背所谓的“第三范式”，但这也是实际的需要啊。理论与实际是有差别的。
订单--这种在现实中以实物的方式存在，实物具有与基础数据的参考性，而不是关联性，基础数据只能是作为订单这个实物的一个“参考”，而不是“关联”，这可以称为“独立性”;再者，订单具有一定的历史性，因为是实物，在实际的过程中，是即时生成的，那么在生成的当时去参考了基础数据，订单就在当时被确定，确对不能因为基础数据的修改而导致订单被“无辜变性”了，这也就是订单的“历史性”，当以后翻阅这些纸张订单时也能对应上系统里的订单。
这是我所理解的最典型的例子，在实际的系统设计中，我们应该多思考一下，是不是要采用“第三范式”，不要再盲目追捧了。
以上纯属我个人意见，仅供参考，欢迎大家讨论。
数据库设计流程
数据库作为数据的一个容器,不但对程序的performance有很大的影响,而且对应用程序的扩展有非常大的影响.所以对应用程序来说,一个具有良好设计的数据库是非常重要的.那么如何才能设计出性能好,又支持扩展的数据库呢?这是我们大家都要去探索的问题.现在有很多版本的数据库设计的流程.然而这也只是目前阶段能设计出一个比较好的数据库的一个途径.更好更优的数据库设计流程是我们追求的目标.但是现在,我们先来了解下目前阶段标准的数据库设计流程.以助于我们在开发应用程序的时候能用到.
先来看下一张数据设计流程图(网上找的,在此感谢这位画图的前辈)

上图是数据库设计一个比较标准的流程图.我们就针对这个流程来讲解数据库设计各个阶段.
需求分析阶段
我们在需求阶段注意两点:
1:考虑到可能的扩充和修改,是设计能易于修改和扩展
2:强调客户参与:目的有几个:更好的理解客户的需求,了解客户的对程序安全性和完整性的要求,以及用户的处理需求.
概念结构设计阶段
在这个阶段我们要设计出能真实反应客观事物的模型,同时让设计的模型能易于理解,易于扩展,能方便的向其他数据库转移.
逻辑结构设计
1:作为对象信息的属性,必须具有原子性的.也就是.我们在画ER图的时候,对象间的关系必须是实体之间的关系,不能是属性和实体的关系.
2:确定数据之间的依赖关系(要极小化出来各个关系,消除冗余),同时要按照数据依赖理论对关系模型进行检查.
数据库物理设计阶段
数据的存储结构以及配置
数据库实施阶段
定义数据库的结构,数据的装载,以及数据库的试运行.
数据库运行和维护阶段
要注意数据的转储和恢复,数据库的安全性和完整性控制.数据库的性能的监督,分析和改造以及数据库的重构
本文只是大而话之的先谈下数据的大的设计流程.并在近期会通过具体的实例来讲解一下这个流程.
Duiker's Blog
数据库设计经验谈(转)
数据库设计经验谈(转)
一个成功的管理系统，是由：[50% 的业务 + 50% 的软件] 所组成，而 50% 的成功软件又有 [25% 的数据库 + 25% 的程序] 所组成，数据库设计的好坏是一个关键。如果把企业的数据比做生命所必需的血液，那么数据库的设计就是应用中最重要的一部分。有关数据库设计的材料汗牛充栋，大学学位课程里也有专门的讲述。不过，就如我们反复强调的那样，再好的老师也比不过经验的教诲。所以我归纳历年来所走的弯路及体会，并在网上找了些对数据库设计颇有造诣的专业人士给大家传授一些设计数据库的技巧和经验。精选了其中的 60 个最佳技巧，并把这些技巧编写成了本文，为了方便索引其内容划分为 5 个部分：
第 1 部分 - 设计数据库之前
这一部分罗列了 12 个基本技巧，包括命名规范和明确业务需求等。
第 2 部分 - 设计数据库表
总共 24 个指南性技巧，涵盖表内字段设计以及应该避免的常见问题等。
第 3 部分 - 选择键
怎么选择键呢？这里有 10 个技巧专门涉及系统生成的主键的正确用法，还有何 时以及如何索引字段以获得最佳性能等。
第 4 部分 - 保证数据完整性
讨论如何保持数据库的清晰和健壮，如何把有害数据降低到最小程度。
第 5 部分 - 各种小技巧
不包括在以上 4 个部分中的其他技巧，五花八门，有了它们希望你的数据库开发工作会更轻松一些。
第 1 部分 - 设计数据库之前
考察现有环境
在设计一个新数据库时，你不但应该仔细研究业务需求而且还要考察现有的系统。大多数数据库项目都不是从头开始建立的；通常，机构内总会存在用来满足特定需求的现有系统（可能没有实现自动计算）。显然，现有系统并不完美，否则你就不必再建立新系统了。但是对旧系统的研究可以让你发现一些可能会忽略的细微问题。一般来说，考察现有系统对你绝对有好处。
定义标准的对象命名规范
一定要定义数据库对象的命名规范。对数据库表来说，从项目一开始就要确定表名是采用复数还是单数形式。此外还要给表的别名定义简单规则（比方说，如果表名是一个单词，别名就取单词的前 4 个字母；如果表名是两个单词，就各取两个单词的前两个字母组成 4 个字母长的别名；如果表的名字由 3 个单词组成，你不妨从头两个单词中各取一个然后从最后一个单词中再取出两个字母，结果还是组成 4 字母长的别名，其余依次类推）对工作用表来说，表名可以加上前缀 WORK_ 后面附上采用该表的应用程序的名字。表内的列[字段]要针对键采用一整套设计规则。比如，如果键是数字类型，你可以用 _N 作为后缀；如果是字符类型则可以采用 _C 后缀。对列[字段]名应该采用标准的前缀和后缀。再如，假如你的表里有好多“money”字段，你不妨给每个列[字段]增加一个 _M 后缀。还有，日期列[字段]最好以 D_ 作为名字打头。
检查表名、报表名和查询名之间的命名规范。你可能会很快就被这些不同的数据库要素的名称搞糊涂了。假如你坚持统一地命名这些数据库的不同组成部分，至少你应该在这些对象名字的开头用 Table、Query 或者 Report 等前缀加以区别。
如果采用了 Microsoft Access，你可以用 qry、rpt、tbl 和 mod 等符号来标识对象（比如 tbl_Employees）。我在和 SQL Server 打交道的时候还用过 tbl 来索引表，但我用 sp_company （现在用 sp_feft_）标识存储过程，因为在有的时候如果我发现了更好的处理办法往往会保存好几个拷贝。我在实现 SQL Server 2000 时用 udf_ （或者类似的标记）标识我编写的函数。
工欲善其事, 必先利其器
采用理想的数据库设计工具，比如：SyBase 公司的 PowerDesign，她支持 PB、VB、Delphe 等语言，通过 ODBC 可以连接市面上流行的 30 多个数据库，包括 dBase、FoxPro、VFP、SQL Server 等，今后有机会我将着重介绍 PowerDesign 的使用。
获取数据模式资源手册
正在寻求示例模式的人可以阅读《数据模式资源手册》一书，该书由 Len Silverston、W. H. Inmon 和 Kent Graziano 编写，是一本值得拥有的最佳数据建模图书。该书包括的章节涵盖多种数据领域，比如人员、机构和工作效能等。其他的你还可以参考：[1]萨师煊　王珊著　数据库系统概论(第二版)高等教育出版社 1991、[2][美] Steven M.Bobrowski 著 Oracle 7 与客户／服务器计算技术从入门到精通　刘建元等译　电子工业出版社，1996、[3]周中元　信息系统建模方法(下)　电子与信息化　1999年第3期，1999
畅想未来，但不可忘了过去的教训
我发现询问用户如何看待未来需求变化非常有用。这样做可以达到两个目的：首先，你可以清楚地了解应用设计在哪个地方应该更具灵活性以及如何避免性能瓶颈；其次，你知道发生事先没有确定的需求变更时用户将和你一样感到吃惊。
一定要记住过去的经验教训！我们开发人员还应该通过分享自己的体会和经验互相帮助。即使用户认为他们再也不需要什么支持了，我们也应该对他们进行这方面的教育，我们都曾经面临过这样的时刻“当初要是这么做了该多好..”。
在物理实践之前进行逻辑设计
在深入物理设计之前要先进行逻辑设计。随着大量的 CASE 工具不断涌现出来，你的设计也可以达到相当高的逻辑水准，你通常可以从整体上更好地了解数据库设计所需要的方方面面。
了解你的业务
在你百分百地确定系统从客户角度满足其需求之前不要在你的 ER（实体关系）模式中加入哪怕一个数据表（怎么，你还没有模式？那请你参看技巧 9）。了解你的企业业务可以在以后的开发阶段节约大量的时间。一旦你明确了业务需求，你就可以自己做出许多决策了。
一旦你认为你已经明确了业务内容，你最好同客户进行一次系统的交流。采用客户的术语并且向他们解释你所想到的和你所听到的。同时还应该用可能、将会和必须等词汇表达出系统的关系基数。这样你就可以让你的客户纠正你自己的理解然后做好下一步的 ER 设计。
创建数据字典和 ER 图表
一定要花点时间创建 ER 图表和数据字典。其中至少应该包含每个字段的数据类型和在每个表内的主外键。创建 ER 图表和数据字典确实有点费时但对其他开发人员要了解整个设计却是完全必要的。越早创建越能有助于避免今后面临的可能混乱，从而可以让任何了解数据库的人都明确如何从数据库中获得数据。
有一份诸如 ER 图表等最新文档其重要性如何强调都不过分，这对表明表之间关系很有用，而数据字典则说明了每个字段的用途以及任何可能存在的别名。对 SQL 表达式的文档化来说这是完全必要的。
创建模式
一张图表胜过千言万语：开发人员不仅要阅读和实现它，而且还要用它来帮助自己和用户对话。模式有助于提高协作效能，这样在先期的数据库设计中几乎不可能出现大的问题。模式不必弄的很复杂；甚至可以简单到手写在一张纸上就可以了。只是要保证其上的逻辑关系今后能产生效益。
从输入输出下手
在定义数据库表和字段需求（输入）时，首先应检查现有的或者已经设计出的报表、查询和视图（输出）以决定为了支持这些输出哪些是必要的表和字段。举个简单的例子：假如客户需要一个报表按照邮政编码排序、分段和求和，你要保证其中包括了单独的邮政编码字段而不要把邮政编码糅进地址字段里。
报表技巧
要了解用户通常是如何报告数据的：批处理还是在线提交报表？时间间隔是每天、每周、每月、每个季度还是每年？如果需要的话还可以考虑创建总结表。系统生成的主键在报表中很难管理。用户在具有系统生成主键的表内用副键进行检索往往会返回许多重复数据。这样的检索性能比较低而且容易引起混乱。
理解客户需求
看起来这应该是显而易见的事，但需求就是来自客户（这里要从内部和外部客户的角度考虑）。不要依赖用户写下来的需求，真正的需求在客户的脑袋里。你要让客户解释其需求，而且随着开发的继续，还要经常询问客户保证其需求仍然在开发的目的之中。一个不变的真理是：“只有我看见了我才知道我想要的是什么”必然会导致大量的返工，因为数据库没有达到客户从来没有写下来的需求标准。而更糟的是你对他们需求的解释只属于你自己，而且可能是完全错误的。
第 2 部分 - 设计表和字段
检查各种变化
我在设计数据库的时候会考虑到哪些数据字段将来可能会发生变更。比方说，姓氏就是如此（注意是西方人的姓氏，比如女性结婚后从夫姓等）。所以，在建立系统存储客户信息时，我倾向于在单独的一个数据表里存储姓氏字段，而且还附加起始日和终止日等字段，这样就可以跟踪这一数据条目的变化。
采用有意义的字段名
有一回我参加开发过一个项目，其中有从其他程序员那里继承的程序，那个程序员喜欢用屏幕上显示数据指示用语命名字段，这也不赖，但不幸的是，她还喜欢用一些奇怪的命名法，其命名采用了匈牙利命名和控制序号的组合形式，比如 cbo1、txt2、txt2_b 等等。
除非你在使用只面向你的缩写字段名的系统，否则请尽可能地把字段描述的清楚些。当然，也别做过头了，比如 Customer_Shipping_Address_Street_Line_1，虽然很富有说明性，但没人愿意键入这么长的名字，具体尺度就在你的把握中。
采用前缀命名
如果多个表里有好多同一类型的字段（比如 FirstName），你不妨用特定表的前缀（比如 CusLastName）来帮助你标识字段。
时效数据
时效性数据应包括“最近更新日期/时间”字段。时间标记对查找数据问题的原因、按日期重新处理/重载数据和清除旧数据特别有用。
标准化和数据驱动
数据的标准化不仅方便了自己而且也方便了其他人。比方说，假如你的用户界面要访问外部数据源（文件、XML 文档、其他数据库等），你不妨把相应的连接和路径信息存储在用户界面支持表里。还有，如果用户界面执行工作流之类的任务（发送邮件、打印信笺、修改记录状态等），那么产生工作流的数据也可以存放在数据库里。预先安排总需要付出努力，但如果这些过程采用数据驱动而非硬编码的方式，那么策略变更和维护都会方便得多。事实上，如果过程是数据驱动的，你就可以把相当大的责任推给用户，由用户来维护自己的工作流过程。
标准化不能过头
对那些不熟悉标准化一词（normalization）的人而言，标准化可以保证表内的字段都是最基础的要素，而这一措施有助于消除数据库中的数据冗余。标准化有好几种形式，但 Third Normal Form（3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好平衡。简单来说，3NF 规定：
* 表内的每一个值都只能被表达一次。
* 表内的每一行都应该被唯一的标识（有唯一键）。
* 表内不应该存储依赖于其他键的非键信息。
遵守 3NF 标准的数据库具有以下特点：有一组表专门存放通过键连接起来的关联数据。比方说，某个存放客户及其有关定单的 3NF 数据库就可能有两个表：Customer 和 Order。Order 表不包含定单关联客户的任何信息，但表内会存放一个键值，该键指向 Customer 表里包含该客户信息的那一行。
更高层次的标准化也有，但更标准是否就一定更好呢？答案是不一定。事实上，对某些项目来说，甚至就连 3NF 都可能给数据库引入太高的复杂性。
为了效率的缘故，对表不进行标准化有时也是必要的，这样的例子很多。曾经有个开发餐饮分析软件的活就是用非标准化表把查询时间从平均 40 秒降低到了两秒左右。虽然我不得不这么做，但我绝不把数据表的非标准化当作当然的设计理念。而具体的操作不过是一种派生。所以如果表出了问题重新产生非标准化的表是完全可能的。
Microsoft Visual FoxPro 报表技巧
如果你正在使用 Microsoft Visual FoxPro，你可以用对用户友好的字段名来代替编号的名称：比如用 Customer Name 代替 txtCNaM。这样，当你用向导程序 [Wizards，台湾人称为‘精灵’] 创建表单和报表时，其名字会让那些不是程序员的人更容易阅读。
不活跃或者不采用的指示符
增加一个字段表示所在记录是否在业务中不再活跃挺有用的。不管是客户、员工还是其他什么人，这样做都能有助于再运行查询的时候过滤活跃或者不活跃状态。同时还消除了新用户在采用数据时所面临的一些问题，比如，某些记录可能不再为他们所用，再删除的时候可以起到一定的防范作用。
使用角色实体定义属于某类别的列[字段]
在需要对属于特定类别或者具有特定角色的事物做定义时，可以用角色实体来创建特定的时间关联关系，从而可以实现自我文档化。
这里的含义不是让 PERSON 实体带有 Title 字段，而是说，为什么不用 PERSON 实体和 PERSON_TYPE 实体来描述人员呢？比方说，当 John Smith, Engineer 提升为 John Smith, Director 乃至最后爬到 John Smith, CIO 的高位，而所有你要做的不过是改变两个表 PERSON 和 PERSON_TYPE 之间关系的键值，同时增加一个日期/时间字段来知道变化是何时发生的。这样，你的 PERSON_TYPE 表就包含了所有 PERSON 的可能类型，比如 Associate、Engineer、Director、CIO 或者 CEO 等。
还有个替代办法就是改变 PERSON 记录来反映新头衔的变化，不过这样一来在时间上无法跟踪个人所处位置的具体时间。
采用常用实体命名机构数据
组织数据的最简单办法就是采用常用名字，比如：PERSON、ORGANIZATION、ADDRESS 和 PHONE 等等。当你把这些常用的一般名字组合起来或者创建特定的相应副实体时，你就得到了自己用的特殊版本。开始的时候采用一般术语的主要原因在于所有的具体用户都能对抽象事物具体化。
有了这些抽象表示，你就可以在第 2 级标识中采用自己的特殊名称，比如，PERSON 可能是 Employee、Spouse、Patient、Client、Customer、Vendor 或者 Teacher 等。同样的，ORGANIZATION 也可能是 MyCompany、MyDepartment、Competitor、Hospital、Warehouse、Government 等。最后 ADDRESS 可以具体为 Site、Location、Home、Work、Client、Vendor、Corporate 和 FieldOffice 等。
采用一般抽象术语来标识“事物”的类别可以让你在关联数据以满足业务要求方面获得巨大的灵活性，同时这样做还可以显著降低数据存储所需的冗余量。
用户来自世界各地
在设计用到网络或者具有其他国际特性的数据库时，一定要记住大多数国家都有不同的字段格式，比如邮政编码等，有些国家，比如新西兰就没有邮政编码一说。
数据重复需要采用分立的数据表
如果你发现自己在重复输入数据，请创建新表和新的关系。
每个表中都应该添加的 3 个有用的字段
* dRecordCreationDate，在 VB 下默认是 Now()，而在 SQL Server 下默认为 GETDATE()
* sRecordCreator，在 SQL Server 下默认为 NOT NULL DEFAULT USER
* nRecordVersion，记录的版本标记；有助于准确说明记录中出现 null 数据或者丢失数据的原因
对地址和电话采用多个字段
描述街道地址就短短一行记录是不够的。Address_Line1、Address_Line2 和 Address_Line3 可以提供更大的灵活性。还有，电话号码和邮件地址最好拥有自己的数据表，其间具有自身的类型和标记类别。
过分标准化可要小心，这样做可能会导致性能上出现问题。虽然地址和电话表分离通常可以达到最佳状态，但是如果需要经常访问这类信息，或许在其父表中存放“首选”信息（比如 Customer 等）更为妥当些。非标准化和加速访问之间的妥协是有一定意义的。
使用多个名称字段
我觉得很吃惊，许多人在数据库里就给 name 留一个字段。我觉得只有刚入门的开发人员才会这么做，但实际上网上这种做法非常普遍。我建议应该把姓氏和名字当作两个字段来处理，然后在查询的时候再把他们组合起来。
我最常用的是在同一表中创建一个计算列[字段]，通过它可以自动地连接标准化后的字段，这样数据变动的时候它也跟着变。不过，这样做在采用建模软件时得很机灵才行。总之，采用连接字段的方式可以有效的隔离用户应用和开发人员界面。
提防大小写混用的对象名和特殊字符
过去最令我恼火的事情之一就是数据库里有大小写混用的对象名，比如 CustomerData。这一问题从 Access 到 Oracle 数据库都存在。我不喜欢采用这种大小写混用的对象命名方法，结果还不得不手工修改名字。想想看，这种数据库/应用程序能混到采用更强大数据库的那一天吗？采用全部大写而且包含下划符的名字具有更好的可读性（CUSTOMER_DATA），绝对不要在对象名的字符之间留空格。
小心保留词
要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突，比如，最近我编写的一个 ODBC 连接程序里有个表，其中就用了 DESC 作为说明字段名。后果可想而知！DESC 是 DESCENDING 缩写后的保留词。表里的一个 SELECT * 语句倒是能用，但我得到的却是一大堆毫无用处的信息。
保持字段名和类型的一致性
在命名字段并为其指定数据类型的时候一定要保证一致性。假如字段在某个表中叫做“agreement_number”，你就别在另一个表里把名字改成“ref1”。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。记住，你干完自己的活了，其他人还要用你的数据库呢。
仔细选择数字类型
在 SQL 中使用 smallint 和 tinyint 类型要特别小心，比如，假如你想看看月销售总额，你的总额字段类型是 smallint，那么，如果总额超过了 $32,767 你就不能进行计算操作了。
删除标记
在表中包含一个“删除标记”字段，这样就可以把行标记为删除。在关系数据库里不要单独删除某一行；最好采用清除数据程序而且要仔细维护索引整体性。
避免使用触发器
触发器的功能通常可以用其他方式实现。在调试程序时触发器可能成为干扰。假如你确实需要采用触发器，你最好集中对它文档化。
包含版本机制
建议你在数据库中引入版本控制机制来确定使用中的数据库的版本。无论如何你都要实现这一要求。时间一长，用户的需求总是会改变的。最终可能会要求修改数据库结构。虽然你可以通过检查新字段或者索引来确定数据库结构的版本，但我发现把版本信息直接存放到数据库中不更为方便吗？。
给文本字段留足余量
ID 类型的文本字段，比如客户 ID 或定单号等等都应该设置得比一般想象更大，因为时间不长你多半就会因为要添加额外的字符而难堪不已。比方说，假设你的客户 ID 为 10 位数长。那你应该把数据库表字段的长度设为 12 或者 13 个字符长。这算浪费空间吗？是有一点，但也没你想象的那么多：一个字段加长 3 个字符在有 1 百万条记录，再加上一点索引的情况下才不过让整个数据库多占据 3MB 的空间。但这额外占据的空间却无需将来重构整个数据库就可以实现数据库规模的增长了。身份证的号码从 15 位变成 18 位就是最好和最惨痛的例子。
列[字段]命名技巧
我们发现，假如你给每个表的列[字段]名都采用统一的前缀，那么在编写 SQL 表达式的时候会得到大大的简化。这样做也确实有缺点，比如破坏了自动表连接工具的作用，后者把公共列[字段]名同某些数据库联系起来，不过就连这些工具有时不也连接错误嘛。举个简单的例子，假设有两个表：
Customer 和 Order。Customer 表的前缀是 cu_，所以该表内的字段名如下：cu_name_id、cu_surname、cu_initials 和cu_address 等。Order 表的前缀是 or_，所以字段名是：
or_order_id、or_cust_name_id、or_quantity 和 or_description 等。
这样从数据库中选出全部数据的 SQL 语句可以写成如下所示：
Select * From Customer, Order Where cu_surname = "MYNAME" ;
and cu_name_id = or_cust_name_id and or_quantity = 1
在没有这些前缀的情况下则写成这个样子（用别名来区分）：
Select * From Customer, Order Where Customer.surname = "MYNAME" ;
and Customer.name_id = Order.cust_name_id and Order.quantity = 1
第 1 个 SQL 语句没少键入多少字符。但如果查询涉及到 5 个表乃至更多的列[字段]你就知道这个技巧多有用了。
第 3 部分 - 选择键和索引
数据采掘要预先计划
我所在的某一客户部门一度要处理 8 万多份联系方式，同时填写每个客户的必要数据（这绝对不是小活）。我从中还要确定出一组客户作为市场目标。当我从最开始设计表和字段的时候，我试图不在主索引里增加太多的字段以便加快数据库的运行速度。然后我意识到特定的组查询和信息采掘既不准确速度也不快。结果只好在主索引中重建而且合并了数据字段。我发现有一个指示计划相当关键——当我想创建系统类型查找时为什么要采用号码作为主索引字段呢？我可以用传真号码进行检索，但是它几乎就象系统类型一样对我来说并不重要。采用后者作为主字段，数据库更新后重新索引和检索就快多了。
可操作数据仓库（ODS）和数据仓库（DW）这两种环境下的数据索引是有差别的。在 DW 环境下，你要考虑销售部门是如何组织销售活动的。他们并不是数据库管理员，但是他们确定表内的键信息。这里设计人员或者数据库工作人员应该分析数据库结构从而确定出性能和正确输出之间的最佳条件。
使用系统生成的主键
这类同技巧 1，但我觉得有必要在这里重复提醒大家。假如你总是在设计数据库的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问。
采用系统生成键作为主键还有一个优点：当你拥有一致的键结构时，找到逻辑缺陷很容易。
分解字段用于索引
为了分离命名字段和包含字段以支持用户定义的报表，请考虑分解其他字段（甚至主键）为其组成要素以便用户可以对其进行索引。索引将加快 SQL 和报表生成器脚本的执行速度。比方说，我通常在必须使用 SQL LIKE 表达式的情况下创建报表，因为 case number 字段无法分解为 year、serial number、case type 和 defendant code 等要素。性能也会变坏。假如年度和类型字段可以分解为索引字段那么这些报表运行起来就会快多了。
键设计 4 原则
* 为关联字段创建外键。
* 所有的键都必须唯一。
* 避免使用复合键。
* 外键总是关联唯一的键字段。
别忘了索引
索引是从数据库中获取数据的最高效方式之一。95% 的数据库性能问题都可以采用索引技术得到解决。作为一条规则，我通常对逻辑主键使用唯一的成组索引，对系统键（作为存储过程）采用唯一的非成组索引，对任何外键列[字段]采用非成组索引。不过，索引就象是盐，太多了菜就咸了。你得考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用作读写。
大多数数据库都索引自动创建的主键字段，但是可别忘了索引外键，它们也是经常使用的键，比如运行查询显示主表和所有关联表的某条记录就用得上。还有，不要索引 memo/note 字段，不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间。
不要索引常用的小型表
不要为小型数据表设置任何键，假如它们经常有插入和删除操作就更别这样作了。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。
不要把社会保障号码（SSN）或身份证号码（ID）选作键
永远都不要使用 SSN 或 ID 作为数据库的键。除了隐私原因以外，须知政府越来越趋向于不准许把 SSN 或 ID 用作除收入相关以外的其他目的，SSN 或 ID 需要手工输入。永远不要使用手工输入的键作为主键，因为一旦你输入错误，你唯一能做的就是删除整个记录然后从头开始。
我在破解他人的程序时候，我看到很多人把 SSN 或 ID 还曾被用做系列号，当然尽管这么做是非法的。而且人们也都知道这是非法的，但他们已经习惯了。后来，随着盗取身份犯罪案件的增加，我现在的同行正痛苦地从一大摊子数据中把 SSN 或 ID 删除。
不要用用户的键
在确定采用什么字段作为表的键的时候，可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。这样做会迫使你采取以下两个措施：
* 在创建记录之后对用户编辑字段的行为施加限制。假如你这么做了，你可能会发现你的应用程序在商务需求突然发生变化，而用户需要编辑那些不可编辑的字段时缺乏足够的灵活性。当用户在输入数据之后直到保存记录才发现系统出了问题他们该怎么想？删除重建？假如记录不可重建是否让用户走开？
* 提出一些检测和纠正键冲突的方法。通常，费点精力也就搞定了，但是从性能上来看这样做的代价就比较大了。还有，键的纠正可能会迫使你突破你的数据和商业/用户界面层之间的隔离。
所以还是重提一句老话：你的设计要适应用户而不是让用户来适应你的设计。
不让主键具有可更新性的原因是在关系模式下，主键实现了不同表之间的关联。比如，Customer 表有一个主键 CustomerID，而客户的定单则存放在另一个表里。Order 表的主键可能是 OrderNo 或者 OrderNo、CustomerID 和日期的组合。不管你选择哪种键设置，你都需要在 Order 表中存放 CustomerID 来保证你可以给下定单的用户找到其定单记录。
假如你在 Customer 表里修改了 CustomerID，那么你必须找出 Order 表中的所有相关记录对其进行修改。否则，有些定单就会不属于任何客户——数据库的完整性就算完蛋了。
如果索引完整性规则施加到表一级，那么在不编写大量代码和附加删除记录的情况下几乎不可能改变某一条记录的键和数据库内所有关联的记录。而这一过程往往错误丛生所以应该尽量避免。
可选键(候选键)有时可做主键
记住，查询数据的不是机器而是人。
假如你有可选键，你可能进一步把它用做主键。那样的话，你就拥有了建立强大索引的能力。这样可以阻止使用数据库的人不得不连接数据库从而恰当的过滤数据。在严格控制域表的数据库上，这种负载是比较醒目的。如果可选键真正有用，那就是达到了主键的水准。
我的看法是，假如你有可选键，比如国家表内的 state_code，你不要在现有不能变动的唯一键上创建后续的键。你要做的无非是创建毫无价值的数据。如你因为过度使用表的后续键[别名]建立这种表的关联，操作负载真得需要考虑一下了。
别忘了外键
大多数数据库索引自动创建的主键字段。但别忘了索引外键字段，它们在你想查询主表中的记录及其关联记录时每次都会用到。还有，不要索引 memo/notes 字段而且不要索引大型文本字段（许多字符），这样做会让你的索引占据大量的数据库空间。
第 4 部分 - 保证数据的完整性
用约束而非商务规则强制数据完整性
如果你按照商务规则来处理需求，那么你应当检查商务层次/用户界面：如果商务规则以后发生变化，那么只需要进行更新即可。假如需求源于维护数据完整性的需要，那么在数据库层面上需要施加限制条件。如果你在数据层确实采用了约束，你要保证有办法把更新不能通过约束检查的原因采用用户理解的语言通知用户界面。除非你的字段命名很冗长，否则字段名本身还不够。
只要有可能，请采用数据库系统实现数据的完整性。这不但包括通过标准化实现的完整性而且还包括数据的功能性。在写数据的时候还可以增加触发器来保证数据的正确性。不要依赖于商务层保证数据完整性；它不能保证表之间（外键）的完整性所以不能强加于其他完整性规则之上。
分布式数据系统
对分布式系统而言，在你决定是否在各个站点复制所有数据还是把数据保存在一个地方之前应该估计一下未来 5 年或者 10 年的数据量。当你把数据传送到其他站点的时候，最好在数据库字段中设置一些标记。在目的站点收到你的数据之后更新你的标记。为了进行这种数据传输，请写下你自己的批处理或者调度程序以特定时间间隔运行而不要让用户在每天的工作后传输数据。本地拷贝你的维护数据，比如计算常数和利息率等，设置版本号保证数据在每个站点都完全一致。
强制指示完整性(参照完整性?)
没有好办法能在有害数据进入数据库之后消除它，所以你应该在它进入数据库之前将其剔除。激活数据库系统的指示完整性特性。这样可以保持数据的清洁而能迫使开发人员投入更多的时间处理错误条件。
关系
如果两个实体之间存在多对一关系，而且还有可能转化为多对多关系，那么你最好一开始就设置成多对多关系。从现有的多对一关系转变为多对多关系比一开始就是多对多关系要难得多。
采用视图
为了在你的数据库和你的应用程序代码之间提供另一层抽象，你可以为你的应用程序建立专门的视图而不必非要应用程序直接访问数据表。这样做还等于在处理数据库变更时给你提供了更多的自由。
给数据保有和恢复制定计划
考虑数据保有策略并包含在设计过程中，预先设计你的数据恢复过程。采用可以发布给用户/开发人员的数据字典实现方便的数据识别同时保证对数据源文档化。编写在线更新来“更新查询”供以后万一数据丢失可以重新处理更新。
用存储过程让系统做重活
解决了许多麻烦来产生一个具有高度完整性的数据库解决方案之后，我决定封装一些关联表的功能组，提供一整套常规的存储过程来访问各组以便加快速度和简化客户程序代码的开发。数据库不只是一个存放数据的地方，它也是简化编码之地。
使用查找
控制数据完整性的最佳方式就是限制用户的选择。只要有可能都应该提供给用户一个清晰的价值列表供其选择。这样将减少键入代码的错误和误解同时提供数据的一致性。某些公共数据特别适合查找：国家代码、状态代码等。
第 5 部分 - 各种小技巧
文档、文档、文档
对所有的快捷方式、命名规范、限制和函数都要编制文档。
采用给表、列[字段]、触发器等加注释的数据库工具。是的，这有点费事，但从长远来看，这样做对开发、支持和跟踪修改非常有用。
取决于你使用的数据库系统，可能有一些软件会给你一些供你很快上手的文档。你可能希望先开始在说，然后获得越来越多的细节。或者你可能希望周期性的预排，在输入新数据同时随着你的进展对每一部分细节化。不管你选择哪种方式，总要对你的数据库文档化，或者在数据库自身的内部或者单独建立文档。这样，当你过了一年多时间后再回过头来做第 2 个版本，你犯错的机会将大大减少。
使用常用英语（或者其他任何语言）而不要使用编码
为什么我们经常采用编码（比如 9935A 可能是‘青岛啤酒’的供应代码，4XF788-Q 可能是帐目编码）？理由很多。但是用户通常都用英语进行思考而不是编码。工作 5 年的会计或许知道 4XF788-Q 是什么东西，但新来的可就不一定了。在创建下拉菜单、列表、报表时最好按照英语名排序。假如你需要编码，那你可以在编码旁附上用户知道的英语。
保存常用信息
让一个表专门存放一般数据库信息非常有用。我常在这个表里存放数据库当前版本、最近检查/修复（对 FoxPro）、关联设计文档的名称、客户等信息。这样可以实现一种简单机制跟踪数据库，当客户抱怨他们的数据库没有达到希望的要求而与你联系时，这样做对非客户机/服务器环境特别有用。
测试、测试、反复测试
建立或者修订数据库之后，必须用用户新输入的数据测试数据字段。最重要的是，让用户进行测试并且同用户一道保证你选择的数据类型满足商业要求。测试需要在把新数据库投入实际服务之前完成。
检查设计
在开发期间检查数据库设计的常用技术是通过其所支持的应用程序原型检查数据库。换句话说，针对每一种最终表达数据的原型应用，保证你检查了数据模型并且查看如何取出数据。
Microsoft Visual FoxPro 设计技巧
对复杂的 Microsoft Visual FoxPro 数据库应用程序而言，可以把所有的主表放在一个数据库容器文件里，然后增加其他数据库表文件和装载同原有数据库有关的特殊文件。根据需要用这些文件连接到主文件中的主表。比如数据输入、数据索引、统计分析、向管理层或者政府部门提供报表以及各类只读查询等。这一措施简化了用户和组权限的分配，而且有利于应用程序函数（存储过程）的分组和划分，从而在程序必须修改的时候易于管理。
数据库设计中一个矛盾:数据库外键,用还是不用？你怎么看.?
2009-11-11 17:40 by chenkai, 4769 visits,收藏,编辑
最近在做一个派单系统数据库设计，在设计中有些疑惑的地方中午在网上发起一个话题讨论. 我把这个讨论流程.发过来 大家可以可以看看.
也可以发表一下自己的意见.
对于主/外键/索引来说，在一些开发团队中被认为是处理数据库关系的利器，也被某些开发团队认为是处理某些具体业务的魔鬼，您的观点呢？在实际应用中您会采取哪种方式？
大家共同观点：
主键和索引是不可少的，不仅可以优化数据检索速度，开发人员还省不其它的工作,
矛盾焦点：数据库设计是否需要外键。这里有两个问题：一个是如何保证数据库数据的完整性和一致性；二是第一条对性能的影响
2009-11-11 13:07 changShaHacker
正方观点：
1，由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。
eg：数据库和应用是一对多的关系，Ａ应用会维护他那部分数据的完整性，系统一变大时，增加了Ｂ应用，Ａ和Ｂ两个应用也许是不同的开发团队来做的。他们如何协调保证数据的完整性，而且一年以后如果又增加了C应用呢？
2，有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。
3，外键在一定程度上说明的业务逻辑，会使设计周到具体全面
2009-11-11 13:08 TeDongDesiger
反方观点：
1，可以用触发器或应用程序保证数据的完整性
2，过分强调或者说使用主键／外键会平添开发难度，导致表过多等问题
3，不用外键时数据管理简单，操作方便，性能高（导入导出等操作，在insert,   update,   delete   数据的时候更快）
eg:在海量的数据库中想都不要去想外键，试想，一个程序每天要insert数百万条记录，当存在外键约束的时候，每次要去扫描此记录是否合格，一般还不止一个字段有外键，这样扫描的数量是成级数的增长！我的一个程序入库在3个小时做完，如果加上外键，需要28个小时！
结论：
1，在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便，最好用外键。
2，用外键要适当，不能过分追求
3，不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库
欢迎各位发表观点...
数据库设计范式深入浅出[转]
关系数据库设计之时是要遵守一定的规则的。尤其是数据库设计范式 现简单介绍1NF（第一范式），2NF（第二范式），3NF（第三范式）和BCNF，另有第四范式和第五范式留到以后再介绍。 在你设计数据库之时，若能符合这几个范式，你就是数据库设计的高手。
第一范式（1NF）：在关系模式R中的每一个具体关系r中，如果每个属性值 都是不可再分的最小数据单位，则称R是第一范式的关系。例：如职工号，姓名，电话号码组成一个表（一个人可能有一个办公室电话 和一个家里电话号码） 规范成为1NF有三种方法：
一是重复存储职工号和姓名。这样，关键字只能是电话号码。
二是职工号为关键字，电话号码分为单位电话和住宅电话两个属性
三是职工号为关键字，但强制每条记录只能有一个电话号码。
以上三个方法，第一种方法最不可取，按实际情况选取后两种情况。
第二范式（2NF）：如果关系模式R（U，F）中的所有非主属性都完全依赖于任意一个候选关键字，则称关系R 是属于第二范式的。
例：选课关系 SCI（SNO，CNO，GRADE，CREDIT）其中SNO为学号， CNO为课程号，GRADEGE 为成绩，CREDIT 为学分。 由以上条件，关键字为组合关键字（SNO，CNO）
在应用中使用以上关系模式有以下问题：
a.数据冗余，假设同一门课由40个学生选修，学分就 重复40次。
b.更新异常，若调整了某课程的学分，相应的元组CREDIT值都要更新，有可能会出现同一门课学分不同。
c.插入异常，如计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。
d.删除异常，若学生已经结业，从当前数据库删除选修记录。某些门课程新生尚未选修，则此门课程及学分记录无法保存。
原因：非关键字属性CREDIT仅函数依赖于CNO，也就是CREDIT部分依赖组合关键字（SNO，CNO）而不是完全依赖。
解决方法：分成两个关系模式 SC1（SNO，CNO，GRADE），C2（CNO，CREDIT）。新关系包括两个关系模式，它们之间通过SC1中的外关键字CNO相联系，需要时再进行自然联接，恢复了原来的关系
第三范式（3NF）：如果关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递信赖，则称关系R是属于第三范式的。
例：如S1（SNO，SNAME，DNO，DNAME，LOCATION） 各属性分别代表学号，
姓名，所在系，系名称，系地址。
关键字SNO决定各个属性。由于是单个关键字，没有部分依赖的问题，肯定是2NF。但这关系肯定有大量的冗余，有关学生所在的几个属性DNO，DNAME，LOCATION将重复存储，插入，删除和修改时也将产生类似以上例的情况。
原因：关系中存在传递依赖造成的。即SNO -> DNO。 而DNO -> SNO却不存在，DNO -> LOCATION, 因此关键辽 SNO 对 LOCATION 函数决定是通过传递依赖 SNO -> LOCATION 实现的。也就是说，SNO不直接决定非主属性LOCATION。
解决目地：每个关系模式中不能留有传递依赖。
解决方法：分为两个关系 S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）
注意：关系S中不能没有外关键字DNO。否则两个关系之间失去联系。
BCNF：如果关系模式R（U，F）的所有属性（包括主属性和非主属性）都不传递依赖于R的任何候选关键字，那么称关系R是属于BCNF的。或是关系模式R，如果每个决定因素都包含关键字（而不是被关键字所包含），则RCNF的关系模式。
例：配件管理关系模式 WPE（WNO，PNO，ENO，QNT）分别表仓库号，配件号，职工号，数量。有以下条件
a.一个仓库有多个职工。
b.一个职工仅在一个仓库工作。
c.每个仓库里一种型号的配件由专人负责，但一个人可以管理几种配件。
d.同一种型号的配件可以分放在几个仓库中。
分析：由以上得 PNO 不能确定QNT，由组合属性（WNO，PNO）来决定，存在函数依赖（WNO，PNO） -> ENO。由于每个仓库里的一种配件由专人负责，而一个人可以管理几种配件，所以有组合属性（WNO，PNO）才能确定负责人，有（WNO，PNO）-> ENO。因为 一个职工仅在一个仓库工作，有ENO -> WNO。由于每个仓库里的一种配件由专人负责，而一个职工仅在一个仓库工作，有 （ENO，PNO）-> QNT。
找一下候选关键字，因为（WNO，PNO） -> QNT，（WNO，PNO）-> ENO ，因此 （WNO，PNO）可以决定整个元组，是一个候选关键字。根据ENO->WNO，（ENO，PNO）->QNT，故（ENO，PNO）也能决定整个元组，为另一个候选关键字。属性ENO，WNO，PNO 均为主属性，只有一个非主属性QNT。它对任何一个候选关键字都是完全函数依赖的，并且是直接依赖，所以该关系模式是3NF。
分析一下主属性。因为ENO->WNO，主属性ENO是WNO的决定因素，但是它本身不是关键字，只是组合关键字的一部分。这就造成主属性WNO对另外一个候选关键字（ENO，PNO）的部 分依赖，因为（ENO，PNO）-> ENO但反过来不成立，而P->WNO，故（ENO，PNO）-> WNO 也是传递依赖。
虽然没有非主属性对候选关键辽的传递依赖，但存在主属性对候选关键字的传递依赖，同样也会带来麻烦。如一个新职工分配到仓库工作，但暂时处于实习阶段，没有独立负责对某些配件的管理任务。由于缺少关键字的一部分PNO而无法插入到该关系中去。又如某个人改成不管配件了去负责安全，则在删除配件的同时该职工也会被删除。
解决办法：分成管理EP（ENO，PNO，QNT），关键字是（ENO，PNO）工作EW（ENO，WNO）其关键字是ENO
缺点：分解后函数依赖的保持性较差。如此例中，由于分解,函数依赖（WNO，PNO）-> ENO 丢失了, 因而对原来的语义有所破坏。没有体现出每个仓库里一种部件由专人负责。有可能出现 一部件由两个人或两个以上的人来同时管理。因此，分解之后的关系模式降低了部分完整性约束。
一个关系分解成多个关系，要使得分解有意义，起码的要求是分解后不丢失原来的信息。这些信息不仅包括数据本身，而且包括由函数依赖所表示的数据之间的相互制约。进行分解的目标是达到更高一级的规范化程度，但是分解的同时必须考虑两个问题：无损联接性和保持函数依赖。有时往往不可能做到既有无损联接性，又完全保持函数依赖。需要根据需要进行权衡。
1NF直到BCNF的四种范式之间有如下关系：
BCNF包含了3NF包含2NF包含1NF
小结：
目地：规范化目的是使结构更合理，消除存储异常，使数据冗余尽量小，便于插入、删除和更新
原则：遵从概念单一化 "一事一地"原则，即一个关系模式描述一个实体或实体间的一种联系。规范的实质就是概念的单一化。
方法：将关系模式投影分解成两个或两个以上的关系模式。
要求：分解后的关系模式集合应当与原关系模式"等价"，即经过自然联接可以恢复原关系而不丢失信息，并保持属性间合理的联系。
注意：一个关系模式结这分解可以得到不同关系模式集合，也就是说分解方法不是唯一的。最小冗余的要求必须以分解后的数据库能够表达原来数据库所有信息为前提来实现。其根本目标是节省存储空间，避免数据不一致性，提高对关系的操作效率，同时满足应用需求。实际上，并不一定要求全部模式都达到BCNF不可。有时故意保留部分冗余可能更方便数据查询。尤其对于那些更新频度不高，查询频度极高的数据库系统更是如此。
在关系数据库中，除了函数依赖之外还有多值依赖，联接依赖的问题，从而提出了第四范式，第五范式等更高一级的规范化要求。在此，以后再谈。
各位朋友，你看过后有何感想，其实，任何一本数据库基础理论的书都会讲这些东西，考虑到很多网友是半途出家，来做数据库。特找一本书大抄特抄一把，各位有什么问题，也别问我了，自已去找一本关系数据库理论的书去看吧，说不定，对各位大有帮助。说是说以上是基础理论的东西，请大家想想，你在做数据库设计的时候有没有考虑过遵过以上几个范式呢，有没有在数据库设计做得不好之时，想一想，对比以上所讲，到底是违反了第几个范式呢？
我见过的数据库设计，很少有人做到很符合以上几个范式的，一般说来，第一范式大家都可以遵守，完全遵守第二第三范式的人很少了，遵守的人一定就是设计数据库的高手了，BCNF的范式出现机会较少，而且会破坏完整性，你可以在做设计之时不考虑它，当然在ORACLE中可通过触发器解决其缺点。以后我们共同做设计之时，也希望大家遵守以上几个范式。
YY淘宝商品数据库设计
前言
这几个月都在做一个通过淘宝API线下管理淘宝店的系统，学习了很多东西，这里想对淘宝商品表设计用自己的想法表现出来，如果你觉得很扯淡，可以写下自己的看法.OK，切入正题.
淘宝的商品这块的复杂程度，是我见过的电子商务网站中最复杂的，灵活性最高的.在看下文之前，先说一下在淘宝中的以下名词:关键属性，销售属性，非关键属性。如下图：

关键属性：能够确认唯一产品的属性，可以是一个，或者多个关键属性的组合，比如：相机的"品牌""型号"能确定唯一的产品，服装的"品牌""货号"能确定唯一的产品
销售属性：组成SKU的特殊属性，它会影响买家的购买和卖家的库存管理，如服装的"颜色"、"套餐"和"尺码"，注意这里的SKU，淘宝销售属性组合成SKU
非关键属性：就是商品属性了，一些对商品进行描述的属性
特征量，特征值的设计
最初了解这种设计，是项目经理的指导，他瞅一眼数据结构之后立马说：典型的特征量对应特征值的设计。额，佩服。恩，看下下图这2个表的数据，就很清楚了，

在特征量这个表中，存放所有的例如：品牌，系列，颜色，尺码 这些名称，在特征值中存放具体的值,比如衣服有12种颜色,那么左表中有 ID：1, 名字：颜色，在右表中存放
黄色，绿色等等具体的值，特征量表是一个父子关系，比如有的品牌下面有系列，系列下面可能还有系列，右表的数据存在冗余是不可避免的，比如衣服有24个颜色，鞋有24个颜色，虽然特征值是一样，但是是属于不同的特征量的。
那么如上图所示，（注意，上图中特征量表我并没有添加父子关系，在设计的时候是有父子关系的）我要添加一个避孕套商品，最少分为2步，首先在类别表中选中计生用品-->一直到避孕套这个子分类，通过子分类的ID，级联查询特征量和特征值这个表，得到诸如品牌，大小，颜色这种属性，如果存在层级关系，比如品牌下面的系列，系列下面还可能有系列，通过特征量表中的父子关系，继续查询出来，当然，也可以通过类目ID一次性查询出来，然后进行关系显示。
对具有不同属性的同种商品做的不同价格，数量设计？

我们看在淘宝中添加衣服和数码相机的情况，这里会出现选择"关键属性" 颜色+尺码之后，出现需要对不同颜色尺码的组合填写价格和数量和商家外部码，相机则是颜色+套餐+外部码，而到了洗发水就是容量+多买多便宜+外部码，这里就是上面介绍的SKU，如果你还不了解SKU，可以做下了解，这样子做的直接好处就是，不同的搭配可能有不同的价格，做库存和销量的统计的时候就能做到准确统计，通过商家的外部编码能让商家关联本地系统，也是为了做统计和库存的方便。
恩 大伙瞅瞅我的设计吧，要拍，请轻拍
2011-03-23 修正：这篇文章以我现在的理解来说，表设计存在问题！！请看到这里马上离开!你也可以关注我后续淘宝分析相关文章~

1.类目和特征量，特征量和特征值并不是多对多的关系，是一对多的关系
2.类目和特征量都是父子表
3.商品的关键属性如：品牌 系列（只存放子及ID），其它属性，比如说裤子的裤型，适合人群，等属性存入基本属性表，这里品牌系列只存放子及ID是因为考虑到，不想建立过多的表，用户搜索用例如lunce把全部信息建好索引，做统计或者数据分析的时候也是可以通过父子特征量的父子关系进行的。
4.销售属性的分开是因为，比如洗发水，我们的特征值表中的值可能不能符合用户需求，那么可以让用户自定义值，颜色可以上传图片等，而且销售属性一般为多个组合，所以单独建立一个表。
5.商品的SKU表，这个特征量ID的组合实际上就是销售属性表的ID组合
当我们添加一个商品的时候，在不考虑页面展现的情况下需要这几步：
1.插入商品基本信息到商品表
2.插入基本属性和关键属性到商品基本属性表
3.插入销售属性到销售属性表
4.插入销售属性ID组合，数量，价格等到商品SKU表。
当我们在页面上展示商品的时候:
1.级联6个表查询出商品所需要的信息，由于关键属性有多级，这里只存了子及，需要做一次递归。当然可以考虑缓存等自己的实现方法
2.分析商品SKU组合，生成销售属性组合及约束，比如颜色+尺码 选择之后的不同价格，是否还有货。颜色有图片的用上传的图片代替默认背景
难点在于：
1.页面的展现不管前台还是后台都必须动态构建，在动态构建的基础上加上基本的验证，比如用户选择了 颜色A+尺码，库存只有3件，数量填写了5，必要提示用户没有了这么多数量，动态构建中还存在某些属性有层级关系，需要按照自己的策略选择一次性加载或者AJAX加载，进行分析显示
2.几个表关联查询的设计，何种方法能最大化减小程序的复杂程度，是直接在数据库中搞定，还是数据库结合内存表(缓存特征量和特征值).
页面上的展现和其它设计
我在实际项目中添加商品的做法是，序列化属性成json字符串到页面，根据特征量中的是否是枚举，是否是父属性，是否关键属性，是否销售属性来动态创建页面表单，效果如他的API页面，如果你有兴趣，可以参考他的JS
在杨过大哥的博客中，这篇讲到了他的网站添加商品的时候动态创建表单的做法，还有一篇类目属性的设计，那种类目属性设计也是一种不错的设计，有兴趣可以看看。
恩 文章到这完了，睡觉去了。 今天可能是博客园360和 qq 的软文的又一个小高潮，悲剧啊！
写在前面：本来“程序员应知”系列中应该写的都是与程序员密切相关的内容，而数据库设计似乎应该是数据库管理员的工作。然而，在实际的工作环境中，我所经历几乎所有的项目中，数据库设计工作都是由程序员来完成的；就算我们是不需要做数据库设计的程序员，也至少需要对数据库的结构有充分的理解，那样也便于我们编写和维护系统。思量再三，我还是将这篇与数据库设计相关的文章放在了这个系列当中。
在几乎所有的企业级应用程序中，包括各种MIS、ERP、CRM等等，都会使用数据库，这样的好处是显而易见的，很容易地实现了数据层和业务逻辑层的分离，而且对于性能的优化也在一定程度上提供了便利。
然而，在我所经历过的项目中，某些数据库的设计会存在一些问题，尤其普遍的就是下面将要描述的这两点，个人觉得是应该避免的误区，总结出来与大家讨论。
误区之一 备用字段
现象描述：
在数据表中，不仅设计了当前所需要的字段，而且还在其中留出几个字段作为备用。
比方说，我设计了一个人员表（Person），其中已经添加了各种必要的字段，包括姓名（Name）、性别（Sex）、出生年月日（birthday）等等。大功告成之后，我忽然想到，将来系统中应该还会有很多其它与人相关的内容吧，比方说毕业院校，比方说工作单位等等，尽管现在根本不需要填写，以后可能还是会用到的吧。拍脑袋一项，那就加入5个varchar2型的字段，分别叫做Text1、Text2……Text5，然后又想，应该还有一些日期型的字段需要备用，就又建立了三个date型的字段，分别起名叫做date1、date2、date3，……
原因分析：
大家应该已经看出问题了，在这个数据表中存在大量暂时无用的字段，我们可以称之为备用字段，它们的作用是什么呢？就是以防万一，防备可能的情况。
这似乎可以叫做防患于未然，等到时候需要的时候，就不需要在表中增加新的字段了，而且这样做的话，一个表的数据应该会被存储在相邻的物理空间中，这对于性能也是有好处的。
另外的原因就是，在古老的数据库中，如果改变数据库的定义（包括增加字段、改变字段的类型、删除字段等等），那么其中所有的数据就会丢失，所以这项工作非常麻烦，我们需要先建立临时表，将数据备份出来，然后创建新表，将数据导入其中，最后再删除原来的表。
问题所在：
这样的做法对于项目会导致很多问题，而且原先想要解决的问题并不一定能够解决，不信的话，请往下看。
问题一：增加大量备用字段，必定会浪费很多空间，尽管其中可能都没有具体的数据，但是仅仅是空字段也会占据一定的空间的。
问题二：由于命名的特点，如果没有完善的文档管理流程，用不了多久（可能也就是两三年），就没有人能够说清楚到底哪个字段代表的是什么意义了。就算有文档管理，这些管理工作也会比较麻烦，而且在每次使用的时候都需要申请，还有可能会出现冲突的情况。
问题三：增加了这些备用字段就真的会够用吗？不一定，因为我们只是每个类型的字段留出几个备用，如果数量超过，或者要使用特殊的、不常用的类型的时候，还是需要增加新的字段。比方说在上述的Person表中，我们要存储照片，那么可能就要增加一个blob类型的photo字段，这在初期设计的时候可不一定会留出这样的备用字段。而且如果没有完善的管理，谁又能说清楚倒底哪个字段已经被使用，哪个字段还可以使用呢？到时候还不是要增加新的字段。
解决方案：
其实上面的这种设计方式就是一种“过度设计”，我们应该做的就是“按需设计”，在经过详细有效的分析之后，在数据表中只放置必要的字段，而不要留出大量的备用字段。
当需要增加相关的信息的时候，就要具体情况具体分析：
如果数量很少，而且信息的性质与原表密切相关，那么就可以直接在原表上增加字段，并将相关的数据更新进去。
如果数量较大，或者并非是原表对象至关重要的属性，那么就可以新增一个表，然后通过键值连接起来。
对于表的数据的存储位置所导致的性能问题，我们可以通过在特定时间对数据库的数据进行重组来解决，而这项工作对于长期运行的数据库来说，也是需要定期进行的。
误区之二 有意义的编码
现象描述：
使用有意义的编码作为一条记录的ID，甚至作为数据库的主键存在，例如，一个员工的编码设置为0203004，其中02代表员工所在分公司，03代表员工所在部门，004代表员工进入到该部门的序号。
原因分析：
ID的设置方式大概有以下几种，一种是纯粹的流水号，从1开始，每次加1，或者对其将以改进，将数字转换成为字符串的格式，比方说“0000001”；一种是无意义的随机编码，比方说GUID；还有一种就是有意义的编码，特定的位数会代表一定的意义。
我想之所以大家这么喜欢使用这种方式，主要是因为想要从编码中就能够得到一些信息，甚至有些程序中还有专门的对编码进行解析的模块。就像我们的身份证号码一样，看到身份证号就可以知道办身份证时的所在地、生日、性别等信息。
问题所在：
其实有意义的编码会导致很多问题，请看：
问题一：对编码资源的浪费。如果是纯粹的流水号，那么从1到10000就可以代表一万条记录，但是，如果使用有意义的编码，很可能1000条记录就会让五位的编码不够用。我就遇到过真正的情况，我们公司的投保单号码的第一位就是有意义的，代表的时该投保单所属的渠道，后面跟着很长的一串数字（9位）。理论上来说，这些编码永远都不会用完，但是，最开始的三个渠道使用的是1、4、7三个编码，但是一次新保险法的实行，导致原有的投保单作废，于是又启用了三个数字2、5、8，接下来公司改名，三个渠道又分别将投保单报废，重新启用新的开头数字，就这样，短短的几年间，所有的投保单号码全都被用完了，其实打印出来的投保单不过100万张。
问题二：不一定是唯一的，难以作为主键。想一下，我们的身份证号码就是这样的。原先15位的时候，后三位是序号，而男性会使用奇数，女性会使用偶数，这样就是说，一个地区同一天生日的人，男女都不能超过500人，否则就会导致号码的重复，尽管出现这种现象的概率比较低，但是还是客观存在的。
问题三：代表的意义不一定准确。比方说用带有意义的编码来为员工定义工号，其中可能会有部门、职务等等意义，但是如果员工在部门间发生了调动，或者职级发生了改变，是否需要改变他的编码呢？改变吧，那么所有的历史数据都要随之修改一次，工作量会非常大；不改变吧，那么代表的意义就不再准确，我们就无法从编码中得到该员工准确的信息。
解决方案：
所以，对于编码，非常不建议使用有意义的编码，要么使用纯粹的流水号，但这样可能需要定义一个范围比较大的类型，对于海量记录的数据，可能会不够用；那样的话就可以使用GUID，这样编码永远都不会重复，而且会有大量的编码资源可用。
从上面的两点我们可以看出，在数据库设计的过程中，有一些在非常多系统中都使用了，但是却带来了很多问题的方法，对于这种情况，我们就应该仔细思考，然后痛下决心，坚决抵制。
排行榜数据库设计与分析——为什么实时排行不可行?
很多网游中都有排行榜，这里就专门讨论一下这个排行榜背后的数据库设计。一开始我觉得这是一个基本的数据库设计问题。只需要有一个实体，没有实体间的关系，没有复杂的逻辑。网络上也搜索不到太多关于这类设计的问题，好像根本不值得为其写个文章。但是在公司专门做了一个月的排行榜数据库设计。才发现问题根本没有看上去那么简单。甚至一篇文章都难以讲明白。不知自己误入歧途了，还是这个问题的确就是很复杂的。所以写个文章讲给大家，或许能有人一语道破。
一开始听到要设计一个排行榜，觉得很简单，一个外键加一个分数列，排名不保存在数据库中，每次查询都实时计算。不就得了？
接下来，就来讨论一下这种方案的可行性。先来描述一下经过最简化的基本要求：
1.       参与排行的设计用户量为1000万左右。
2.       并不要求实时，一小时更新一次。（我一开始的想法很天真，实时不是更好？所以才试了这个实时的排行榜）
3.       排行榜的结果要正确。（最废话的一条，其实很关键，直接导致实时方案作废。）
生产环境，数据库服务器：
CPU：双路4核，至强。
内存：32G。
开发、测试的环境：（以下运行时间数据基于此环境）
CPU：赛扬D 2.66G
内存：1G。
建表：
Create Table RealTimeCLB
(
UserId INT NOT NULL PRIMARY KEY,
Rating INT NOT NULL
)
放数据：一定要用Tran。
BEGIN TRAN
DECLARE @I as int
SET @I = 0
INSERTDATA:
INSERT RealTimeCLB VALUES(@I, RAND()*10000000)
SET @I = @I + 1
IF @I < 5000000
GOTO INSERTDATA
COMMIT TRAN
插入500万数据就用了16分钟，心里有点怵了。实时计算排名会不会慢呢？不管了，试试再说，反正真正的服务器很强大的说。注意Rating值是用随机数生成的。
为Rating列加索引：
CREATE INDEX IX_RealTimeCLB_Rating ON RealTimeCLB (Rating);
加索引又用了30秒。
查询：
SELECT TOP 100 *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB
用时0秒。很快啊。会不会影响并发的数据更新呢？
UPDATE RealTimeCLB SET Rating = Rating + RAND() * 1000 where UserId = 2
运行没有影响。
这里要解释一个问题。如果查询时，有更新操作，那查询出来的不就是脏的了吗？这个是可以接受了。更新晚于查询，再正常不过了。所以这个不是个问题。
但是如果世界就这么和谐了，也就不用研究一个月了。本文只是这一个月的第一天而已。
因为查询的方式多种多样。上面只查了前100名，很快。但是如果随便一个想查一下自己的名次呢？这也是必须要实现的基本功能。
查询指定用户的名次：
SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank]
FROM RealTimeCLB WHERE UserId = 1
如果你看到这里没有大叫，就说明你没有仔细看，或者至少对SQL不熟悉。因为上面的语句永远返回1。无论查谁，都是第1。
正确的SQL有很多写法，下面是其中一种：
SELECT * from
(SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB) AS d
WHERE d.UserId = 1
很不幸，这条语句用了4.5秒。如果用1000万用户的数据量，岂不是要10秒？如果你不知道为什么查询自己很慢，就找本书看看索引是如何运作的吧。这里我就不解释了。
也许我的SQL比较低效（你有快的吗？要实时计算。）。但是QQ和MSN之类用户已经有2亿了，如果那天也要做个迅雷样的排行榜。实时？那还了得？数据库服务器天天别干别的了，光排个名就排不过来了。
把Rank做为一列放进表里，查询不就快了？那更新不就慢了？更新一个人的分数，就要给一群人重新计算排名。你SQL写得好，在500万数据量上，也要5秒运行时间。
所以结论就是，排行榜，在大用户量和当前硬件环境下，是不可能实时的。
如果有人说，我们数据量很小，就10万用户，那总可以了吧？一次查询也就0.05秒，还可以了。听上去是可以了。SQL Server 2005提供的Rank函数，让按列计算排名快了很多。但是还是不行！因为上面的方法，无法保证最基本的一个需求，正确性！
可以不管查询出来的数据是旧的，但是一定要正确啊。但是上面的方案，不能保证查询结果的正确性！
而下面的解释，才是本文的重点部分。
回到查询语句
SELECT * from
(SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB) AS d
WHERE d.UserId = 1
UserId是外键，而且用来查询的UserId一定存在，但是就是这个语句会出问题，有看出什么问题吗？
问题就在于，这个语句返回的行数不确定！逻辑上，一个User一个Rank，但是这个语句，可能会返回两个或两个以上的结果行，甚至可能没有返回（即使UserId存在）。
出现的必要条件：
1.       在这个查询语句正确运行时，同时有数据更新。
2.       表上的Rating列建有索引。
表上有索引，就可能有这个问题，经过测试，如果把表上的索引删除，这个语句一定有一个返回行。
大家应该已经猜到问题的所在。在有索引的表上更新索引列，索引树为了保持平衡，就要同时改变索引数据的位置。如果同时有基于此索引的查询，就有可能因为索引节点在索引树上跳来跳去而遗漏或是重复读取一些节点。从而导致上面的问题。
解决方案1：查询时加表锁。既保证了正确性，又保证了时效性。但是查询的时候，就不能更新数据了。放弃。
解决方案2：不加索引。先把索引删除。
DROP INDEX IX_RealTimeCLB_Rating ON RealTimeCLB
那么在500万数据量下的查询速度如何呢？
SELECT TOP 100 *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB
要21秒。100万数据要4秒。基本上成正比。其实时间就是花在了排序上。所以运行时间基本上只和排序算法的效率相关。因为没有了索引，所以查询一个用户的时间也和这个差不多。如果你说你们只有几千用户量，还可以试下这个方法。
解决方案3：还是别实时了~~~~~，详见下回分解。
排行榜数据库设计与分析——为什么实时排行不可行?
很多网游中都有排行榜，这里就专门讨论一下这个排行榜背后的数据库设计。一开始我觉得这是一个基本的数据库设计问题。只需要有一个实体，没有实体间的关系，没有复杂的逻辑。网络上也搜索不到太多关于这类设计的问题，好像根本不值得为其写个文章。但是在公司专门做了一个月的排行榜数据库设计。才发现问题根本没有看上去那么简单。甚至一篇文章都难以讲明白。不知自己误入歧途了，还是这个问题的确就是很复杂的。所以写个文章讲给大家，或许能有人一语道破。
一开始听到要设计一个排行榜，觉得很简单，一个外键加一个分数列，排名不保存在数据库中，每次查询都实时计算。不就得了？
接下来，就来讨论一下这种方案的可行性。先来描述一下经过最简化的基本要求：
1.       参与排行的设计用户量为1000万左右。
2.       并不要求实时，一小时更新一次。（我一开始的想法很天真，实时不是更好？所以才试了这个实时的排行榜）
3.       排行榜的结果要正确。（最废话的一条，其实很关键，直接导致实时方案作废。）
生产环境，数据库服务器：
CPU：双路4核，至强。
内存：32G。
开发、测试的环境：（以下运行时间数据基于此环境）
CPU：赛扬D 2.66G
内存：1G。
建表：
Create Table RealTimeCLB
(
UserId INT NOT NULL PRIMARY KEY,
Rating INT NOT NULL
)
放数据：一定要用Tran。
BEGIN TRAN
DECLARE @I as int
SET @I = 0
INSERTDATA:
INSERT RealTimeCLB VALUES(@I, RAND()*10000000)
SET @I = @I + 1
IF @I < 5000000
GOTO INSERTDATA
COMMIT TRAN
插入500万数据就用了16分钟，心里有点怵了。实时计算排名会不会慢呢？不管了，试试再说，反正真正的服务器很强大的说。注意Rating值是用随机数生成的。
为Rating列加索引：
CREATE INDEX IX_RealTimeCLB_Rating ON RealTimeCLB (Rating);
加索引又用了30秒。
查询：
SELECT TOP 100 *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB
用时0秒。很快啊。会不会影响并发的数据更新呢？
UPDATE RealTimeCLB SET Rating = Rating + RAND() * 1000 where UserId = 2
运行没有影响。
这里要解释一个问题。如果查询时，有更新操作，那查询出来的不就是脏的了吗？这个是可以接受了。更新晚于查询，再正常不过了。所以这个不是个问题。
但是如果世界就这么和谐了，也就不用研究一个月了。本文只是这一个月的第一天而已。
因为查询的方式多种多样。上面只查了前100名，很快。但是如果随便一个想查一下自己的名次呢？这也是必须要实现的基本功能。
查询指定用户的名次：
SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank]
FROM RealTimeCLB WHERE UserId = 1
如果你看到这里没有大叫，就说明你没有仔细看，或者至少对SQL不熟悉。因为上面的语句永远返回1。无论查谁，都是第1。
正确的SQL有很多写法，下面是其中一种：
SELECT * from
(SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB) AS d
WHERE d.UserId = 1
很不幸，这条语句用了4.5秒。如果用1000万用户的数据量，岂不是要10秒？如果你不知道为什么查询自己很慢，就找本书看看索引是如何运作的吧。这里我就不解释了。
也许我的SQL比较低效（你有快的吗？要实时计算。）。但是QQ和MSN之类用户已经有2亿了，如果那天也要做个迅雷样的排行榜。实时？那还了得？数据库服务器天天别干别的了，光排个名就排不过来了。
把Rank做为一列放进表里，查询不就快了？那更新不就慢了？更新一个人的分数，就要给一群人重新计算排名。你SQL写得好，在500万数据量上，也要5秒运行时间。
所以结论就是，排行榜，在大用户量和当前硬件环境下，是不可能实时的。
如果有人说，我们数据量很小，就10万用户，那总可以了吧？一次查询也就0.05秒，还可以了。听上去是可以了。SQL Server 2005提供的Rank函数，让按列计算排名快了很多。但是还是不行！因为上面的方法，无法保证最基本的一个需求，正确性！
可以不管查询出来的数据是旧的，但是一定要正确啊。但是上面的方案，不能保证查询结果的正确性！
而下面的解释，才是本文的重点部分。
回到查询语句
SELECT * from
(SELECT *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB) AS d
WHERE d.UserId = 1
UserId是外键，而且用来查询的UserId一定存在，但是就是这个语句会出问题，有看出什么问题吗？
问题就在于，这个语句返回的行数不确定！逻辑上，一个User一个Rank，但是这个语句，可能会返回两个或两个以上的结果行，甚至可能没有返回（即使UserId存在）。
出现的必要条件：
1.       在这个查询语句正确运行时，同时有数据更新。
2.       表上的Rating列建有索引。
表上有索引，就可能有这个问题，经过测试，如果把表上的索引删除，这个语句一定有一个返回行。
大家应该已经猜到问题的所在。在有索引的表上更新索引列，索引树为了保持平衡，就要同时改变索引数据的位置。如果同时有基于此索引的查询，就有可能因为索引节点在索引树上跳来跳去而遗漏或是重复读取一些节点。从而导致上面的问题。
解决方案1：查询时加表锁。既保证了正确性，又保证了时效性。但是查询的时候，就不能更新数据了。放弃。
解决方案2：不加索引。先把索引删除。
DROP INDEX IX_RealTimeCLB_Rating ON RealTimeCLB
那么在500万数据量下的查询速度如何呢？
SELECT TOP 100 *, RANK() OVER (ORDER BY Rating DESC) AS [rank] FROM RealTimeCLB
要21秒。100万数据要4秒。基本上成正比。其实时间就是花在了排序上。所以运行时间基本上只和排序算法的效率相关。因为没有了索引，所以查询一个用户的时间也和这个差不多。如果你说你们只有几千用户量，还可以试下这个方法。
解决方案3：还是别实时了~~~~~，详见下回分解。
步步为营 .NET三层架构解析 二、数据库设计
要开发用户管理系统,我们首先要了解需求,现在就举一个简单需求,用户表,假设有两种角色用一个字段departID来判断,管理员和员工,
我们要先建一个用户表custom和一个部门表department:
view sourceprint?
CREATE TABLE [dbo].[custom](
[id] [int] IDENTITY(1,1) NOT NULL,
[cname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[departID] [int] NOT NULL,
[age] [int] NOT NULL,
[ename] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_custom] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
CREATE TABLE [dbo].[department](
[id] [int] IDENTITY(1,1) NOT NULL,
[departname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[description] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
建完数据库表后,开始写存储过程,插入一条数据:
view sourceprint?
CREATE PROCEDURE [dbo].[spInsertCustom]
@cname nvarchar(50),
@ename nvarchar(50),
@age int,
@departID int,
@password nvarchar(50)
AS
BEGIN
insert into custom(cname,departID,age,ename,password) values (@cname,@departID,@age,@ename,@password)
END
RETURN @@Identity
view sourceprint?
create PROCEDURE [dbo].[spInsertDepartment]
@departname nvarchar(50),
@description nvarchar(50)
AS
BEGIN
insert into department(departname,description)values(@departname,@description)
END
RETURN @@Identity
现建两个更新一条数据的存储过程:
view sourceprint?
CREATE PROCEDURE [dbo].[spupdatecustom]
@id int,
@cname nvarchar(50),
@departID int,
@age int,
@ename nvarchar(50),
@password nvarchar(50)
AS
BEGIN
update
custom
set
cname = @cname,
departID = @departID,
age = @age,
ename = @ename,
password = @password
where id = @id
END
COMMIT TRAN
view sourceprint?
create procedure spupdatedepart
(
@departname nvarchar(50),
@description nchar(10),
@id int
)
as
UPDATE [dbo].[department]
SET [departname] = @departname
,[description] = @departname
WHERE id=@id
再新建两个取出所有用户的存储过程:
view sourceprint?
CREATE PROCEDURE [dbo].[spGetcustom]
AS
BEGIN
select * from custom order by id desc
END
view sourceprint?
create PROCEDURE [dbo].[spGetAlldepartment]
AS
BEGIN
select * from department
END
再新建一个根据ID取出一条数据的存储过程:
view sourceprint?
CREATE PROCEDURE [dbo].[spGetcustomer]
@id int
AS
BEGIN
select * from custom where id = @id
END
现建一个根据部门名取部门ID的存储过程:
view sourceprint?
create PROCEDURE [dbo].[spGetdepartmenter]
@departname nvarchar(50)
AS
BEGIN
select * from department where departname = @departname
END
再建两个根据ID删除数据的存储过程:
view sourceprint?
create PROCEDURE [dbo].[spDeletecustom]
@id int
AS
BEGIN
delete custom where id = @id
END
view sourceprint?
CREATE PROCEDURE spdeletedepart
@id int
AS
BEGIN
delete department where id = @id
END
GO
数据库设计就建好了,这只是一个简单的示例.欢迎拍砖.
下次讲解SQLHelper的设计.
一个进销存数据库设计的例子
Posted on 2005-07-26 09:48快乐家++ 阅读(9427)评论(15)  编辑收藏
from:http://search.csdn.net/Expert/topic/763/763838.xml?temp=.9785883
CREATE TABLE user(
User_Id varchar(6),
User_Pwd varchar(8) NULL,
Again_Pwd varchar(8) NULL,
Bel_Group varchar(3) NULL,
Div_Type varchar(1) NULL,
User_Auth varchar(1) NULL,
Auth_Type varchar(1) NULL,
User_Status varchar(1) NULL,
Create_User varchar(6) NULL,
Create_Date varchar(7) NULL,
Create_Time varchar(6) NULL,
Appr_User varchar(6) NULL,
Appr_Date varchar(7) NULL,
Appr_Time varchar(6) NULL,
Pwd_Date varchar(7) NULL,
Err_Count float NULL,
Use_eJCIC varchar(1) NULL
)
CREATE TABLE Supplier  /*供应商表*/
(
Supplier_ID     int     IDENTITY(1,1)     NOT NULL, /* 供应商编号 ,主键 */
Name            varchar(250)              NOT NULL, /* 供应商名称 */
Address         varchar(250)              NOT NULL, /* 地址 */
Phone           varchar(25)               NULL,     /* 电话 */
Fax             varchar(25)               NULL,     /* 传真 */
PostalCode      varchar(10)               NULL,     /* 邮编 */
ConstactPerson  varchar(20)               NULL      /* 联系人 */
)
CREATE TABLE Customer   /* 客户表*/
(
Customer_ID     int    IDENTITY(1,1)      NOT NULL, /* 客户编号,主键*/
Name            varchar(250)              NOT NULL, /* 客户名称 */
Address         varchar(250)              NOT NULL, /* 地址 */
Phone           varchar(25)               NULL,     /* 电话 */
Fax             varchar(25)               NULL,     /* 传真 */
PostalCode      varchar(10)               NULL,     /* 邮编 */
ConstactPerson  varchar(20)               NULL      /* 联系人 */
)
CREATE TABLE Dept      /* 部门表 */
(
Dept_ID        int   IDENTITY(1,1)        NOT NULL, /* 部门编号,主键 */
Name           varchar(30)                NOT NULL, /* 名称 */
Remark           varchar(250)               NOT NULL/* 描述,备注 */
)
CREATE TABLE Dept_Supplier /* 部门--供应商表*/
(
Dept_ID       int                         NOT NULL,  /* 部门编号,主键 ,  外键( 参照 DEPT 表  )*/
Supplier_ID   int                         NOT NULL   /* 供应商编号 ,主键,外键( 参照 SUPPLIER 表) */
)
CREATE TABLE Dept_Customer /* 部门--客户表*/
(
Dept_ID       int                         NOT NULL, /* 部门编号,主键 ,  外键( 参照 DEPT 表  )*/
Customer_ID   int                         NOT NULL  /* 客户编号,主键,  外键( 参照 SUPPLIER 表) */
)
CREATE TABLE StoreHouse   /* 仓库表 */
(
StoreHouse_ID   int IDENTITY(1,1)         NOT NULL,  /* 仓库编号,主键 */
Address         varchar(250)              NOT NULL,  /* 地址 */
Phone           varchar(25)               NULL,      /* 电话 */
Employee_ID     INT                       NOT NULL,  /* 仓库保管 ,外键 ( 参照 EMPLOYEE 表 ) */
CreateDate      datetime                  NULL       /* 仓库成立时间 */
)
CREATE TABLE ProductClass  /* 商品总分类表 */
(
ProductClass_ID  int IDENTITY(1,1)        NOT NULL,  /* 商品总分类编号, 主键 */
Name             varchar(30)              NOT NULL,  /* 商品分类名称 */
Employee_ID      INT                      NOT NULL,  /* 建分类人 ,外键 ( 参照 EMPLOYEE 表 )*/
CreateDate       datetime                 NULL,      /* 建分类时间 */
Remark             varchar(250)             NULL,    /* 描述,备注 */
)
CREATE TABLE ProductList  /* 商品细分类表 */
(
ProductClass_ID  INT                      NOT NULL, /* 商品总分类编号, 外键 ( 参照PRODUCTCLASS 表 ) */
ProductList_ID   int IDENTITY(1,1)        NOT NULL, /* 商品细分类编号,主键 */
Name             varchar(30)              NOT NULL, /* 商品名称 */
Employee_ID      INT                      NOT NULL, /* 建分类人,外键 ( 参照 EMPLOYEE 表 )*/
CreateDate       datetime                 NULL,     /* 建分类时间 */
Remark             varchar(250)             NULL,   /* 描述 ,备注 */
)
CREATE TABLE ProductSpec  /* 商品规格表 */
(
ProductSpec_ID   INT IDENTITY(1,1)        NOT NULL, /* 商品规格编号,主键 */
Name             varchar(30)              NOT NULL, /* 商品规格名称 */
Employee_ID      INT                      NOT NULL, /* 操作员 ,外键 ( 参照 EMPLOYEE 表 )*/
CreateDate       datetime                 NULL,     /* 创建时间 */
Remark             varchar(250)             NULL    /* 描述,备注 */
)
CREATE TABLE ProductUnit /* 商品计量单位表 */
(
ProductUnit_ID   INT IDENTITY(1,1)        NOT NULL, /* 计量单位编号 ,主键 */
Name             varchar(30)              NOT NULL, /* 计量单位名称 */
Employee_ID      INT                      NOT NULL, /* 操作员 ,外键 ( 参照 EMPLOYEE 表 )*/
CreateDate       datetime                 NULL,     /* 创建时间 */
Remark             varchar(250)             NULL    /* 描述,备注 */
)
CREATE TABLE Product    /* 商品目录表 */
(
ProductList_ID   int                      NOT NULL,  /* 商品细分类编号, 外键 ( 参照 PRODUCTLIST 表 ) */
Product_ID       INT IDENTITY(1,1)        NOT NULL,  /* 商品名称编号, 主键 */
Name             varchar(30)              NOT NULL,  /* 商品名称 */
ProductSpec_ID   INT                      NOT NULL,  /* 商品规格, 外键 ( 参照 PRODUCTSPEC 表 ) */
ProductUnit_ID   INT                      NOT NULL,  /* 计量单位, 外键 ( 参照 PRODUCTUNIT 表 ) */
Price            MONEY                    NULL,      /* 参考价格 */
Employee_ID      INT                      NOT NULL,  /* 操作员,   外键 ( 参照 EMPLOYEE 表 )*/
CreateDate       datetime                 NULL,      /* 创建时间 */
Remark             varchar(250)             NULL     /* 描述,备注 */
)
CREATE TABLE Product_Supplier  /* 商品--供应商表 */
(
Product_ID       INT                      NOT NULL,   /* 商品名称编号,主键 , 外键( 参照 PRODUCT 表  )*/
Supplier_ID      INT                      NOT NULL    /* 供应商编号 , 主键,  外键( 参照 SUPPLIER 表) */
)
CREATE TABLE Employee  /* 员工表 */
(
Employee_ID      INT IDENTITY(1,1)        NOT NULL,  /* 员工编号 */
Dept_ID          INT                      NOT NULL,  /* 所属部门编号 */
Name             varchar(30)              NOT NULL,  /* 姓名 */
Duty             varchar(20)              NOT NULL,  /* 职务 */
Gender           varchar(6)               NOT NULL,  /* 性别 */
BirthDate        datetime                 NOT NULL,  /* 出生日期 */
HireDate         datetime                 NULL,      /* 合同签订 日期 */
MatureDate       datetime                 NULL,      /* 合同到期日 */
IdentityCard     varchar(20)              NULL,      /* 身份证号 */
Address          varchar(250)             NULL,      /* 住址 */
Phone            varchar(25)              NULL,      /* 电话 */
Email            varchar(30)              NULL       /* E_MAIL */
)
/*-///////////////////////////////////////////////////////////////////////////////////////-*/
CREATE TABLE BuyOrder    /* 进货合同 */
(
BuyOrder_ID     INT IDENTITY(1,1)        NOT NULL, /* 进货合同编号 , 主键 */
WriteDate       datetime                 NOT NULL, /* 合同签订日期  */
InsureDate      datetime                 NOT NULL, /* 合同生效日期  */
EndDate         datetime                 NOT NULL, /* 合同到期日期  */
Dept_ID         INT                      NOT NULL, /* 签订部门, 外键 ( 参照 DEPT 表 ) */
Supplier_ID     INT                      NOT NULL, /* 供应商,   外键 ( 参照 SUPPLIER 表 ) */
Employee_ID     INT                      NOT NULL  /* 合同主要负责人, 外键 ( 参照 EMPLOYEE 表) */
)
CREATE TABLE BuyOrder_Detail  /* 进货合同明细表 */
(
BuyOrder_ID     INT                      NOT NULL,  /* 进货合同编号,主键, 外键 ( 参照 BUYORDER 表 ) */
Product_ID      INT                      NOT NULL,  /* 所进商品编号,主键, 外键 (参照 PRODUCT 表 ) */
Quantity        INT                      NOT NULL,  /* 商品数量 */
Price           money                    NOT NULL   /* 商品进价 */
)
CREATE TABLE EnterStock    /* 入库单表 */
(
EnterStock_ID    INT IDENTITY(1,1)       NOT NULL, /* 入库单编号 , 主键 */
EnterDate        datetime                NOT NULL, /* 入库时间 */
Dept_ID          INT                     NOT NULL, /* 入库部门 ,外键 ( 参照 DEPT 表 )*/
StoreHouse_ID    INT                     NOT NULL, /* 所入仓库 ,外键 ( 参照 STOREHOUSE 表)*/
Employee_ID      INT                     NOT NULL  /* 入库人 ,  外键 ( 参照 EMPLOYEE 表)*/
/*需添加 仓库保管员如何来验证入库单 ?? */
)
CREATE TABLE EnterStock_Detail /* 入库单明细 */
(
EnterStock_ID    INT                     NOT NULL, /* 入库单编号 , 主键, 外键 (参照 ENTERSTOCK 表 )*/
Product_ID       INT                     NOT NULL, /* 此种商品编号,主键, 外键 (参照 PRODUCT 表 ) */
Quantity         int                     NOT NULL, /* 此种商品数量 */
Price            money                   NULL,     /* 此种商品参考价格  */
HaveInvoice      bit                     not null, /* 此种商品有没有开发票 ( 缺省为 0 , 有没有开票 )*/
InvoiceNum       varchar(30)             NULL      /* 发票号 */
)
CREATE TABLE BackStock  /* 退库单表 */
(
BackStock_ID     INT IDENTITY(1,1)       NOT NULL, /* 退库单编号 , 主键 */
BackDate         datetime                NOT NULL, /* 退库时间 */
Dept_ID          INT                     NOT NULL, /* 退库部门 ,  外键 ( 参照 DEPT 表 )*/
StoreHouse_ID    INT                     NOT NULL, /* 所退入仓库 ,外键 ( 参照 STOREHOUSE 表)*/
Employee_ID      INT                     NOT NULL, /* 退库人 ,    外键 ( 参照 EMPLOYEE 表)*/
Remark             varchar(250)            NULL    /* 退库原因 */
)
CREATE TABLE BackStock_Detail /* 退库单明细表 */
(
BackStock_ID     INT                     NOT NULL, /* 退库单编号 , 主键, 外键 (参照 BACKSTOCK 表 )*/
Product_ID       INT                     NOT NULL, /* 所退商品编号,主键, 外键 (参照 PRODUCT 表 ) */
Quantity         int                     NOT NULL, /* 退入数量 */
Price            money                   NULL      /* 参考价格 */
)
CREATE TABLE LeaveStock  /* 出库单表 */
(
LeaveStock_ID    INT IDENTITY(1,1)       NOT NULL,  /* 出库单编号 , 主键, 外键 (参照 LEAVESTOCK 表 )*/
LeaveDate        datetime                NOT NULL,  /* 出库时间 */
Dept_ID          INT                     NOT NULL,  /* 出库部门 ,  外键 ( 参照 DEPT 表 )*/
StoreHouse_ID    INT                     NOT NULL,  /* 所出仓库 ,外键 ( 参照 STOREHOUSE 表)*/
ToStoreHouse_ID  INT                     NOT NULL,  /* 所入仓库 ,外键 ( 参照 STOREHOUSE 表)*/
Employee_ID      INT                     NOT NULL   /* 出库人 ,    外键 ( 参照 EMPLOYEE 表)*/
/* 仓库保管员如何来验证出库单 ?? */
)
CREATE TABLE LeaveStock_Detail  /* 出库单明细表 */
(
LeaveStock_ID    INT                     NOT NULL,  /* 出库单编号 , 主键, 外键 (参照 BACKSTOCK 表 )*/
Product_ID       INT                     NOT NULL,  /* 所出商品编号,主键, 外键 (参照 PRODUCT 表 ) */
Quantity         int                     NOT NULL,  /* 出库数量 */
Price            money                   NULL       /* 出库价格 */
)
CREATE TABLE BackSale  /* 退货单表 */
(
BackSale_ID      INT IDENTITY(1,1)       NOT NULL,  /* 退货单编号 , 主键 */
BackDate         datetime                NOT NULL,  /* 退货日期  */
Dept_ID          INT                     NOT NULL,  /* 退货部门 ,  外键 ( 参照 DEPT 表 )*/
StoreHouse_ID    INT                     NOT NULL,  /* 退入仓库 ,  外键 ( 参照 STOREHOUSE 表)*/
Employee_ID      INT                     NOT NULL,  /* 退货人 ,    外键 ( 参照 EMPLOYEE 表)*/
Remark             varchar(250)            NULL     /* 退货原因 */
)
CREATE TABLE BackSale_Detail  /* 退货单明细表 */
(
BackSale_ID      INT                     NOT NULL,  /* 退货单编号 , 主键, 外键 (参照 BACKSTOCK 表 )*/
Product_ID       INT                     NOT NULL,  /* 所退商品编号,主键, 外键 (参照 PRODUCT 表 ) */
Quantity         int                     NOT NULL,  /* 退货数量 */
Price            money                   NULL       /* 价格 */
)
CREATE TABLE SaleOrder    /* 销售合同 */
(
SaleOrder_ID     INT IDENTITY(1,1)       NOT NULL,  /* 合同编号 , 主键 */
WriteDate        datetime                NOT NULL,  /* 合同签订日期  */
InsureDate       datetime                NOT NULL,  /* 合同生效日期  */
EndDate          datetime                NOT NULL,  /* 合同到期日期  */
Dept_ID          INT                     NOT NULL,  /* 签订部门, 外键 ( 参照 DEPT 表 ) */
Customer_ID      INT                     NOT NULL,  /* 客户编号, 外键 ( 参照 CUSTOMER 表 ) */
Employee_ID      INT                     NOT NULL   /* 合同主要负责人, 外键 ( 参照 EMPLOYEE 表) */
)
CREATE TABLE SaleOrder_Detail  /* 销售合同明细表 */
(
SaleOrder_ID     INT                     NOT NULL,  /* 销售合同编号,主键, 外键 ( 参照 BUYORDER 表 ) */
Product_ID       INT                     NOT NULL,  /* 销售商品编号,主键, 外键 (参照 PRODUCT 表 ) */
Quantity         int                     not null,  /* 商品数量 */
Price            money                   null       /* 商品进价 */
)
CREATE TABLE Buy     /* 进货表 ( 验货表 ) */
(
Buy_ID          INT IDENTITY(1,1)         NOT NULL, /* 进货编号 , 主键 */
ComeDate        datetime                  NOT NULL, /* 进货日期 */
Dept_ID         INT                       NOT NULL, /* 进货部门, 外键 ( 参照 DEPT 表 ) */
Employee_ID     INT                       NOT NULL  /* 验货人,   外键 ( 参照 EMPLOYEE 表)*/
)
CREATE TABLE Buy_Detail  /* 进货表明细 ( 验货表 ) */
(
Buy_ID           INT                      NOT NULL, /* 进货编号,主键, 外键 ( 参照 BUY 表 ) */
Product_ID       INT                      NOT NULL, /* 商品编号,主键, 外键 ( 参照 PRODUCT 表 ) */
BuyOrder_ID      INT                      NULL,     /* 采购合同,  外键 ( 参照 BUYORDER 表 ) */
Quantity         int                      not null, /* 数量 */
Price            money                    null      /* 价格 */
/* BUYORDER_ID 为 NULL 时, 为现金进货 */
)
CREATE TABLE Sale   /* 销售 表 */
(
Sale_ID          INT IDENTITY(1,1)        NOT NULL,  /* 销售 编号  */
SaleDate         datetime                 not null,  /* 销售 日期 */
Dept_ID          INT                      NOT NULL,  /* 销售部门, 外键 ( 参照 DEPT 表 ) */
Employee_ID      INT                      NOT NULL   /* 售货人,   外键 ( 参照 EMPLOYEE 表)*/
)
CREATE TABLE Sale_Detail  /* 销售明细 ( 验货表 ) */
(
Sale_ID          INT                      NOT NULL,  /* 销售编号,主键, 外键 ( 参照 SALE 表 ) */
Product_ID       INT                      NOT NULL,  /* 商品编号,主键, 外键 ( 参照 PRODUCT 表 ) */
SaleOrder_ID     INT                      NULL,      /* 销售合同, 外键 ( 参照 SALEORDER 表 ) */
Quantity         int                      not null,  /* 数量 */
Price            money                    not null,  /* 价格 */
Discount         int                      null       /* 折扣 */
/* SALEORDER_ID 为 NULL 时, 为现金销售 */
)
CREATE TABLE StockPile  /* 库存表 */
(
StockPile_ID     INT IDENTITY(1,1)        NOT NULL, /* 库存编号 , 主键 */
Dept_ID          INT                      NOT NULL, /* 商品所属部门, 外键 ( 参照 DEPT 表 ) */
StoreHouse_ID    INT                      NOT NULL, /* 所在仓库,     外键 ( 参照 SOTREHOUSE 表 ) */
Product_ID       INT                      NOT NULL, /* 商品编号,     外键 ( 参照 PRODUCT 表 ) */
FirstEnterDate   datetime                 not null, /* 此种商品第一次入库时间 */
LastLeaveDate    datetime                 null,     /* 此种商品最后一次出库时间 */
Quantity         int                      not null, /* 所存数量 */
Price            money                    not null  /* 加权价 */
/* LASTLEAVEDATE 为NULL 时,此种商品从来没有 卖过 */
)
SQL Server 数据库设计
一、数据库设计的必要性
在实际的软件项目中，如果系统中需要存储的数据量比较大，需要设计的表比较多，表与表之间的关系比较复杂，那我们就需要进行规范的数据库设置。如果不经过数据库的设计，我们构建的数据库不合理、不恰当，那么数据库的维护、运行效率会有很大的问题。这将直接影响到项目的运行性和可靠性。
二、什么是数据库设计
数据库设计实际上就是规划和结构化数据库中的数据对象以及这些数据对象之间的关系过程。
三、数据库设计的重要性
Ø 不经过设计的数据库或是设计糟糕的数据库很可能导致
1、 数据库运行效率地下
2、 更新、删除、添加数据出现问题
Ø 良好设计的数据库
1、 执行效率高
2、 使应用程序更便于开发
3、 扩展性好
4、 维护性好
四、数据模型
数据模型就像是数据间联系的一个轮廓图，整个模型就像一个框架。
如果按照记录间联系的表示方式，对数据模型进行分类，可以分为：层次模型、网状模型、关系模型。前两种又称为格式化数据模型。数据模型的好坏直接影响到数据库的性能，所以数据模型的选择是数据库设计的首要任务。
Ø 实体-关系（E-R）数据模型
E-R数据模型（Entity-Relationship data model），即实体-关系数据模型。E-R数据模型不同于传统的关系数据模型，它不是面向实现，而是面向现实物体的。
Ø 实体（Entity）
数据是用来描述现实中的物体的，而描述的对象都是形形色色的，有具体的、也有抽象的；有物理上存在的、也有概念性的。凡是可以互相区别而且可以被人们认识的事、物、概念等统统抽象为实体。多个相同的类型的实体可以称为实体集（Entity set）。因此，在E-R数据模型中，也有型与值之分；实体可以作为型来定义，每个实体可以是它的实例和值。
Ø 属性（Attribute）
实体一般具体若干特征，这些特征称为实体的属性。而每个属性都有自己的取值范围，在E-R数据模型中称为值集（value set）。在同一实体集中，每个实体的属性及其值集都是相同的，但可能取不同的值。属性对应数据库表的列。
Ø 关系（Relationship）
实体之间会有各种关系，这些关系抽象为联系。不但实体可以有属性，关系也可以有属性。
五、数据库设计步骤
Ø 数据库设计可以分为以下几个阶段
1、 需求分析阶段：分析客户的业务需求，特别是数据方面的需求
2、 概要设计阶段：绘制数据库的E-R图，并确认需求文档的正确性和完整性，E-R图是项目的设计人员、开发人员、测试人员，以及和客户进行沟通的重要凭据
3、 详细设计阶段：将概要设计阶段的E-R图转换为数据库表，进行逻辑设计，确定各个表之间的主外键关系，运用数据库的三范式进行审核，并进行技术评审。最后决定选哪种数据库（Oracle、SQLServer、MySQL）来建库、建表。
Ø 需求分析阶段：数据库系统分析
秀气分析阶段的重点是调查、收集、分析客户的业务数据需求以及数据的安全性、完整性需求等。
需求分析步骤：
1、 确认业务需求
2、 标识关系实体
3、 标识每个实体的具有的属性
4、 确认实体之间的关系
Ø 概要设计阶段：绘制E-R图
作为数据库设计者，你需要和项目组内其他成员分享你的设计思路，共同研讨数据库设计的合理性、安全性、完整性，并确认是否符合客户的业务需求。那么使用E-R图，这种图形化的表示方式最为直观。
* E-R图中的实体、属性和关系

上面的简单E-R图可以看出用户和收支之间的关系。在上图中可以看出：用矩形表示实体，实体是一般名词；椭圆表示属性，一般也是名词；菱形表示关系，一般是动词。
* 映射基数
映射基数表示可以通过关系与该实体的个数。对于实体集A和B之间的二元关系，可能的映射基数有：
1、 一对一：也就是A实体中最多只有一个B实体的关联，而B实体的最多只有一个A实体的关联。用E-R图表示：

2、 一对多：A实体可以与B实体任意数量的进行关联，B中的实体最多与A中的一个实体关联。E-R图表示：

3、 多对一：A实体最多与一个B实体进行关联，而B实体可以和任意多个A实体进行关联。E-R图表示：

4、 多对多：A实体可以有多个B实体，而B实体也可以有任意多个A实体。E-R图表示：

* E-R图
E-R图可以以图形化的方式将数据库的整个逻辑结构表示出来，组成部分有：
1、 矩形表示实体集
2、 椭圆表示属性
3、 菱形表示关系、
4、 直线用来连接实体集与属性、实体集和关系
5、 直线箭头表示实体集之间映射基数
Ø 详细设计阶段：将E-R图转换为表
步骤如下：
1、 将各个实体转换为对应的表，将各属性转换为对应的列
2、 标识每张表的主键
3、 将实体之间的关系转换为表与表之间的主外键关系
六、数据库设计规范化
Ø 数据库设计中经常出现的问题
1、 数据冗余大
2、 插入数据异常
3、 删除异常
4、 更新异常
Ø 规范设计
一个较好的关系数据库模型，它的每个关系中的属性一定要满足某种内在的语义条件，即要按一定的规范设计关系模型，这就是设计的规范化。
在数据库设计时，有一些专门的规则，称为数据库的设计范式，遵循这些规则，就可以创建出良好的数据库，数据库著名的三大范式理论：
1、 第一范式（1NF）
第一范式是满足关系数据库模型所要遵循的最基本的条件范式，几关系中的每个属性必须是不可再分的简单项，不能是属性组合，即属性的取值是不可拆分的原子值。
2、 第二范式（2NF）
第二范式是在第一范式的基础上，确保表中的每列都和主键相关。其定义是如果一个关系满足1NF，并且除了主键关系外的其他列都依赖于该主键，则满足第二范式。
3、 第三范式(3NF)
第三范式是在第二范式的基础上进行的，第三范式的目标是确保每列都和主键列直接相关，而不是间接相关的。其定义是：如果一个关系满足2NF，并且除主键外的其他列都不传递依赖于该主键。
Ø 规范化和性能关系
为了满足三大范式，数据库的性能可能会有一定程度的降低。所以，在实际数据库设计中，我们既要尽量满足三大范式，从而避免数据冗余和各种数据库的操作异常，同时也要考虑数据的访问性能。有时候，为了提高数据库的访问效率，适当的允许少量数据冗余咧存在，才是最适合的数据库设计方案。
AgileEAS.NET平台开发实例-药店系统-数据库设计
开篇
上篇，我们分析了，功能需求和非功能性的需求，本篇我们就来看下，如何设计数据库，当然数据库的设计也是有一些技巧性而已，不过大家经常做数据库设计的朋友都
知道，如果我们的数据库设计完全符合数据库要求的第三范式的话，那么我们可能在通过ORM框架来使用这个数据库设计的时候，会有不方便的地方，因为ORM在多表关联
上的处理或者说是支持的并不好，所以我们常常在数据库设计的时候，会允许在表中存在冗余字段，这样我们能够在查询的过程中可以很方便的读取数据，而不用关联查询，
当然也有不错的方案去处理这方面的需求，比如通过视图等。
下面我们就来看看，如果设计药店系统的数据库，后面我们会附上数据库设计文档的下载。
大纲
1、设计功能模块的数据库设计。
2、分析设计的合理性。
设计每个功能模块的数据库设计
我们本节将会根据上篇的需求文档来进行数据库的设计，来分析每个模块的数据库设计，我们采取的方式还是从整体上去考虑，我们在每个模块都要使用的公共的基础设
计等是否需要单独维护和管理等。
下面我们来分析下我们需要设计出的单独的表：

下面我们来分析下每个模块的数据库表的设计：
1、基础数据
药品字典：

当然上面的有些字段并没有完全的体现出来，具体的一些补充设计，大家可以基于这个基础之上进行完善和改进。
供应商：

供应商信息主要是为了药品的采购和退药提供帮助，有了供应商信息后，后期可以直接进行拨款操作，当然这是根据这种按消耗付给供应商钱成为可能，HIS医院系统
一般都会有这样的机制。

药品类型的维护，方便后期进行扩展和编辑等，一般不推荐直接删除药品类型，会造成数据的丢失和失效性，建议可设计启用禁用字段。
2、药品入库

药品入库一方面是需要引用药品字典表中的药品信息，还要保存供应商信息，同时还要引用药品类型。
3、药品出库(顾客发药)

这里的药品出库，就是药店系统中的顾客发药，里面需要保存顾客的姓名，发药日期，数量，等等，这样可以为后期的顾客退药提供帮助。
4、库存管理

库存管理，是所有模块的药品数据流的流入或流出。
5、供应商退药

供应商退药，主要是药店退药到供应商，记录药店退供应商的药品记录明细。
6、顾客退药

顾客退药。必须包含退药的顾客姓名和数量，日期等信息，方便后期的统计查询等，并详细记录明细信息。
7、药品调价

详细记录，调价原因，调价的药品数量，调价日期，新旧价格等信息。
8、药品报损

记录报损的数量，原因，日期等。
9、药品盘点

盘点表记录盘点的信息，账目库存与实际库存信息，记录盘点的日期，盘点记录的状态，是否进行过调整操作等。
药店系统目前涉及到的主要模块都已经涵盖，大家可以从上面的设计中看到很多的冗余信息，比如关于药品信息的内容，有很多的内容都冗余。主要是为了方便
基础信息的读取，防止过多的关联查询，当然通过视图也可以解决，但是还是会在编码中使用起来会有一定的不方便，当然，冗余字段会造成数据库物理存储太大，当然如果
说我们的数据的数量级很大的话，可能我们需要在设计的时候，考虑这个方面的要求，当我们的数据量不是太大的时候，可能我们更讲究效率优先。
设计分析
通过上面的数据库设计文档，我们发现了以下的几点，我们在每个表里面都保存了药品的如下几个字段的信息：

我们是否应该直接保存药品字典中的主键信息，然后其他表里面直接引用药品字典中的主键作为外键即可，那么可能有这样的一个好处，如果我们后期维护一个药品字
典后，信息变更后，所有的药品信息，都会跟着同步，但是也有一个不好处，也许我们有时候需要保留原有的药品字典信息时，用于跟踪历史记录时，就会出问题。而且。维
护时。我们必须要关联药品字典表，当然建立视图可以解决。
并且我们在设计数据库时，对于像基础数据这些信息时，一定要设计为不要轻易删除的字段，伪删除比较好，例如我们添加如下字段。

当然不删除会造成冗余数据太多，但是删除后会造成部分数据的信息错误，或者说无效的引用，那么我们如何来做，上面我们在每个表中都保存相关的药品信息，从侧面
也能避免删除基础数据时造成的无法找到引用的问题等。
数据库设计的过程中还有很多的其他的技巧，把二个都纵向变化的因素，我们会单独通过一个关联表来维护这二个都变化的因素之间的关联关系。具体的应用还有很多，
当然我的水平也是有限，不一定是很有道理，欢迎大家拍砖，如果部分内容有错误或者不正确，还请大家指正。
总结
本文主要给出了药店系统的数据库设计，当然可能我的数据库设计不符合正常的实际使用，不足之处还请大家多多指点，如果你有更好的设计的思路，还请指出，我会不
断的改进。
药店系统-数据库设计文档http://www.agilelab.cn/downloads/fx/AgileEAS.NET平台开发实例-药店系统-数据库设计文档.pdf
数据库设计14个注意事项
1. 原始单据与实体之间的关系
可以是一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对
应且只对应一个实体。在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单证对应多个实
体，或多张原始单证对应一个实体。这里的实体可以理解为基本表。明确这种对应关系后，对我们设计
录入界面大有好处。
〖例1〗：一份员工履历资料，在人力资源信息系统中，就对应三个基本表：员工基本情况表、社会
关系表、工作简历表。这就是“一张原始单证对应多个实体”的典型例子。
2. 主键与外键
一般而言，一个实体不能既无主键又无外键。在E—R 图中, 处于叶子部位的实体, 可以定义主键，
也可以不定义主键(因为它无子孙), 但必须要有外键(因为它有父亲)。
主键与外键的设计，在全局数据库的设计中，占有重要地位。当全局数据库的设计完成以后，有个
美国数据库设计专家说：“键，到处都是键，除了键之外，什么也没有”，这就是他的数据库设计经验
之谈，也反映了他对信息系统核心(数据模型)的高度抽象思想。因为：主键是实体的高度抽象，主键与
外键的配对，表示实体之间的连接。
3. 基本表的性质
基本表与中间表、临时表不同，因为它具有如下四个特性：
(1) 原子性。基本表中的字段是不可再分解的。
(2) 原始性。基本表中的记录是原始数据（基础数据）的记录。
(3) 演绎性。由基本表与代码表中的数据，可以派生出所有的输出数据。
(4) 稳定性。基本表的结构是相对稳定的，表中的记录是要长期保存的。
理解基本表的性质后，在设计数据库时，就能将基本表与中间表、临时表区分开来。
4. 范式标准
基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是
最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间
的目的。
〖例2〗：有一张存放商品的基本表，如表1所示。“金额”这个字段的存在，表明该表的设计不满
足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加
“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。
在Rose 2002中，规定列有两种类型：数据列和计算列。“金额”这样的列被称为“计算列”，而“
单价”和“数量”这样的列被称为“数据列”。
表1 商品表的表结构
商品名称 商品型号 单价 数量 金额
电视机 29吋 2,500 40 100,000
5. 通俗地理解三个范式
通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就
必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：
第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余
。
没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运
行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式
，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
6. 要善于识别与正确处理多对多的关系
若两个实体之间存在多对多的关系，则应消除这种关系。消除的办法是，在两者之间增加第三个实
体。这样，原来一个多对多的关系，现在变为两个一对多的关系。要将原来两个实体的属性合理地分配
到三个实体中去。这里的第三个实体，实质上是一个较复杂的关系，它对应一张基本表。一般来讲，数
据库设计工具不能识别多对多的关系，但能处理多对多的关系。
〖例3〗：在“图书馆信息系统”中，“图书”是一个实体，“读者”也是一个实体。这两个实体之
间的关系，是一个典型的多对多关系：一本图书在不同时间可以被多个读者借阅，一个读者又可以借多
本图书。为此，要在二者之间增加第三个实体，该实体取名为“借还书”，它的属性为：借还时间、借
还标志(0表示借书，1表示还书)，另外，它还应该有两个外键(“图书”的主键，“读者”的主键)，使
它能与“图书”和“读者”连接。
7. 主键PK的取值方法
PK是供程序员使用的表间连接工具，可以是一无物理意义的数字串, 由程序自动加1来实现。也可
以是有物理意义的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时，建议字段的个
数不要太多，多了不但索引占用空间大，而且速度也慢。
8. 正确认识数据冗余
主键与外键在多表中的重复出现, 不属于数据冗余，这个概念必须清楚，事实上有许多人还不清楚
。非键字段的重复出现, 才是数据冗余！而且是一种低级冗余，即重复性的冗余。高级冗余不是字段的
重复出现，而是字段的派生出现。
〖例4〗：商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出
来的，它就是冗余，而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数
据的不一致性，因为同一数据，可能从不同时间、地点、角色上多次录入。因此，我们提倡高级冗余(派
生性冗余)，反对低级冗余(重复性冗余)。
9. E--R图没有标准答案
信息系统的E--R图没有标准答案，因为它的设计与画法不是惟一的，只要它覆盖了系统需求的业务
范围和功能内容，就是可行的。反之要修改E--R图。尽管它没有惟一的标准答案，并不意味着可以随意
设计。好的E—R图的标准是：结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余。
10. 视图技术在数据库设计中很有用
与基本表、代码表、中间表不同，视图是一种虚表，它依赖数据源的实表而存在。视图是供程序员
使用数据库的一个窗口，是基表数据综合的一种形式, 是数据处理的一种方法，是用户数据保密的一种
手段。为了进行复杂处理、提高运算速度和节省存储空间, 视图的定义深度一般不得超过三层。 若三层
视图仍不够用, 则应在视图上定义临时表, 在临时表上再定义视图。这样反复交迭定义, 视图的深度就
不受限制了。
对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统，视图的作用更加重要。这些
系统的基本表完成物理设计之后，立即在基本表上建立第一层视图，这层视图的个数和结构，与基本表
的个数和结构是完全相同。并且规定，所有的程序员，一律只准在视图上操作。只有数据库管理员，带
着多个人员共同掌握的“安全钥匙”，才能直接在基本表上操作。请读者想想：这是为什么？
11. 中间表、报表和临时表
中间表是存放统计数据的表，它是为数据仓库、输出报表或查询结果而设计的，有时它没有主键与
外键(数据仓库除外)。临时表是程序员个人设计的，存放临时记录，为个人所用。基表和中间表由DBA维
护，临时表由程序员自己用程序自动维护。
12. 完整性约束表现在三个方面
域的完整性：用Check来实现约束，在数据库设计工具中，对字段的取值范围进行定义时，有一个Ch
eck按钮，通过它定义字段的值城。
参照完整性：用PK、FK、表级触发器来实现。
用户定义完整性：它是一些业务规则，用存储过程和触发器来实现。
13. 防止数据库设计打补丁的方法是“三少原则”
(1) 一个数据库中表的个数越少越好。只有表的个数少了，才能说明系统的E--R图少而精，去掉了
重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计；
(2) 一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的
外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间；
(3) 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且
很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去
，在主表中留下许多空余的字段。所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一
个子表。这个方法很简单，有的人就是不习惯、不采纳、不执行。
数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概
念，综合观点，不能孤立某一个原则。该原则是相对的，不是绝对的。“三多”原则肯定是错误的。试
想：若覆盖系统同样的功能，一百个实体(共一千个属性) 的E--R图，肯定比二百个实体(共二千个属性)
的E--R图，要好得多。
提倡“三少”原则，是叫读者学会利用数据库设计技术进行系统的数据集成。数据集成的步骤是将
文件系统集成为应用数据库，将应用数据库集成为主题数据库，将主题数据库集成为全局综合数据库。
集成的程度越高，数据共享性就越强，信息孤岛现象就越少，整个企业信息系统的全局E—R图中实体的
个数、主键的个数、属性的个数就会越少。
提倡“三少”原则的目的，是防止读者利用打补丁技术，不断地对数据库进行增删改，使企业数据
库变成了随意设计数据库表的“垃圾堆”，或数据库表的“大杂院”，最后造成数据库中的基本表、代
码表、中间表、临时表杂乱无章，不计其数，导致企事业单位的信息系统无法维护而瘫痪。
“三多”原则任何人都可以做到，该原则是“打补丁方法”设计数据库的歪理学说。“三少”原则
是少而精的原则，它要求有较高的数据库设计技巧与艺术，不是任何人都能做到的，因为该原则是杜绝
用“打补丁方法”设计数据库的理论依据。
14. 提高数据库运行效率的办法
在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：
(1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。
(2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以
文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。
(3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，
以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过
八十个，则垂直分割该表，将原来的一个表分解为两个表。
(4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
(5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化
，这三个层次上同时下功夫。
上述十四个技巧，是许多人在大量的数据库分析与设计实践中，逐步总结出来的。对于这些经验的
运用，读者不能生帮硬套，死记硬背，而要消化理解，实事求是，灵活掌握。并逐步做到：在应用中发
展，在发展中应用。
租车信息系统数据库设计（5）
2010-12-26 19:40 by DBFocus, 1641 visits,收藏,编辑
前篇回顾
从租车信息系统数据库设计（1）至租车信息系统数据库设计（4）我们完成了一个简单的租车信息系统的数据库设计。
从功能上来讲还有很多可以扩展的方面，如权限管理、发票管理等等，本文不将展开。大家可以对这些需求进行设想，设计相应的表、字段和关联，并融合到整体设计中。
本篇是本系列的最后一篇，我们将利用先前设计的数据库结构来写一些查询，完成一些业务需求，同时也反过来审视先前的设计。
获取需要催促还车的订单
我们的业务人员每天都要获取超出预订期限未还车的订单。对于这些订单，业务人员需要一一电话客户。
那就让我们来帮助业务人员写这个查询吧！
view sourceprint?
01 select
02     RentalOrder.Order_ID
03 from
04     Table_Order RentalOrder
05 where
06     RentalOrder.Order_BookEndDate < GETDATE()
07     and
08     OrderStatus_ID = (select
09                         OrderStatus.OrderStatus_ID
10                     from
11                         Table_OrderStatus OrderStatus
12                     where
13                         OrderStatus.OrderStatus_Name = 'Inuse')
这个查询把订单预订终止日期比当前时间早，但订单状态还是‘Inuse’的订单好找了出来（订单状态种类请参见租车信息系统数据库设计（2））。
注：1. 此查询中有一个子查询，大家可以直接使用查出来的OrderStatus_ID代替之，这样对性能有好处。此处是为了增加可读性。
2. 此查询仅输出了Order_ID，大家可根据需要连接相应的表，输出需要的字段。
顾客预订用车
当某顾客决定要租车时，他会在租车网站上选定某型号的车、哪个门店、某个时间段，并点击预订。
此时系统要查找指定门店的该车型车辆在相应时间是否有档期，我们分步骤来写这个查询。
查询的输入为：
1. 车型ID：@CarCategory_ID
2. 门店ID：@Store_ID
3. 租用起始日期：@StartDate
4. 租用结束日期：@EndDate
查询的输出为：
符合条件的车辆ID：Car_ID
view sourceprint?
01 --1 选出指定车型（Category），指定门店（Store），且车辆状态为Ready和Inuse的车辆
02 select
03     Car_ID
04 into
05     #CandidateCars1
06 from
07     Table_Car
08 where
09     CarCategory_ID = @CarCategory_ID
10     and
11     Store_ID = @Store_ID
12     and
13     CarStatus_ID in (select
14                         CarStatus_ID
15                     from
16                         Table_CarStatus
17                     where
18                         CarStatus_Name in ('Ready', 'Inuse'));
注：1. 我们把第一步的查询结果放到临时表中，供下一步使用。
2. 车辆状态的枚举值请参见租车信息系统数据库设计（1）。车辆状态为Inuse表示此时该车正在被租用，不在车库中（但对于未来某段时间的预订，该车可能会有档期）。车辆状态和订单状态都有一个Inuse的枚举值，请不要混淆。
3. 此查询中的子查询可以用查出的ID值代替。
view sourceprint?
01
到第二步结束我们的结果集中可能会有多辆符合条件的车。那到底选哪一辆车最合适呢？
我首先想到的是尽可能把车辆的档期排满。那就是一辆一辆的来排，第一辆排不下了，再排到第二辆，这样也许能使车辆的利用率最高。
但这种方案很快就被我排除了，原因有3个方面：
1. 这种方式会使某些车辆的租用很稠密，而另一些车可能一个订单都没有。
2. 某些车太稠密的订单容易产生意外情况，如前一个租车的顾客还车晚了，造成后面的顾客无法领到订单中的车辆。这种情况虽然可以通过临时更改车辆，但需要人为处理的意外境况增多了，且还需要修改原先的订单中的车辆信息。
3. 某些车太稠密的订单又会使某些车辆的使用率特别高，造成其维修率和折损率也特别高。
所以在第三步选车策略的目标是使每辆车的租用尽可能平均，车辆的租用间隔稀疏一些。相应的选车策略可以有：
1. 选从当前时间开始，被预订的时间总和最少的那辆车
2. 选从当前时间开始，被预订次数最少的那辆车
3. 选之前预订时间段与本次预订时间段前后间隔最大的那辆车
4. 随机选择一辆车
这几种策略哪个最好，我很难说，可能需要进行一些实验。大家可以思考讨论。
在这里我选择第3个策略来写查询。第3个策略相对于其他的策略从文字上稍难理解一些，我先来看一个图示：

图中是某辆车的预订时间轴，我希望本次预订期与之前的预订期之间的间隔尽可能大，这样当某顾客还车迟了时就能有多一些的缓冲时间。
要用SQL实现这一策略，我想到了2种算法：
1. 把前后间隔时间求和后倒序排列，取Top 1的记录
2. 先按与‘之前预订期1’的间隔时间倒序排列后取Top 2的2条记录，再拿这2条记录按与‘之前预订期2’的间隔时间倒序排列后取Top 1的记录
我最后选择了算法1来写SQL语句
view sourceprint?
01 --3 在候选车辆中，选之前预订时间段与本次预订时间段前后间隔最大的那辆车
02 With CandidateCarsWithDate as
03 (
04     select
05         CandidateCars.Car_ID,
06         (select
07             MAX(RentalOrder1.Order_BookEndDate)
08         from
09             Table_Order RentalOrder1
10         where
11             RentalOrder1.Car_ID = CandidateCars.Car_ID
12             and
13             RentalOrder1.Order_BookEndDate < @StartDate) MaxBeforeDate,
14         (select
15             MIN(RentalOrder2.Order_BookStartDate)
16         from
17             Table_Order RentalOrder2
18         where
19             RentalOrder2.Car_ID = CandidateCars.Car_ID
20             and
21             RentalOrder2.Order_BookStartDate > @EndDate) MinAfterDate
22     from
23         #CandidateCars2 CandidateCars
24 )
25 select top(1)
26     Car_ID
27 from
28     CandidateCarsWithDate
29 order by
30     DATEDIFF(day, ISNULL(MaxBeforeDate, '1900-01-01'), ISNULL(MinAfterDate, '9999-01-01')) desc;
我使用了CTE表达式来写这个查询，语句在SQL Server 2008中测试通过。
注：1. With中的查询包含了两个子查询，分别获得比本次预订起始日期早的最大的终止日期和比本次预订终止日期晚的最小的起始日期。
2. 在With子查询下的select语句，按照With中计算得到的MinAfterDate和MaxBeforeDate的差值降序排列（按照算法描述，应该减去本次预订的间隔天数，但这不会影响排序结果），并取出Top 1的车辆ID。
3. 最后一行使用ISNULL的原因是在本次预订之前或之后可能没有其他预订，故取了两个特别的日期。
至此我们得到了符合条件的车辆ID了。
总结与思考
本篇我们设想了2个需求，并撰写了查询。‘获取需要催促还车的订单’较为简单一些，我就不多说什么了。对于‘顾客预订用车’相对复杂些，我再多说两句。
1. 对于‘顾客预订用车’，我为了把逻辑清晰分成了3小段，使用了2个临时表。大家可以考虑把这些逻辑合在一起，写成一个CTE或使用嵌套子查询（如果大家使用临时表，记得在最后把这些表drop掉）。
2. 大家可以再进一步加入用户验证和生成Order的逻辑后封装成存储过程供应用程序端调用。
3. 本文中选用的策略和算法未必是最优的，大家可以进一步思考、探索与分享。
4. 本文为了逻辑清晰，写出的查询性能并不是很好，有很多可以提升的地方。大家在把三段查询合并的时候可以从全局进行考虑来调校查询性能。
后记
非常感谢“es潇潇”的反馈。原文第二部分第二段的SQL查询中，原先使用的是内连接，这会使新购置的车辆无法被预订到。这是本文代码的疏漏，对原文的SQL语句已进行修正。
“es潇潇”的问题又给了我两个方面的思考：
1. 我们的门店可以根据地域分成组，当在一个门店无法预订到指定车型时，可以在同一组的其他门店查找合适车辆进行预订。对于在一个区域中的门店可以通过内部调车来满足顾客的需求。这样的改动对于顾客来说又是完全透明的。要做这样的改动就需要增加新表，并修改原先的查询逻辑，大家可以进一步思考。
2. “当我们有一把锤子时，看到的任何东西都像钉子”。对于我来说我的锤子就是SQL了，所以我把选车的逻辑放在了SQL查询中。这样是否好呢？是否把候选车辆集返回给前台应用服务器来做逻辑筛选更好呢？这里要权衡两个资源要素。SQL端进行筛选，损失了数据库的CPU等资源，但数据库到应用服务器间的网络负载减轻了，在Application进行筛选就正好反过来。如何进行选择呢？
大家的反馈印证了，集众智而有所进这句话。再次感谢大家的支持与建议。
数据库设计原则
作者: zyf0808 发表日期: 2006-03-26 10:02 文章属性:转载复制链接
数据库设计
实现sql server数据库的优化，首先要有一个好的数据库设计方案。在实际工作中，许多sql server方案往往是由于数据库设计得不好导致性能很差。实现良好的数据库设计必须考虑这些问题:
1. 逻辑数据库规范化问题
一般来说，逻辑数据库设计会满足规范化的前3级标准：
第1规范：没有重复的组或多值的列;
第2规范: 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分;
第3规范: 一个非关键字段不能依赖于另一个非关键字段。
遵守这些规则的数据库设计会产生较少的列和更多的表，因而也就减少了数据冗余，也减少了用于存储数据的页。
2. 生成物理数据库
要想正确选择基本物理实现策略，必须了解和利用好数据库访问格式和硬件资源的操作特点，特别是内存和磁盘子系统i/o。以下是一些常用技巧：
与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段可以被更快地读取，而且可以在一个数据页上放置更多的数据行，因而也就减少了i/o操作。
把一个表放在某个物理设备上，再通过sql server的段把它的不分簇索引放在一个不同的物理设备上，这样能提高性能。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做的好处更加明显。
用sql server段把一个频繁使用的大表分割开，并放在多个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性能。
用sql server段把文本或图像列的数据存放在一个单独的物理设备上可以提高性能。一个专用的智能型的控制器能进一步提高性能。
应用系统设计
在应用系统的设计中，要着重考虑以下几点：
1．合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是提高查询效率。索引的使用要恰到好处，其使用原则如下：
在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引；在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引；在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。 如果待排序的列有多个，可以在这些列上建立复合索引。
2. 避免或简化排序
应当尽量简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序这个步骤。为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。
3．消除对大型表行数据的顺序存取
在嵌套查询中，表的顺序存取对查询效率可能产生致命的影响。我们有时可以使用并集来避免顺序存取。尽管也许在所有的检查列上都有索引，但某些形式的where子句会强迫优化器使用顺序存取，这一点也应注意。
4. 避免相关子查询
如果一个列同时在主查询和where子句中出现，很可能当主查询中的列值改变之后，子查询必须重新查询一次。而且查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
5．避免困难的正规表达式
mathes和like关键字支持通配符匹配，但这种匹配特别耗时。例如：select * from customer where zipcode like “98_ _ _”，即使在zipcode字段上已建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为：select * from customer where zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。
6．使用临时表加速查询
把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘i/o，所以查询工作量可以得到大幅减少。但要注意，临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。
操作系统相关优化
操作系统性能的好坏直接影响数据库的使用性能，如果操作系统存在问题，如cpu过载、过度内存交换、磁盘i/o瓶颈等，在这种情况下，单纯进行数据库内部性能调整是不会改善系统性能的。我们可以通过windows nt的系统监视器(system monitor)来监控各种设备，发现性能瓶颈。
cpu 一种常见的性能问题就是缺乏处理能力。系统的处理能力是由系统的cpu数量、类型和速度决定的。如果系统没有足够的cpu处理能力，它就不能足够快地处理事务以满足需要。我们可以使用system monitor确定cpu的使用率，如果以75%或更高的速率长时间运行，就可能碰到了cpu瓶颈问题，这时应该升级cpu。但是升级前必须监视系统的其他特性，如果是因为sql语句效率非常低，优化语句就有助于解决较低的cpu利用率。而当确定需要更强的处理能力，可以添加cpu或者用更快的cpu 替换。
内存 sql server可使用的内存量是sql server性能最关键因素之一。而内存同i/o子系统的关系也是一个非常重要的因素。例如，在i/o操作频繁的系统中，sql server用来缓存数据的可用内存越多，必须执行的物理i/o也就越少。这是因为数据将从数据缓存中读取而不是从磁盘读取。同样，内存量的不足会引起明显的磁盘读写瓶颈，因为系统缓存能力不足会引起更多的物理磁盘i/o。
可以利用system monitor检查sql server的buffer cache hit ratio计数器，如果命中率经常低于90%，就应该添加更多的内存。
i/o子系统 由i/o子系统发生的瓶颈问题是数据库系统可能遇到的最常见的同硬件有关的问题。配置很差的i/o子系统引起性能问题的严重程度仅次于编写很差的sql语句。i/o子系统问题是这样产生的，一个磁盘驱动器能够执行的i/o操作是有限的，一般一个普通的磁盘驱动器每秒只能处理85次i/o操作，如果磁盘驱动器超载，到这些磁盘驱动器的i/o操作就要排队，sql的i/o延迟将很长。这可能会使锁持续的时间更长，或者使线程在等待资源的过程中保持空闲状态，其结果就是整个系统的性能受到影响。
解决i/o子系统有关的问题也许是最容易的，多数情况下，增加磁盘驱动器就可以解决这个性能问题。
当然，影响性能的因素很多，而应用又各不相同，找出一个通用的优化方案是很困难的，只能是在系统开发和维护的过程中针对运行的具体情况，不断加以调整。
大型数据库设计
随着计算机技术越来越广泛地应用于国民经济的各个领域，在计算机硬件不断微型化的同时，应用系统向着复杂化、大型化的方向发展。数据库是整个系统的核心，它的设计直接关系系统执行的效率和系统的稳定性。因此在软件系统开发中，数据库设计应遵循必要的数据库范式理论，以减少冗余、保证数据的完整性与正确性。只有在合适的数据库产品上设计出合理的数据库模型，才能降低整个系统的编程和维护难度，提高系统的实际运行效率。虽然对于小项目或中等规模的项目开发人员可以很容易地利用范式理论设计出一套符合要求的数据库，但对于一个包含大型数据库的软件项目，就必须有一套完整的设计原则与技巧。
一、成立数据小组
大型数据库数据元素多，在设计上有必要成立专门的数据小组。由于数据库设计者不一定是使用者，对系统设计中的数据元素不可能考虑周全，数据库设计出来后，往往难以找到所需的库表，因此数据小组最好由熟悉业务的项目骨干组成。
数据小组的职能并非是设计数据库，而是通过需求分析，在参考其他相似系统的基础上，提取系统的基本数据元素，担负对数据库的审核。审核内容包括审核新的数据库元素是否完全、能否实现全部业务需求；对旧数据库（如果存在旧系统）的分析及数据转换；数据库设计的审核、控制及必要调整。
二、设计原则
１．规范命名。所有的库名、表名、域名必须遵循统一的命名规则，并进行必要说明，以方便设计、维护、查询。
２．控制字段的引用。在设计时，可以选择适当的数据库设计管理工具，以方便开发人员的分布式设计和数据小组的集中审核管理。采用统一的命名规则，如果设计的字段已经存在，可直接引用；否则，应重新设计。
３．库表重复控制。在设计过程中，如果发现大部分字段都已存在，开发人员应怀疑所设计的库表是否已存在。通过对字段所在库表及相应设计人员的查询，可以确认库表是否确实重复。
４．并发控制。设计中应进行并发控制，即对于同一个库表，在同一时间只有一个人有控制权，其他人只能进行查询。
５．必要的讨论。数据库设计完成后，数据小组应与相关人员进行讨论，通过讨论来熟悉数据库，从而对设计中存在的问题进行控制或从中获取数据库设计的必要信息。
６．数据小组的审核。库表的定版、修改最终都要通过数据小组的审核，以保证符合必要的要求。
７．头文件处理。每次数据修改后，数据小组要对相应的头文件进行修改（可由管理软件自动完成），并通知相关的开发人员，以便进行相应的程序修改。
三、设计技巧
１．分类拆分数据量大的表。对于经常使用的表（如某些参数表或代码对照表），由于其使用频率很高，要尽量减少表中的记录数量。例如，银行的户主账表原来设计成一张表，虽然可以方便程序的设计与维护，但经过分析发现，由于数据量太大，会影响数据的迅速定位。如果将户主账表分别设计为活期户主账、定期户主账及对公户主账等，则可以大大提高查询效率。
２．索引设计。对于大的数据库表，合理的索引能够提高整个数据库的操作效率。在索引设计中，索引字段应挑选重复值较少的字段；在对建有复合索引的字段进行检索时，应注意按照复合索引字段建立的顺序进行。例如，如果对一个５万多条记录的流水表以日期和流水号为序建立复合索引，由于在该表中日期的重复值接近整个表的记录数，用流水号进行查询所用的时间接近３秒；而如果以流水号为索引字段建立索引进行相同的查询，所用时间不到１秒。因此在大型数据库设计中，只有进行合理的索引字段选择，才能有效提高整个数据库的操作效率。
３．数据操作的优化。在大型数据库中，如何提高数据操作效率值得关注。例如，每在数据库流水表中增加一笔业务，就必须从流水控制表中取出流水号，并将其流水号的数值加一。正常情况下，单笔操作的反应速度尚属正常，但当用它进行批量业务处理时，速度会明显减慢。经过分析发现，每次对流水控制表中的流水号数值加一时都要锁定该表，而该表却是整个系统操作的核心，有可能在操作时被其他进程锁定，因而使整个事务操作速度变慢。对这一问题的解决的办法是，根据批量业务的总笔数批量申请流水号，并对流水控制表进行一次更新，即可提高批量业务处理的速度。另一个例子是对插表的优化。对于大批量的业务处理，如果在插入数据库表时用普通的Ｉｎｓｅｒｔ语句，速度会很慢。其原因在于，每次插表都要进行一次Ｉ/Ｏ操作，花费较长的时间。改进后，可以用Ｐｕｔ语句等缓冲区形式等满页后再进行Ｉ/Ｏ操作，从而提高效率。对大的数据库表进行删除时，一般会直接用Ｄｅｌｅｔｅ语句，这个语句虽然可以进行小表操作，但对大表却会因带来大事务而导致删除速度很慢甚至失败。解决的方法是去掉事务，但更有效的办法是先进行Ｄｒｏｐ操作再进行重建。
４．数据库参数的调整。数据库参数的调整是一个经验不断积累的过程，应由有经验的系统管理员完成。以Ｉｎｆｏｒｍｉｘ数据库为例，记录锁的数目太少会造成锁表的失败；逻辑日志的文件数目太少会造成插入大表失败等，这些问题都应根据实际情况进行必要的调整。
５．必要的工具。在整个数据库的开发与设计过程中，可以先开发一些小的应用工具，如自动生成库表的头文件、插入数据的初始化、数据插入的函数封装、错误跟踪或自动显示等，以此提高数据库的设计与开发效率。
６．避免长事务。对单个大表的删除或插入操作会带来大事务，解决的办法是对参数进行调整，也可以在插入时对文件进行分割。对于一个由一系列小事务顺序操作共同构成的长事务（如银行交易系统的日终交易），可以由一系列操作完成整个事务，但其缺点是有可能因整个事务太大而使不能完成，或者，由于偶然的意外而使事务重做所需的时间太长。较好的解决方法是，把整个事务分解成几个较小的事务，再由应用程序控制整个系统的流程。这样，如果其中某个事务不成功，则只需重做该事务，因而既可节约时间，又可避免长事务。
７．适当超前。计算机技术发展日新月异，数据库的设计必须具有一定前瞻性，不但要满足当前的应用要求，还要考虑未来的业务发展，同时必须有利于扩展或增加应用系统的处理功能。
相对于中小型数据库，大型数据库的设计与开发要复杂得多，因此在设计、开发过程中，除了要遵循数据库范式理论、增加系统的一致性和完整性外，还要在总体上根据具体情况进行分布式设计，紧紧把握集中控制、统一审核的基本原则，保证数据库设计结构紧凑、分布平衡、定位迅速。在数据库操作上，要采用一定的技巧提高整个应用系统的执行效率，并注意适当超前，以适应不断变化的应用及系统发展的要求。
数据库设计几个小技巧
这几天看了一下ERP数据库设计，总结几个数据库设计的小技巧。
说实话，ERP系统确实是博大精深，而且对各个不同行业，都有不同的应用设计。
1、大部分表都有的字段：
ID：内码，表的唯一主键之一
Code：编码，有的可以用来做主键
Name：名称
State：记录状态
Enabled：是否有效
Desc：描述
Demo：备注
SortID：排序
CreateDate：记录创建日期
CreateUser：记录创建人
ModifyDate：记录更新日期
ModifyUser：记录更新人
2、在同一张表中有父子关系设计比如用一张表表示单位，就是一个树结构因此在树表里面，一般有下面字段：
ParentID：父ID
Level：记录在本树中的层次
IsLeaf：记录是否是树的叶子节点
3、有数据迁移的表：比如交易类型数据，一般有2张表表示
a、原始表：记录数据最新状态
b、迁移表：记录数据迁移状态 Action：表示数据前移原因
4、在表中字段不足表示的情况下，有2种解决方案：
a、预留一定数量字段 这种方案比较简单，使用起来也方便，避免了扩表的麻烦 缺点就是，扩展字段表达意义含糊，而且当扩展
b、采用EAV（Entity Attribute Value）设计：实体、属性、值，数据库模型的一种。 这个扩展方式，动态为数据模型增加或移除属性而不必改变表结构。   但是同时存在问题，解析表字段麻烦，在查询、插入数据时候，会比较复杂。
请大家补充一般设计数据库小技巧。