你的位置:首页 > 数据库

[数据库]数据库设计(4/9):创建架构


对于设计和创建数据库完全是个新手?没关系,Joe Celko, 世界上读者数量最多的SQL作者之一,会告诉你这些基础。和往常一样,即使是最专业的数据库老手,也会给他们带来惊喜。Joe是DMBS杂志是多年来最受 读者喜爱的作者。他在美国、英国,北欧,南美及非洲传授SQL知识。他在ANSI / ISO SQL标准委员会工作了10年,为SQL-89和SQL-92标准做出了杰出贡献。


 介绍完表,Joe Celko会谈下如何把它们放一起作为数据库,还有什么是实体关系和视图。

在第一篇,我们因它们是什么并区分它们命名数据元。在第二篇,我们用SQL里给我们的数据类型和简单的行或列约束来模型化数据元。在第三篇,我们把这些行放入表成为实体,关系和辅助数据。

现在我们有了基表,是时候把它们放一起作为数据库,增加其它的架构对象混合一起。这需要我们从比一次一个表或多个表更高的层级来看。对这个一个有用的工具是实体关系图(E-R (Entity-Relationship) diagram)。不好的消息是有很多风格的实体关系图,其中一些变得非常复杂。这个工具的第一个版本应归于Peter Chen在他1976年的论文里,它还是一个很好开始的地方。每个系统认同实体表表现为一个在它里面有表名的矩形。但一些系统会放入所有列名,对于主键标上特殊符号作为不同等等。

Chen最先使用方块牌(diamond)作为关系表。这是个很好的主意,在它里面很容易画n元关系,你可以快速看到交替的模式框和方块牌。如果一个表同时使用,会有一些结论。例如,婚姻是丈夫和妻子之间的关系,但也有婚姻日期的数据,登记号,证婚人(presiding official)等等。

接下来的系统放弃了方块牌,把关系表放入矩形,并使用只能显示二元关系的线,但线的末端有可选或必选成员关系的标志,0,1或更多成员关系级别,给它一些权利。这三个图形是条形作为1,圆形作为0,“鸡爪”作为多个。这个百闻不如一见。

我们可以认为讲师(lecturer)传授(teaches)课程(courses),因此课程是被讲师传授。

这很好理解,但我们应该表示更多的规则。例如,如果我们有一个策略,每个讲师必须刚好只传授一个课程?我们可以添加用最大1的条行标志和第二个执行中间线的条行来表示传授关系。这个逻辑适用于关系里涉及的课程。

现在,让我们放宽一些规则。我们认为保持一个讲师工作,即使他这次没传授任何东西,但换取这份工作安全,我们想让他有时候可以传授一个或更多的课程。圆形指向线中心,鸡爪在课程框旁。

这一切都很好,直到我们觉得多对多的关系,这会看起来像这样:

我们需要有一个明确的关系表,称它“教学任务(Teaching Assignments)”,在讲师和课程之间。实体关系图更容易看懂,不需要看很多的SQL DDL语句。还有其它突出的模式,例如扇形。

 

我不能把部门和人员正确匹配我们。假设常见的组织架构,这应该2个1:n分部(Divisions)的关系模型。

你可以用多个工具从SQL DDL里获得实体关系图,在更高级查看其它问题模式。这里我不会给ER模型和图的详细说明;现在我只想让你知道它们。接下来,你可以自己学习使用它们。

一旦你的表设计已经确定,就可以考虑数据访问了。这通常意味着你会加索引到表。有两类索引:主和从。主索引必须在表上执行唯一性约束,像PRIMARY KEY和UNIQUE约束,从索引添加是为了性能提升。

SQL引擎会自动为你创建主索引,但这个假设并不对你有好处。在SQL Server里,在一个表上你只能有一个聚集索引,因此小心用它。例如,不用聚集索引在customer_id列作为客户表的主键,你会使用它保持物理文件按部门编号排序,因为这是你的报表分组和汇总的样子。同时使用非聚集索引作为查找客户就可以了。

索引的树结构由在CREATE INDEX语句里的列顺序决定。这就是说:

1 CREATE INDEX Foobar ON Customers (state_code, city_name);

1 CREATE INDEX Barfoo ON Customers (city_name, state_code); 

逻辑上是一样的,但功能不同。

选择从索引是个非完全多项式(NP-Complete)问题,因此你不能用常规方法创建它们。最好你可以遵循一些简单的启发式。第一个启发式不要重叠索引(over-index)。初学者喜欢增加很多索引让它们的产寻更快。这并不都是对的:查询优化器会忽略用不到的索引,因此事实上它们变成了“无用代码”。但当基表修改的时候,每个插入,更新和删除语句会修改这些无用的索引。这会是很大的负担。

第二个启发式如果一列从不在查询条件里使用(意思是说在WHERE、ON或HAVING子句里),那它不应该在索引里出现。

第三个启发式你不应该有常见列前缀列表的索引。

这就是说如果你有个像这样的索引:

1 CREATE INDEX Floob ON ExampleTable (a, b, c, d); 

那实际上,下列这些索引是赠送的:

