14个数据库设计中常用技巧.docx
《14个数据库设计中常用技巧.docx》由会员分享，可在线阅读，更多相关《14个数据库设计中常用技巧.docx（30页珍藏版）》请在冰豆网上搜索。
14个数据库设计中常用技巧
14个数据库设计中常用技巧
来源：
Javereserarch 作者：
maXiaoKe 2007年11月26日16:
47 网友评论：
0条 点击：
312
本文介绍了数据库设计中的14个技巧，这是许多人在大量的数据库分析与设计实践中，逐步总结出来的……
下述十四个技巧，是许多人在大量的数据库分析与设计实践中，逐步总结出来的。
对于这些经验的运用，读者不能生帮硬套，死记硬背，而要消化理解，实事求是，灵活掌握。
并逐步做到：
在应用中发展，在发展中应用。
1.原始单据与实体之间的关系
可以是一对一、一对多、多对多的关系。
在一般情况下，它们是一对一的关系：
即一张原始单据对应且只对应一个实体。
在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单证对应多个实体，或多张原始单证对应一个实体。
这里的实体可以理解为基本表。
明确这种对应关系后，对我们设计录入界面大有好处。
〖例1〗：
一份员工履历资料，在人力资源信息系统中，就对应三个基本表：
员工基本情况表、社会关系表、工作简历表。
这就是“一张原始单证对应多个实体”的典型例子。
2.主键与外键
一般而言，一个实体不能既无主键又无外键。
在E?
R图中,处于叶子部位的实体,可以定义主键，也可以不定义主键（因为它无子孙）,但必须要有外键（因为它有父亲）。
主键与外键的设计，在全局数据库的设计中，占有重要地位。
当全局数据库的设计完成以后，有个美国数据库设计专家说：
“键，到处都是键，除了键之外，什么也没有”，这就是他的数据库设计经验之谈，也反映了他对信息系统核心（数据模型）的高度抽象思想。
因为：
主键是实体的高度抽象，主键与外键的配对，表示实体之间的连接。
3.基本表的性质
基本表与中间表、临时表不同，因为它具有如下四个特性：
（1）原子性。
基本表中的字段是不可再分解的。
（2）原始性。
基本表中的记录是原始数据（基础数据）的记录。
（3）演绎性。
由基本表与代码表中的数据，可以派生出所有的输出数据。
（4）稳定性。
基本表的结构是相对稳定的，表中的记录是要长期保存的。
理解基本表的性质后，在设计数据库时，就能将基本表与中间表、临时表区分开来。
4.范式标准
基本表及其字段之间的关系,应尽量满足第三范式。
但是，满足第三范式的数据库设计，往往不是最好的设计。
为了提高数据库的运行效率，常常需要降低范式标准：
适当增加冗余，达到以空间换时间的目的。
〖例2〗：
有一张存放商品的基本表，如表1所示。
“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。
但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。
在Rose2002中，规定列有两种类型：
数据列和计算列。
“金额”这样的列被称为“计算列”，而“单价”和“数量”这样的列被称为“数据列”。
表1商品表的表结构
商品名称商品型号单价数量金额
电视机29?
2,50040100,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来实现约束，在数据库设计工具中，对字段的取值范围进行定义时，有一个Check按钮，通过它定义字段的值城。
参照完整性：
用PK、FK、表级触发器来实现。
用户定义完整性：
它是一些业务规则，用存储过程和触发器来实现。
13.防止数据库设计打补丁的方法是“三少原则”
（1）一个数据库中表的个数越少越好。
只有表的个数少了，才能说明系统的E--R图少而精，去掉了重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计;
（2）一个表中组合主键的字段个数越少越好。
因为主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间;
（3）一个表中的字段个数越少越好。
只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。
所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一个子表。
这个方法很简单，有的人就是不习惯、不采纳、不执行。
数据库设计的实用原则是：
在数据冗余和处理速度之间找到合适的平衡点。
“三少”是一个整体概念，综合观点，不能孤立某一个原则。
该原则是相对的，不是绝对的。
“三多”原则肯定是错误的。
试想：
若覆盖系统同样的功能，一百个实体（共一千个属性）的E--R图，肯定比二百个实体（共二千个属性）的E--R图，要好得多。
提倡“三少”原则，是叫读者学会利用数据库设计技术进行系统的数据集成。
数据集成的步骤是将文件系统集成为应用数据库，将应用数据库集成为主题数据库，将主题数据库集成为全局综合数据库。
集成的程度越高，数据共享性就越强，信息孤岛现象就越少，整个企业信息系统的全局E?
R图中实体的个数、主键的个数、属性的个数就会越少。
提倡“三少”原则的目的，是防止读者利用打补丁技术，不断地对数据库进行增删改，使企业数据库变成了随意设计数据库表的“垃圾堆”，或数据库表的“大杂院”，最后造成数据库中的基本表、代码表、中间表、临时表杂乱无章，不计其数，导致企事业单位的信息系统无法维护而瘫痪。
“三多”原则任何人都可以做到，该原则是“打补丁方法”设计数据库的歪理学说。
“三少”原则是少而精的原则，它要求有较高的数据库设计技巧与艺术，不是任何人都能做到的，因为该原则是杜绝用“打补丁方法”设计数据库的理论依据。
14.提高数据库运行效率的办法
在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：
（1）在数据库物理设计时，降低范式，增加冗余,少用触发器,多用存储过程。
（2）当计算非常复杂、而且记录条数非常巨大时（例如一千万条），复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。
这是电信计费系统设计的经验。
（3）发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。
水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。
若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。
（4）对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
（5）在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。
SQL教程联接JOIN使用大全.
来源：
2009年06月09日08:
30 网友评论：
0条 点击：
489
第三范式（3NF）：
如果关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递信赖，则称关系R是属于第三范式的。
例：
如S1（SNO，SNAME，DNO，DNAME，LOCATION）各属性分别代表学号，姓名，所在系，系名称，系地址。
关键字SNO决定各个属性。
由于是单个关键字，没有部分依赖的问题，肯定是2NF。
但这关系肯定有大量的冗余，有关学生所在的几个属性DNO，DNAME，LOCATION将重复存储，插入，删除和修改时也将产生类似以上例的情况。
原因：
关系中存在传递依赖造成的。
由于SNAME和DNO是依赖SNO，而DNAME和LOCATION是依赖DNO的，即通过一个学号可以知道该学生的姓名以及他所在系代码，但无法由学号知道系地址，学号和系地址间是通过学号所对应的学生的所在系关联的，因此关键字SNO对LOCATION函数决定是通过传递依赖DNO->LOCATION实现的。
也就是说，SNO不直接决定非主属性LOCATION。
解决目地：
每个关系模式中不能留有传递依赖。
解决方法：
分为两个关系S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）
注意：
关系S中不能没有外关键字DNO。
否则两个关系之间失去联系。
在数据库的设计过程中常常按照第三范式来设计数据库，当然在有些场合为优化数据库的性能而增加了相关冗余字段以使表的结构不符合3NF，在多数场合中，没有一张表能完整的发挥客户所需要的结果集。
这样就需要通过联接多张在逻辑上存在依赖关系的表，选择你所需要的数据。
在使用JOIN前，必须明白是通过联接，根据各个表之间的逻辑关系从相关表中检索数据。
通过SQLServer自带帮助文件，可以清楚的知道：
可在FROM或WHERE子句中指定联接。
1.1.在WHERE子句中指定联接
下例使用WHERE子句进行表之间的
SELECTA.SYMBOL,A.SNAME,B.TDATE,B.CLOSE
FROMSECURITYCODEA,DAYQUOTEB
WHEREA.SYMBOL=B.SYMBOL
ANDB.TDATE>=A.LISTDATE
ANDA.SYMBOLLIKE‘600%’
在上例中联接的表A与表B通过A.SYMBOL=B.SYMBOL这个条件联接，筛选条件为B.TDATE不小于A.LISTDATE。
在WHERE子句中指定联接，对于较简单的联接，使用这种方式可能较方便，但综合来说，不推荐使用该语法联接表。
1.2.在FROM子句中指定联接
拿上面的例子来详细说，表SECURITYCODE中主要存放证券代码的基本信息，表中的SYMBOL代表股票代码，SNAME代表股票名称，LISTDATE代表上市日期；表DAYQUOTE中主要存放股票的每日行情。
SYMBOL代表股票代码，TDATE代表交易日期，CLOSE代表收盘价。
我现在的取值逻辑是：
取出股票代码以600打头的股票自上市日期以来的所有交易日的收盘价。
将SECURITYCODE与DAYQUOTE通过SYMBOL联接。
SELECTA.SYMBOL,A.SNAME,B.TDATE,B.CLOSE
FROMSECURITYCODEA
JOINDAYQUOTEB
ONA.SYMBOL=B.SYMBOL
WHEREB.TDATE>=A.LISTDATE
ANDA.SYMBOLLIKE‘600%’
ORDERBYA.SYMBOL,B.TDATE
对于使用FROM子句方式联接表，可以很清楚的看出表之间的联接条件。
就可读性以及后续的可修改性与WHERE子句相比有较大的优势。
下面就联接的方式引用帮助文件中的具体介绍，联接可以分为以下几种：
1.3.内联接
内联接（典型的联接运算，使用像=或<>之类的比较运算符）。
包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
在SQL-92标准中，内联接可在FROM或WHERE子句中指定。
这是WHERE子句中唯一一种SQL-92支持的联接类型。
WHERE子句中指定的内联接称为旧式内联接。
内联接一般常见写法：
SELECTA.COLUMN1,[A.COLUMN2],B.COLUMN1,[B.COLUMN2]
FROMTABLE1A
[INNER]JOINTABLE2B
ONA.COLUMN0=B.COLUMN0
在查询分析器中使用INNERJOIN时常常省略INNER。
1.4.外联接
外联接可以是左向外联接、右向外联接或完整外部联接。
在FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
LEFTJOIN或LEFTOUTERJOIN
左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。
如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHTJOIN或RIGHTOUTERJOIN
右向外联接是左向外联接的反向联接。
将返回右表的所有行。
如果右表的某行在左表中没有匹配行，则将为左表返回空值。
FULLJOIN或FULLOUTERJOIN
完整外部联接返回左表和右表中的所有行。
当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。
如果表之间有匹配行，则整个结果集行包含基表的数据值。
仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。
内联接消除与另一个表中的任何行不匹配的行。
而外联接会返回FROM子句中提到的至少一个表或视图的所有行，只要这些行符合任何WHERE或HAVING搜索条件。
将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。
完整外部联接中两个表的所有行都将返回。
外联接的中常见的是LEFTJOIN，将LEFTJOIN用熟已经可以解决大半问题了。
外联接的一般写法：
SELECTA.COLUMN1,[A.COLUMN2],B.COLUMN1,[B.COLUMN2]
FROMTABLE1A
LEFT|RIGHT|FULL[OUTER]JOINTABLE2B
ONA.COLUMN0=B.COLUMN0
在查询分析器中使用OUTERJOIN时常常省略OUTER。
LEFT和RIGHT只是方向问题，在特定场合下，FULLOUTERJOIN相当于LEFTOUTERJOIN和RIGHTOUTERJOIN的消除重复行的合集。
1.5.交叉联接
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。
交叉联接也称作笛卡尔积。
没有WHERE子句的交叉联接将产生联接所涉及的表的笛卡尔积。
第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
也就是说在没有WHERE子句的情况下，若表A有3行记录，表B有6行记录:
：
SELECTA.*,B.*FROM表ACROSSJOIN表B
那以上语句会返回18行记录。
快速掌握一些异常精妙的"SQL"语句
来源：
赛迪网 2008年05月23日09:
16 网友评论：
0条 点击：
155
◆复制表（只复制结构,源表名：
a新表名：
b）
SQL:
select*intobfromawhere1<>1
◆拷贝表（拷贝数据,源表名：
a目标表名：
b）
SQL:
insertintob（a,b,c）selectd,e,ffromb;
◆显示文章、提交人和最后回复时间
SQL:
selecta.title,a.username,b.adddatefromtablea,
（selectmax（adddate）adddatefromtablewheretable.title=a.title）b
◆说明：
外连接查询（表名1：
a表名2：
b）
SQL:
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=
b.c
◆日程安排提前五分钟提醒
SQL:
select*from日程安排wheredatediff（'minute',f开始时间,getdate（））>5
◆两张关联表，删除主表中已经在副表中没有的信息
SQL:
deletefrominfowherenotexists
（select*frominfobzwhereinfo.infid=infobz.infid）
◆说明：
SQL:
SELECTA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATE
FROMTABLE1,（SELECTX.NUM,X.UPD_DATE,Y.UPD_DATE
PREV_UPD_DATEFROM（SELECTNUM,UPD_DATE,INBOUND_QTY,
STOCK_ONHANDFROMTABLE2WHERETO_CHAR（UPD_DATE,
'YYYY/MM'）=TO_CHAR（SYSDATE,'YYYY/MM'））X,
（SELECTNUM,UPD_DATE,STOCK_ONHANDFROMTABLE2
WHERETO_CHAR（UPD_DATE,'YYYY/MM'）=TO_CHAR（TO_DATE
（TO_CHAR（SYSDATE,'YYYY/MM'）¦¦'/01','
YYYY/MM/DD'）-1,'YYYY/MM'））Y,WHEREX.NUM=Y.NUM
（+）ANDX.INBOUND_QTY+NVL（Y.STOCK_ONHAND,0）<>
X.STOCK_ONHAND）BWHEREA.NUM=B.NUM
◆说明：
SQL:
select*fromstudentinfowherenotexists（select*fromstudentwhere
studentinfo.id=student.id）and系名称='"&strdepartmentname&"'and专业名称
='"&strprofessionname&"'orderby性别,生源地,高考总成绩。
了解MySQL的数据类型以及建库策略
来源：
赛迪网 作者：
84171 2008年01月17日09:
51 网友评论：
0条 点击：
65
无论是在小得可怜的免费数据库空间或是大型电子商务网站，合理的设计表结构、充分利用空间是十分必要的。
这就要求我们对数据库系统的常用数据类型有充分的认识。
下面我就将我的一点心得写出来跟大家分享。
一、数字类型
数字类型按照我的分类方法分为三类：
整数类、小数类和数字类。
我所谓的“数字类”，就是指DECIMAL和NUMERIC，它们是同一种类型。
它严格的说不是一种数字类型，因为他们实际上是将数字以字符串形式保存的;他的值的每一位（包括小数点）占一个字节的存储空间，因此这种类型耗费空间比较大。
但是它的一个突出的优点是小数的位数固定，在运算中不会“失真”，所以比较适合用于“价格”、“金额”这样对精度要求不高但准确度要求非常高的字段。
小数类，即浮点数类型，根据精度的不同，有FLOAT和
升级会员 