作项目分析数据库设计是个很重要也很难问题
完全按照范式有可能不符合用户需求不利于编程
看来是具体问题具体分析数据库设计是范式和需求折中
在上学时没觉得数据类型有多重要现在发觉了解数据类型
具体内容也是很重要可以知道区别数据库的间兼容问题
该如何处理
数据库设计窍门技巧:
第2 部分— 设计表和字段
1. 检查各种变化
我在设计数据库时候会考虑到哪些数据字段将来可能会发生变更比方说姓氏就是如此(注
意是西方人姓氏比如女性结婚后从夫姓等)所以在建立系统存储客户信息时我倾向于
在单独个数据表里存储姓氏字段而且还附加起始日和终止日等字段这样就可以跟踪这
数据条目变化
— Shropshire Lad
2. 采用有意义字段名
有回我参加开发过个项目其中有从其他员那里继承那个员喜欢用屏幕上
显示数据指示用语命名字段这也不赖但不幸是她还喜欢用些奇怪命名法其命名采
用了匈牙利命名和控制序号组合形式比如cbo1、txt2、txt2_b 等等
除非你在使用只面向你缩写字段名系统否则请尽可能地把字段描述清楚些当然也别
做过头了比如Customer_Shipping_Address_Street_Line_1 I 虽然很富有介绍说明性但没人愿意
键入这么长名字具体尺度就在你把握中
— Lamont Adams
3. 采用前缀命名
如果多个表里有好多同类型字段(比如FirstName)你不妨用特定表前缀(比如
CusLastName)来帮助你标识字段
— notoriousDOG
时效性数据应包括“最近更新日期/时间”字段时间标记对查找数据问题原因、按日期重新处
理/重载数据和清除旧数据特别有用
— kol
5. 标准化和数据驱动
数据标准化不仅方便了自己而且也方便了其他人比方说假如你用户界面要访问外部数据
源(文件、XML 文档、其他数据库等)你不妨把相应连接和路径信息存储在用户界面支持表
里还有如果用户界面执行工作流的类任务(发送邮件、打印信笺、修改记录状态等)那
么产生工作流数据也可以存放在数据库里预先安排总需要付出努力但如果这些过程采用数
据驱动而非硬编码方式那么策略变更和维护都会方便得多事实上如果过程是数据驱动
你就可以把相当大责任推给用户由用户来维护自己工作流过程
— tduvall
6. 标准化不能过头
对那些不熟悉标准化词(normalization )人而言标准化可以保证表内字段都是最基础
要素而这措施有助于消除数据库中数据冗余标准化有好几种形式但Third Normal
Form(3NF)通常被认为在性能、扩展性和数据完整性方面达到了最好平衡简单来说3NF 规
定:
· 表内每个值都只能被表达次
· 表内每行都应该被唯标识(有唯键)
· 表内不应该存储依赖于其他键非键信息
遵守3NF 标准数据库具有以下特点:有组表专门存放通过键连接起来关联数据比方说
某个存放客户及其有关定单3NF 数据库就可能有两个表:Customer 和OrderOrder 表不包
含定单关联客户任何信息但表内会存放个键值该键指向Customer 表里包含该客户信息
那行
更高层次标准化也有但更标准是否就定更好呢？答案是不定事实上对某些项目来
说甚至就连3NF 都可能给数据库引入太高复杂性
— Lamont Adams
为了效率缘故对表不进行标准化有时也是必要这样例子很多曾经有个开发财务分析
软件Software活就是用非标准化表把查询时间从平均40 秒降低到了两秒左右虽然我不得不这么做
但我绝不把数据表非标准化当作当然设计理念而具体操作不过是种派生所以如果表
出了问题重新产生非标准化表是完全可能
— epepke
7. Microsoft Access 报表窍门技巧
如果你正在使用Microsoft Access你可以用对用户友好字段名来代替编号名称:比如用
Customer Name 代替txtCNaM这样当你用向导创建表单和报表时其名字会让那些不
是员人更容易阅读
— jwoodruf
8. 不活跃或者不采用指示符
增加个字段表示所在记录是否在业务中不再活跃挺有用不管是客户、员工还是其他什么
人这样做都能有助于再运行查询时候过滤活跃或者不活跃状态同时还消除了新用户在采用
数据时所面临些问题比如某些记录可能不再为他们所用再删除时候可以起到定
防范作用
— theoden
9. 使用角色实体定义属于某类别列
在需要对属于特定类别或者具有特定角色事物做定义时可以用角色实体来创建特定时间关
联关系从而可以实现自我文档化
这里含义不是让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 记录来反映新头衔变化不过这样来在时间上无法跟踪
个人所处位置具体时间
— teburlew
10. 采用常用实体命名机构数据
组织数据最简单办法就是采用常用名字比如: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 等
采用般抽象术语来标识“事物”类别可以让你在关联数据以满足业务要求方面获得巨大灵
活性同时这样做还可以显著降低数据存储所需冗余量
— teburlew
11. 用户来自世界各地
在设计用到网络或者具有其他国际特性数据库时定要记住大多数国家都有区别字段格
式比如邮政编码等有些国家比如新西兰就没有邮政编码说
— billh
12. 数据重复需要采用分立数据表
如果你发现自己在重复输入数据请创建新表和新关系
— Alan Rash
13. 每个表中都应该添加3 个有用字段
· dRecordCreationDate在VB 下默认是Now而在SQL Server 下默认为GETDATE
· sRecordCreator在SQL Server 下默认为NOT NULL DEFAULT USER
· nRecordVersion记录版本标记；有助于准确介绍说明记录中出现null 数据或者丢失数据原
因
— Peter Ritchie
14. 对地址和电话采用多个字段
描述街道地址就短短行记录是不够Address_Line1、Address_Line2 和Address_Line3 可
以提供更大灵活性还有电话号码和邮件地址最好拥有自己数据表其间具有自身类型
和标记类别
— dwnerd
过分标准化可要小心这样做可能会导致性能上出现问题虽然地址和电话表分离通常可以达到
最佳状态但是如果需要经常访问这类信息或许在其父表中存放“首选”信息(比如
Customer 等)更为妥当些非标准化和加速访问的间妥协是有定意义
— dhattrem
15. 使用多个名称字段
我觉得很吃惊许多人在数据库里就给name 留个字段我觉得只有刚入门开发人员才会这
么做但实际上网上这种做法非常普遍我建议应该把姓氏和名字当作两个字段来处理然后在
查询时候再把他们组合起来
— klempan
Klempan 不是唯个注意到使用单个name 字段人要把这种情况变得对用户更为友好有好
些思路方法我最常用是在同表中创建个计算列通过它可以自动地连接标准化后字段这
样数据变动时候它也跟着变不过这样做在采用建模软件Software时得很机灵才行总的采用连接
字段方式可以有效隔离用户应用和开发人员界面
— damon
16. 提防大小写混用对象名和特殊
过去最令我恼火事情的就是数据库里有大小写混用对象名比如CustomerData这问
题从Access 到Oracle 数据库都存在我不喜欢采用这种大小写混用对象命名思路方法结果还不
得不手工修改名字想想看这种数据库/应用能混到采用更强大数据库那天吗？采用全
部大写而且包含下划符名字具有更好可读性(CUSTOMER_DATA)绝对不要在对象名
的间留空格
— bfren
17. 小心保留词
要保证你字段名没有和保留词、数据库系统或者常用访问思路方法冲突比如最近我编写个
ODBC 连接里有个表其中就用了DESC 作为介绍说明字段名后果可想而知！DESC 是
DESCENDING 缩写后保留词表里个SELECT *语句倒是能用但我得到却是大堆
毫无用处信息
— Daniel Jordan
18. 保持字段名和类型致性
在命名字段并为其指定数据类型时候定要保证致性假如字段在某个表中叫做
“agreement_number”你就别在另个表里把名字改成“ref1”假如数据类型在个表里
是整数那在另个表里可就别变成型了记住你干完自己活了其他人还要用你数
据库呢
— anta
19. 仔细选择数字类型
在SQL 中使用small 和tiny 类型要特别小心比如假如你想看看月销售总额你总额字
段类型是small那么如果总额超过了$32,767 你就不能进行计算操作了
— eger
20. 删除标记
在表中包含个“删除标记”字段这样就可以把行标记为删除在关系数据库里不要单独删除
某行；最好采用清除数据而且要仔细维护索引整体性
— kol
21. 避免使用触发器
触发器功能通常可以用其他方式实现在调试时触发器可能成为干扰假如你确实需要采
用触发器你最好集中对它文档化
— kol
22. 包含版本机制
建议你在数据库中引入版本控制机制来确定使用中数据库版本无论如何你都要实现这要
求时间长用户需求总是会改变最终可能会要求修改数据库结构虽然你可以通过检
查新字段或者索引来确定数据库结构版本但我发现把版本信息直接存放到数据库中不更为方
便吗？
— Richard Foster
23. 给文本字段留足余量
ID 类型文本字段比如客户ID 或定单号等等都应该设置得比般想象更大时间不长你
多半就会要添加额外而难堪不已比方说假设你客户ID 为10 位数长那你应该
把数据库表字段长度设为12 或者13 个长这算浪费空间吗？是有点但也没你想象
那么多:个字段加长3 个在有1 百万条记录再加上点索引情况下才不过让整个数据
库多占据3MB 空间但这额外占据空间却无需将来重构整个数据库就可以实现数据库规模
增长了
— tlundin
24. 列命名窍门技巧
我们发现假如你给每个表列名都采用统前缀那么在编写SQL 表达式时候会得到大
大简化这样做也确实有缺点比如破坏了自动表连接工具作用后者把公共列名同某些数
据库联系起来不过就连这些工具有时不也连接嘛举个简单例子假设有两个表:
Customer 和OrderCustomer 表前缀是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 个表乃至更多列你就知道这个窍门技巧
多有用了
— Bryce Stenberg
第3 部分— 选择键和索引
1. 数据采掘要预先计划
我所在市场部门度要处理8 万多份联系方式同时填写每个客户必要数据(这绝对不是小
活)我从中还要确定出组客户作为市场目标当我从最开始设计表和字段时候我试图不
在主索引里增加太多字段以便加快数据库运行速度然后我意识到特定组查询和信息采掘
既不准确速度也不快结果只好在主索引中重建而且合并了数据字段我发现有个指示计划相
当关键——当我想创建系统类型查找时为什么要采用号码作为主索引字段呢？我可以用传真号码
进行检索但是它几乎就象系统类型样对我来说并不重要采用后者作为主字段数据库更新
后重新索引和检索就快多了
— hscovell
可操作数据仓库(ODS)和数据仓库(DW)这两种环境下数据索引是有差别在DW 环境
下你要考虑销售部门是如何组织销售活动他们并不是数据库管理员但是他们确定表内
键信息这里设计人员或者数据库工作人员应该分析数据库结构从而确定出性能和正确输出的间
最佳条件
— teburlew
2. 使用系统生成主键
这天类同窍门技巧1但我觉得有必要在这里重复提醒大家假如你总是在设计数据库时候采用
系统生成键作为主键那么你实际控制了数据库索引完整性这样数据库和非人工机制就
有效地控制了对存储数据中每行访问
采用系统生成键作为主键还有个优点:当你拥有致键结构时找到逻辑缺陷很容易
— teburlew
3. 分解字段用于索引
为了分离命名字段和包含字段以支持用户定义报表请考虑分解其他字段(甚至主键)为其组
成要素以便用户可以对其进行索引索引将加快SQL 和报表生成器脚本执行速度比方说
我通常在必须使用SQL LIKE 表达式情况下创建报表 number 字段无法分解为
year、serial number、 type 和defendant code 等要素性能也会变坏假如年度和类型字
段可以分解为索引字段那么这些报表运行起来就会快多了
— rdelval
4. 键设计4 原则
· 为关联字段创建外键
· 所有键都必须唯
· 避免使用复合键
· 外键总是关联唯键字段
— Peter Ritchie
5. 别忘了索引
索引是从数据库中获取数据最高效方式的95%数据库性能问题都可以采用索引技术得到
解决作为条规则我通常对逻辑主键使用唯成组索引对系统键(作为存储过程)采用
唯非成组索引对任何外键列采用非成组索引不过索引就象是盐太多了菜就篌了你
得考虑数据库空间有多大表如何进行访问还有这些访问是否主要用作读写
— tduvall
大多数数据库都索引自动创建主键字段但是可别忘了索引外键它们也是经常使用键比
如运行查询显示主表和所有关联表某条记录就用得上还有不要索引memo/note 字段不
要索引大型字段(有很多)这样作会让索引占用太多存储空间
— gbrayton
6. 不要索引常用小型表
不要为小型数据表设置任何键假如它们经常有插入和删除操作就更别这样作了对这些插入和
删除操作索引维护可能比扫描表空间消耗更多时间
— kbpatel
7. 不要把社会保障号码(SSN)选作键
永远都不要使用SSN 作为数据库键除了隐私原因以外须知政府越来越趋向于不准许把
SSN 用作除收入相关以外其他目SSN 需要手工输入永远不要使用手工输入键作为主
键旦你输入你唯能做就是删除整个记录然后从头开始
— teburlew
上个世纪70 年代我还在读大学时候我记得那时SSN 还曾被用做学号当然尽管这么做是非
法而且人们也都知道这是非法但他们已经习惯了后来随着盗取身份犯罪案件增
加我现在大学校园正痛苦地从大摊子数据中把SSN 删除
— generalist
8. 不要用用户键
在确定采用什么字段作为表键时候可定要小心用户将要编辑字段通常情况下不要
选择用户可编辑字段作为键这样做会迫使你采取以下两个措施:
· 在创建记录的后对用户编辑字段行为施加限制假如你这么做了你可能会发现你应用程
序在商务需求突然发生变化而用户需要编辑那些不可编辑字段时缺乏足够灵活性当用
户在输入数据的后直到保存记录才发现系统出了问题他们该如何想？删除重建？假如记录不可
重建是否让用户走开？
· 提出些检测和纠正键冲突思路方法通常费点精力也就搞定了但是从性能上来看这样做
代价就比较大了还有键纠正可能会迫使你突破你数据和商业/用户界面层的间隔
离
所以还是重提句老话:你设计要适应用户而不是让用户来适应你设计
— Lamont Adams
不让主键具有可更新性原因是在关系模式下主键实现了区别表的间关联比如
Customer 表有个主键CustomerID而客户定单则存放在另个表里Order 表主键可能
是OrderNo 或者OrderNo、CustomerID 和日期组合不管你选择哪种键设置你都需要在
Order 表中存放CustomerID 来保证你可以给下定单用户找到其定单记录
假如你在Customer 表里修改了CustomerID那么你必须找出Order 表中所有相关记录对其进
行修改否则有些定单就会不属于任何客户——数据库完整性就算完蛋了
如果索引完整性规则施加到表级那么在不编写大量代码和附加删除记录情况下几乎不可能
改变某条记录键和数据库内所有关联记录而这过程往往丛生所以应该尽量避免
— ljboast
9. 可选键有时可做主键
记住查询数据不是机器而是人
假如你有可选键你可能进步把它用做主键那样话你就拥有了建立强大索引能力这
样可以阻止使用数据库人不得不连接数据库从而恰当过滤数据在严格控制域表数据库
上这种负载是比较醒目如果可选键真正有用那就是达到了主键水准
我看法是假如你有可选键比如国家表内state_code你不要在现有不能变动唯键上
创建后续键你要做无非是创建毫无价值数据比如以下例子:
Select count(*)
from address, state_ref
where
address.state_id = state_ref.state_id
and state_ref.state_code = 'TN'
我做法是这样:
Select count(*)
from address
where
and state_code = 'TN'
如你过度使用表后续键建立这种表关联操作负载真得需要考虑下了
— Stocker
10. 别忘了外键
大多数数据库索引自动创建主键字段但别忘了索引外键字段它们在你想查询主表中记录
及其关联记录时每次都会用到还有不要索引memo/notes 字段而且不要索引大型文本字段
(许多)这样做会让你索引占据大量数据库空间
— gbrayton
第4 部分— 保证数据完整性
1. 用约束而非商务规则强制数据完整性
如果你按照商务规则来处理需求那么你应当检查商务层次/用户界面:如果商务规则以后发生变
化那么只需要进行更新即可
假如需求源于维护数据完整性需要那么在数据库层面上需要施加限制条件
如果你在数据层确实采用了约束你要保证有办法把更新不能通过约束检查原因采用用户理解
语言通知用户界面除非你字段命名很冗长否则字段名本身还不够— Lamont Adams
只要有可能请采用数据库系统实现数据完整性这不但包括通过标准化实现完整性而且还
包括数据功能性在写数据时候还可以增加触发器来保证数据正确性不要依赖于商务层
保证数据完整性；它不能保证表的间(外键)完整性所以不能强加于其他完整性规则的上
— Peter Ritchie
2. 分布式数据系统
对分布式系统而言在你决定是否在各个站点复制所有数据还是把数据保存在个地方的前应该
估计下未来5 年或者10 年数据量当你把数据传送到其他站点时候最好在数据库字段
中设置些标记在目站点收到你数据的后更新你标记为了进行这种数据传输请写下
你自己批处理或者调度以特定时间间隔运行而不要让用户在每天工作后传输数据本地
拷贝你维护数据比如计算常数和利息率等设置版本号保证数据在每个站点都完全致
— Suhair TechRepublic
3. 强制指示完整性
没有好办法能在有害数据进入数据库的后消除它所以你应该在它进入数据库的前将其剔除激
活数据库系统指示完整性特性这样可以保持数据清洁而能迫使开发人员投入更多时间处
理条件
— kol
4. 关系
如果两个实体的间存在多对关系而且还有可能转化为多对多关系那么你最好开始就设置
成多对多关系从现有多对关系转变为多对多关系比开始就是多对多关系要难得多
— CS Data Architect
5. 采用视图
为了在你数据库和你应用代码的间提供另层抽象你可以为你应用建立专门
视图而不必非要应用直接访问数据表这样做还等于在处理数据库变更时给你提供了更多
自由
— Gay Howe
6. 给数据保有和恢复制定计划
考虑数据保有策略并包含在设计过程中预先设计你数据恢复过程采用可以发布给用户/开发
人员数据字典实现方便数据识别同时保证对数据源文档化编写在线更新来“更新查询”供
以后万数据丢失可以重新处理更新
— kol
7. 用存储过程让系统做重活
解决了许多麻烦来产生个具有高度完整性数据库解决方案的后我所在团队(Team)决定封装些
关联表功能组提供整套常规存储过程来访问各组以便加快速度和简化客户代码开
发在此期间我们发现3GL 编码器设置了所有可能条件比如以下所示:
SELECT Cnt = COUNT (*)
FROM [<Table>]
WHERE [<primary key column>] = < value>
IF Cnt = 0
BEGIN
INSERT INTO [<Table>]
( [< primary key column>] )
VALUES ( <New value> )
END
ELSE
BEGIN
<indicate duplication error>
END
而个非3GL 编码器是这样做:
INSERT INTO [<Table>]
( [< primary key column>] )
VALUES
( <New value> )
IF @@ERROR = 2627 -- Literal error code for Primary Key Constra
BEGIN
<indicate duplication error>
END
第2 个简单多了而且事实上利用了我们给数据库功能虽然我个人不喜欢使用嵌入文
字(2627)但是那样可以很方便地用点预先处理来代替数据库不只是个存放数据地
方它也是简化编码的地
— a-smith
8. 使用查找
控制数据完整性最佳方式就是限制用户选择只要有可能都应该提供给用户个清晰价值
列表供其选择这样将减少键入代码和误解同时提供数据致性某些公共数据特别适
合查找:国家代码、状态代码等
— CS Data Architect
第5 部分— 各种小窍门技巧
1. 文档、文档、文档
对所有快捷方式、命名规范标准、限制和都要编制文档
— nickypendragon
采用给表、列、触发器等加注释数据库工具是这有点费事但从长远来看这样做对开
发、支持和跟踪修改非常有用
— chardove
取决于你使用数据库系统可能有些软件Software会给你些供你很快上手文档你可能希望先开
始在说然后获得越来越多细节或者你可能希望周期性预排在输入新数据同时随着你
进展对每部分细节化不管你选择哪种方式总要对你数据库文档化或者在数据库自身
内部或者单独建立文档这样当你过了年多时间后再回过头来做第2 个版本你犯错机会
将大大减少
— mrs_helm
2. 使用常用英语(或者其他任何语言)而不要使用编码
为什么我们经常采用编码(比如9935A 可能是墨水笔供应代码4XF788-Q 可能是帐目编
码)？理由很多但是用户通常都用英语进行研究而不是编码工作5 年会计或许知道
4XF788-Q 是什么东西但新来可就不定了在创建下拉菜单、列表、报表时最好按照英语
名排序假如你需要编码那你可以在编码旁附上用户知道英语
— amasa
3. 保存常用信息
让个表专门存放般数据库信息非常有用我常在这个表里存放数据库当前版本、最近检查/修
复(对Access)、关联设计文档名称、客户等信息这样可以实现种简单机制跟踪数据
库当客户抱怨他们数据库没有达到希望要求而和你联系时这样做对非客户机/服务器环境
特别有用
— Richard Foster
4. 测试、测试、反复测试
建立或者修订数据库的后必须用用户新输入数据测试数据字段最重要是让用户进行测
试并且同用户道保证你选择数据类型满足商业要求测试需要在把新数据库投入实际服务的
前完成
— juneebug
5. 检查设计
在开发期间检查数据库设计常用技术是通过其所支持应用原型检查数据库换句话说
针对每种最终表达数据原型应用保证你检查了数据模型并且查看如何取出数据
— jgootee
6. Access 设计窍门技巧
对复杂Microsoft Access 数据库应用而言可以把所有主表放在个数据库文件里然
后增加其他数据库文件和装载同原有数据库有关特殊根据需要用这些连接到主文件
中主表比如数据输入、数据QC、统计分析、向管理层或者政府部门提供报表以及各类只读
查询等这措施简化了用户和组权限分配而且有利于应用分组和划分从而在
必须修改时候易于管理
— Dennis Walden