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

简介:

在第一篇,我们因它们是什么并区分它们命名数据元。在第二篇,我们用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_Personnel
2 AS
3 SELECT *
4 FROM Personnel
5 WHERE city_name = 'New York'; 
复制代码

现在我们用下列语句UPDATE:

1 UPDATE NYC_Personnel
2 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_overlaps
10 CHECK (NOT EXISTS
11 (SELECT *
12 FROM Hotel AS H1, Hotel AS H2
13 WHERE H1.room_nbr = H2.room_nbr
14 AND H2.arrival_date < H1.arrival_date
15 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_date
10 AND H1.arrival_date < H2.departure_date)
11 AND H1.arrival_date <= H1.departure_date
12 WITH CHECK OPTION; 
复制代码

例如:

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

随后:

1 INSERT INTO Valid_Hotel_Stays
2 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,伟大都市报纸的温顺记者~~~


本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/5595792.html,如需转载请自行联系原作者

相关文章
|
1月前
|
SQL NoSQL 前端开发
基于BS架构的饰品购物平台设计与实现(程序+文档+数据库)
基于BS架构的饰品购物平台设计与实现(程序+文档+数据库)
|
2月前
|
存储 监控 安全
360 企业安全浏览器基于阿里云数据库 SelectDB 版内核 Apache Doris 的数据架构升级实践
为了提供更好的日志数据服务,360 企业安全浏览器设计了统一运维管理平台,并引入 Apache Doris 替代了 Elasticsearch,实现日志检索与报表分析架构的统一,同时依赖 Doris 优异性能,聚合分析效率呈数量级提升、存储成本下降 60%....为日志数据的可视化和价值发挥提供了坚实的基础。
360 企业安全浏览器基于阿里云数据库 SelectDB 版内核 Apache Doris 的数据架构升级实践
|
3月前
|
存储 缓存 关系型数据库
鱼和熊掌如何兼得?一文解析RDS数据库存储架构升级
阿里云RDS率先推出新型存储类型通用云盘,提供低延迟、低成本、高持久性的用户体验。
鱼和熊掌如何兼得?一文解析RDS数据库存储架构升级
|
4月前
|
存储 SQL Java
数据库TiDB-01.数据库架构概述
TiDB兼容MySQL 5.7协议,支持水平扩容或者缩容的金融级高可用的云原生分布式数据库。
260 2
数据库TiDB-01.数据库架构概述
|
9月前
|
存储 负载均衡 容灾
MySQL数据库的分布式架构和数据分片方案
MySQL数据库的分布式架构和数据分片方案
|
1月前
|
SQL 存储 数据管理
数据库系统架构与DBMS功能探微:现代信息时代数据管理的关键
数据库系统架构与DBMS功能探微:现代信息时代数据管理的关键
34 1
|
2月前
|
敏捷开发 弹性计算 架构师
浅谈微服务架构下的数据库设计与实践
在当今快速发展的软件工程领域,微服务架构因其高度的模块化和灵活性而受到广泛欢迎。然而,随之而来的是对数据库设计和管理提出了新的挑战。本文将探讨在微服务架构下,如何有效地设计和实践数据库以支持服务的独立性、数据的一致性和系统的扩展性。我们将从微服务的数据库隔离策略谈起,深入分析数据库的分库分表、事务管理、数据一致性解决方案等关键技术,并通过实例说明如何在实际项目中应用这些原则和技术。本文旨在为软件开发者和架构师提供一份指南,帮助他们在微服务架构的环境下,更好地进行数据库设计和管理。
203 1
|
7月前
|
存储 SQL 缓存
Mysql数据库(3)—架构和日志
Mysql数据库(3)—架构和日志
68 0
Mysql数据库(3)—架构和日志
|
3月前
|
NoSQL Java 关系型数据库
基于java Swing 和 mysql实现的飞机订票系统(源码+数据库+ppt+ER图+流程图+架构说明+论文+运行视频指导)
基于java Swing 和 mysql实现的飞机订票系统(源码+数据库+ppt+ER图+流程图+架构说明+论文+运行视频指导)
231 0
|
4月前
|
运维 Oracle 关系型数据库
LIS实验室信息管理系统功能模块(Oracle数据库、Client/Server架构)
LIS实验室信息管理系统功能模块(Oracle数据库、Client/Server架构)
52 0