1 CREATE INDEX Floob_3 ON ExampleTable (a, b, c);2 CREATE INDEX Floob_2 ON ExampleTable (a, b);3 CREATE INDEX Floob_1 ON ExampleTable (a); 

直接创建隐含的索引是多余的。

下一个你经常会用的添加到架构的东西是视图。很多程序员认为视图可以帮助用户减少重复代码的编写。那是对的,但视图的最大优点是它每次用同样的方式做同样的事,对每个人。人总不会一致的。不抱怨的话,相比另一个程序员,程序员不会实现不同的业务规则。Fred读到的规格是(shipping_qty > 100))和Sam读到的规格(shipping_qty >= 100);如果他们使用视图的话,业务规则适用一个且只有一个方式。

通常来讲,视同扮演2个方式。或者他们是本地的语句(通常一个SELECT)和扩展为内嵌的文本,它们的定义保持在架构里。另一个做法是从它们的定义以物理表实现它们的定义。一般而言,当多个会话同时使用它们的时候,一个好的SQL引擎会实现视图,这样的话虚拟表可以在主存里共享,一个会话可以多次使用同样的视图。在SQL Server,你可以在视图上创建索引提高性能。

即使有经验的SQL人员也不知道视图的另一部分; WITH CHECK OPTION子句。如果指定了WITH CHECK OPTION,视图表必须可更新。这个做法是阻止通过WHERE子句的违反。我们用例子解释下:

1 CREATE VIEW NYC_Personnel2 AS3 SELECT *4 FROM Personnel5 WHERE city_name = 'New York'; 

现在我们用下列语句UPDATE:

1 UPDATE NYC_Personnel2 SET city_name = 'Birmingham'; –- everyone moved!! 

UPDATE会执行,没有任何问题,但我们再次使用NYC_Personnel时,刚才看到的记录现在都消失了。这已不再符合WHERE子句的条件!同样,一个有(col1 = 'B')的INSERT INTO语句没有问题,但在这个视图里从不会看到。

WITH CHECK OPTION会让系统会在INSERT或UPDATE上检查WHERE子句。如果新的或修改的行测试失败,修改会被拒绝,视图还是一样。那么,刚才的UPDATE语句会收到错误信息,你不能在特定方式里修改特定列。

WITH CHECK OPTION可以作为架构级别的CHECK()子句。例如,假设有规则的酒店预定,你不能添加客人到另一个客人已或会占用的房间。不用直接写约束,像这样:

 1 CREATE TABLE Hotel 2 (room_nbr INTEGER NOT NULL, 3 arrival_date DATE NOT NULL, 4 departure_date DATE NOT NULL, 5 guest_name CHAR(30) NOT NULL, 6 CONSTRAINT schedule_right 7 CHECK (H1.arrival_date <= H1.departure_date), 8 –- valid Standard SQL, but going to to work!! 9 CONSTRAINT no_overlaps10 CHECK (NOT EXISTS11 (SELECT *12 FROM Hotel AS H1, Hotel AS H213 WHERE H1.room_nbr = H2.room_nbr14 AND H2.arrival_date < H1.arrival_date15 AND H1.arrival_date < H2.departure_date))); 

schedule_right约束没有问题,因为它没有子查询,但很多产品会检查overlaps约束。我们可以不用表上的no_overlaps约束,我们可以在Hotel表上所有行列上构建一个视图,并增加执行WITH CHECK OPTION的WHERE子句。

 1 CREATE VIEW Valid_Hotel_Stays (room_nbr, arrival_date, departure_date, guest_name) 2 AS 3 SELECT H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name 4 FROM Hotel AS H1 5 WHERE NOT EXISTS 6 (SELECT * 7 FROM Hotel AS H2 8 WHERE H1.room_nbr = H2.room_nbr 9 AND H2.arrival_date < H1.arrival_date10 AND H1.arrival_date < H2.departure_date)11 AND H1.arrival_date <= H1.departure_date12 WITH CHECK OPTION; 

例如:

1 INSERT INTO Valid_Hotel_Stays2 VALUES (1, '2011-01-01', '2011-01-03', 'Ron Coe'); 

随后:

1 INSERT INTO Valid_Hotel_Stays2 VALUES (1, '2011-01-03', '2011-01-05', 'John Doe'); 

在第2个INSERT INTO语句上,会给我们想要的违反了WITH CHECK OPTION子句。

真正的好处是,这让约束在声明代码(declarative code)里了,且查询优化器可以使用。

视图可以通过隔离让用户看不到未授权或不需要的数据。理想地,你想为每个用户创建一系列的视图,让他们觉得数据库就像专门为它们设计的一样。这会花点时间,你需要知道如何使用SQL的第三(最被忽略的)子语言——数据控制语言(the DCL (Data Control Language))。

DCL不是个安全系统;它是SQL数据库的简单的控制工具。它让数据不在安全级别外暴露。

在一个安全的系统里,在最小的安全级别,我们被告知超人是来自外星球的奇怪访客,有常人不及的能力和技能。但我们需要更高的级别来知道他是伪装的Clark Kent,伟大都市报纸的温顺记者~~~

原文链接:

http://www.sqlservercentral.com/articles/Database+Design/Building+a+Database+Schema/70793/