数据库设计（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语句里的列顺序决定。这就是说：

CREATE INDEX Foobar ON Customers (state_code, city_name);

和

CREATE INDEX Barfoo ON Customers (city_name, state_code); 

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

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

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

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

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

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

那实际上，下列这些索引是赠送的：

CREATE INDEX Floob_3 ON ExampleTable (a, b, c);
CREATE INDEX Floob_2 ON ExampleTable (a, b);
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子句的违反。我们用例子解释下：

CREATE VIEW NYC_Personnel
AS
SELECT *
FROM Personnel
WHERE city_name = 'New York'; 

现在我们用下列语句UPDATE：

UPDATE NYC_Personnel
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()子句。例如，假设有规则的酒店预定，你不能添加客人到另一个客人已或会占用的房间。不用直接写约束，像这样：

CREATE TABLE Hotel
(room_nbr INTEGER NOT NULL,
arrival_date DATE NOT NULL,
departure_date DATE NOT NULL,
guest_name CHAR(30) NOT NULL,
CONSTRAINT schedule_right
CHECK (H1.arrival_date <= H1.departure_date),
–- valid Standard SQL, but going to to work!!
CONSTRAINT no_overlaps
CHECK (NOT EXISTS
(SELECT *
FROM Hotel AS H1, Hotel AS H2
WHERE H1.room_nbr = H2.room_nbr
AND H2.arrival_date < H1.arrival_date
AND H1.arrival_date < H2.departure_date))); 

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

CREATE VIEW Valid_Hotel_Stays (room_nbr, arrival_date, departure_date, guest_name)
AS
SELECT H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name
FROM Hotel AS H1
WHERE NOT EXISTS
(SELECT *
FROM Hotel AS H2
WHERE H1.room_nbr = H2.room_nbr
AND H2.arrival_date < H1.arrival_date
AND H1.arrival_date < H2.departure_date)
AND H1.arrival_date <= H1.departure_date
WITH CHECK OPTION; 

例如：

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

随后：

INSERT INTO Valid_Hotel_Stays
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/