PostgreSQL从菜鸟到专家数据存取设计_sql

概述从表面上看，关系数据库，例如PostgreSQL，拥有很多类似于电子表格的地方。但是，当你了解数据库的底层结构，你可以发现它复杂得多，主要因为它有能力通过复杂的方法将表格关联到一起。它可以比电子表格有效地存储更多复杂的数据，并且它有用很多其他功能方便选择存储的数据。例如，数据库可以管理多个用户同时使用。　　将数据存入数据库　　让我们看看存放我们简单的但表格客户列表到数据库，看这

从表面上看，关系数据库，例如Postgresql，拥有很多类似于电子表格的地方。但是，当你了解数据库的底层结构，你可以发现它复杂得多，主要因为它有能力通过复杂的方法将表格关联到一起。它可以比电子表格有效地存储更多复杂的数据，并且它有用很多其他功能方便选择存储的数据。例如，数据库可以管理多个用户同时使用。

　　将数据存入数据库

　　让我们看看存放我们简单的但表格客户列表到数据库，看这么做有什么好处。我们将扩展它并看Postgresql怎么帮助我们解决客户订单的问题。

　　就像我们在前面章节看到的，数据库由表(tables)组成，或者用更正式的术语，关系(relations)。我们将在本书中使用表这个术语。表里头包含数据行(更正式的叫法是元组(tuples))，并且每条数据行都包含许多列(columns)，或者叫做属性(attributes)。

　　首先，我们需要设计一个表来保存我们的客户信息。好消息是，电子表格的数据往往是一个几乎现成的解决方案，因为它按照一定的行和列保存数据。在开始建立一个基本的数据库表格前，我们需要确定三件事情：

　　我们需要多少个列来存储每个项目的属性?

　　每个属性(列)的数据类型是什么?

　　我们怎么区别不同的行包含的不同项目?

　　注意数据库的表的每行的顺序不影响数据。在单独的数据表格中，行的数据可能非常重要，但在数据库的表中，没有顺序。因为当你查看数据库的表存储的数据的时候，数据库可能会随意按照它选择的数据的顺序将数据给你，除非你特别告诉它你要按特殊的顺序排序数据。如果你需要按特殊的顺序查看数据，可以通过指定获取数据的顺序，而不需要例会它存储的方式。我们将在第四章的SELECT语句的ORDER BY从句中了解到怎么按顺序获取数据。

　　选择列

　　如果你回顾下图2-1中原始的客户信息电子表格，你会发现我们已经确定每个客户需要的合理列：名，姓，邮政编码等。所以，我们已经回答了我们应该有多少列的问题。

　　电子表格的行和数据库中的行最重要的不同是数据库里头的表格的列数对于所有的行都是相同的。这对于我们原始的电子表格的不是问题。

　　为每个列选择数据类型

　　第二步是为每列的数据确定类型。电子表格中允许每个单元格拥有不同的类型，在数据库的表中，每个列的类型必须相同。就像大多数编程语言一样，数据库使用类型来标记不同的数据值。平常，你需要知道所有的基本类型。主要的选择可以是整数，浮点数，定长文本，变长文本和日期。通常最容易的判断恰当的数据类型的方法是看看示例的数据。

　　在我们的客户数据中，所有的列的类型都可以是文本类型，即使电话号码看上去好像都是数字。将电话号码作为数字存储通常存在以下问题：很容易丢失前导的零，并阻止我们存储国际拨号符(+)，不允许在区号前后写上括号等等。显然，电话号码远不止一串数字。回过头来，用字符串存储电话号码可能不是最好的选择，因为我们可能无意地插入各种其他字符，但至少会比使用数字类型要好点。初始设计可以在之后做优化。

　　我们会发现头衔(女士，先生，医生)的长度通常比较短——通常少于四个字符。类似的，邮政编码也有固定长度。因此，我们可以设置这些列为固定长度，但设置其他的所有列为变长的，因为比方说没有简单的办法判断一个人的名字有多长。

　　我们将在本章靠后的“基础数据类型”小节以及第八章讨论Postgresql的数据类型。

　　标记行的唯一性

　　我们在转换我们的电子表格到数据库表格的最后的问题有点微妙，它牵涉到数据库管理表和表之间的关系。我们需要确定什么使数据库中一条客户数据记录区别于另一条客户记录。换句话说，我们怎么区别我们的客户?在电子表格中，我们不趋向于关心区别客户的细节。但是，在数据库设计中，这是一个关键问题，因为关系数据库的规则需要从某个方面区别每条记录的唯一性。

　　最明显的解决方案好像是通过客户名来区别客户，但不幸的是，这通常不足以区别。因为很有可能两个客户拥有同样的名字。另外一项你可以选择的是电话号码，但问题是如果两个客户住在于一起呢?在这个时候，你可能建议使用名字和电话号码的组合。

　　当然，不大可能两个客户同时拥有相同的名字和电话号码，但是，这种方法不但很不雅观，而且还有另一个潜在问题。如果客户更换电话服务商，电话号码发生变化，将发生什么。在我们的定义中，唯一的客户必须是一个客户。因为他不同于我们已有的客户。当然，我们知道它是有不同的电话号码的旧客户。在数据库中，选择一个可能会变动的功能作为客户的唯一标识是个坏习惯，因为管理唯一标识的变动非常麻烦。

　　这种唯一标识的问题，经常出现在数据库设计中。我们应该做的是寻找一个主键(primary key)——一种最容易的用于区别一个客户的数据行于其他所有行的方法。遗憾的是，我们还没有成功，但所有的也没有失败，因为标准的解决方案是为每个客户分配一个唯一的数字。

　　我们简单地为每位客户分配一个唯一的数字，然后，爽，我们有一个明显的方法区别客户，而不管他们是否改变电话号码或者换到其他的住所，甚至改变他们的名字。这种在实际数据中没有办法选择其他列而附而加的作为一个唯一的键值的键，叫做代理键。在现实世界数据中即使存在一些特殊的数据，数据库中叶经常这样做，提供序列数据类型来帮助解决这类问题。我们将在后面的“基础数据类型”小姐讨论这个类型。

　　我们已经完成为我们的初始表格做一个数据库设计，现在是时候存储我们的数据到数据库中了。图2-4展示了在Postgresql数据库中我们的数据在windows或者linux主机的终端窗口中通过一个简单的命令行工具psql显示出来的样子。

650) this.wIDth=650;" border="1" alt="Postgresql从菜鸟到专家数据存取设计" src="http://img.jb51.cc/vcimg/static/loading.png" src="http://image20.it168.com/201204_500x375/1035/a6eba706d9ab6808.png">

图2-4 通过命令行从数据库中查看我们的数据

　　注意我们添加了附加的一列，叫做customer_ID，作为我们参考客户的唯一方式。它是这个表中我们的主键。就像你所看见的，数据看上去就像一个电子表格，按行列布局。在后面的章中，我们将讲解在实际中定义数据库表格，存储和访问数据，但我们可以确信这些都不难。

　　在数据库中访问数据

　　你可以很容易地通过命令行工具psql查看你在Postgresql中的数据，如图2-4所示。但是，Postgresql不仅限于在命令行中使用。图2-5显示更友好的图形界面工具pgadmin III，它是可以从http://www.pgadmin.org 获得的免费工具，它从8.0开始已经和windows的Postgresql捆绑发布了。我们将在第五章看到更多的图形界面接口。

图2-5 通过pgadmin III查看数据库中的客户数据

　　通过网络访问数据

　　当然，如果我们只可以在存储数据的同一台机器访问我们的数据，这情况和通过共享单个的电子表格给不同的用户没有太多改观。

　　Postgresql是一个机遇服务器的数据库，就像前面章节所说的，一旦配置完成，我们可以通过网络接受客户的请求。虽然客户端可以和数据库服务器在同一台机器，对于多用户访问来说，这实在是小菜一碟。对于微软的windows用户，因为有ODBC驱动，所以我们可以使用任何支持ODBC的windows桌面应用程序连接到保存我们数据的服务器。图2-6显示windows上的微软Access软件访问一个在linux主机上的Postgresql数据库。这就是通过ODBC连接经过网络访问的外部表。

图2-6通过微软的Access访问以上相同的数据

　　现在我们可以同时从很多机器通过网络访问这些相同的数据。我们只有一份数据，保存在中央服务器中，可以通网络从过多个桌面程序同时访问。

　　我们将在第五章了解配置ODBC连接的技术细节。

　　处理多用户访问

　　和所有的关系数据库一样，Postgresql可以自动确保对数据库数据修改的冲突不会发生。它确保各个用户在使用数据的时候感觉好像访问全部的数据都不受限制，但在幕后，Postgresql监视着改变且避免同时更新的冲突。

　　这种表面上让很多用户可以同时读写同样的数据，但实际上确保其一致性的能力，对于数据库来说是非常重要的功能。当一个用户修改了一列，你要么看到它变化前的样子要么是变化后的样子，从不会看到修改一半的样子。

　　一个经典的例子是银行的数据库在两个账户间转账。如果在转账的时候，一些人正在生成一个汇报所有金额的报表，确保综述正确就非常重要了。对于报表来说钱在哪个账户在报表生成的时候无关紧要，但重要的是报表无法看到中间点，也就是一个账户计入借方但另一个账号还没计入贷方的时候。

　　像Postgresql一样的关系数据库都隐藏了任何中间状态，所以中间状态不会被其他用户发现。术语上说这叫隔离。报表 *** 作从转账 *** 作隔离开来，所以它看上去是在其之前或者之后发生，但绝不会同时发生。我们将在第九章讨论事务的时候回顾隔离的概念。

　　数据分片和分块

　　我们现在知道了当数据存在于表中后，访问它是多么容易。让我们首先看看我们实际上应该怎么处理数据。我们通常在大的数据集上会执行两类基本 *** 作：选择符合特定值的集合的行和选择数据的部分列。在数据库术语中，他们分别叫做选择和投影。它们听上去有点复杂，但实际上选择和投影都非常简单。

　　选择

　　让我们从选择开始，也就是我们选择行的子集。假设我们想知道我们住在Bingham的客户。让我们回到Postgresql的标准命令行工具psql来看我们怎么使用sql语言让Postgresql获得我们需要的数据。我们要用的sql命令非常简单：

　　SELECT * FROM customer WHERE town = ‘Bingham’;

　　如果你键入你的SQL语句(通过命令行的工具psql)，你需要在末尾加入一个分号。分号告诉psql已经到达命令的末尾了，因为很长的命令可能扩展到不止一行。在本书中我们通常会显示分号。

　　Postgresql通过返回customer表中所有的town为Bingham的行作为响应，就像图2-7所示。

图2-7选择数据行的子集

　　所以，挑选表中某些行的行为就叫做选择。就像你所看到的，这非常容易。不用担心SQL语句的细节，我们将在第五章正式的回顾它。

　　投影

　　现在我们来看看投影，也就是选择表中的某些列。假设我们仅仅需要选择客户表中的姓名。请记住我们分别把这两个列叫做fname和lname。选择名字的命令也非常简单：

　　SELECT fname,lname FROM customer;

　　Postgresql通过返回恰当的列作为响应，如图2-8所示：

图2-8 选择数据列的子集

　　你当然可以假设某些时候我们需要在数据中同时执行以上两种 *** 作;也就是说选择某些行中的某些列。这也可以通过非常简单的sql实现。例如，假设我们需要知道住在Bingham的客户的姓名，我们只要简单的将以上两条SQL语句组合成一条简单的语句：

　　SELECT fname,lname FROM customer WHERE town = ‘Bingham’;

　　Postgresql如图2-9一样响应我们的请求：

图2-9同时选择行和列的子集

　　有个东西需要非常注意。在很多传统编程语言例如C或者Java中，当在文件中查找数据的时候，我们需要编写一些代码扫描文件中的所有行，并每次找到我们需要的城镇的时候，打印出名字。虽然可以挤压代码让他们变成一行代码，它将是非常长和复杂的一行，不像这里的sql那么简洁。这是因为C和Java等类似的语言从本质上说还是一种过程语言。

　　你在这些语言中指出计算机怎么工作，对于sql，用术语讲这是一种描述性语言，你告诉计算机你要什么，Postgresql通过某些内部逻辑处理你要的任务。

　　如果你从没有使用过描述性语言你也许会觉得有点古怪，但是如果你开始使用这种想法，你会发现告诉计算机你要什么很明显是一种很好的想法，而不是告诉它要怎么做。你会觉得很奇怪为什么到现在为止你才遇到这么好的语言。

　　增加信息

　　到现在为止我们所碰到的都是通过我们的数据库模拟电子表格的单个工作表，而且我们也刚刚接触到sql功能的表皮。就像我们将要在本书中看到的，Postgresql一类的关系数据库富有大量的有用功能，这让他们的工作能力大大超越了电子表格。其中一个数据库最重要的能力就是它们有能力将表与表之间的数据连接在一起，这就是我们现在将要读到的。

　　使用多重表格

　　回到我们关于客户订单的问题，也就是在为每个客户存储扩展订单信息时突然让我们的简单客户表格变得非常凌乱的问题。在我们开始不知道我们的客户会有多少订单的时候要怎么存储客户的订单?你也许会从本章的标题就能猜测到，在关系数据库中解决这种问题的办法是增加一个表格存储这些信息。

　　就像我们设计客户表一样，我们从确定我们要存储的每个订单的信息开始。现在，让我们假设我们要存储下订单的客户的名字，下订单的日期，订单发出日期以及发货方式。和customer表一样，我们也需要为每条订单添加一个唯一参考数字，而不是假设哪个信息可能是唯一的。没有必要再存储所有的客户细节了。我们已经知道通过customer_ID，我们可以在customer表中找到客户的细节。

　　你也许觉得奇怪为什么我们忽略掉了订单的细节。当然，对于大多数客户，这是一个很重要的方面——他们想要知道他们订了什么内容。如果你认为这是一个和不知道客户有多少订单一样类似的问题，你非常正确。我们不知道每个订单有多少项目。重复组的问题还没远离你。我们将暂时把这个问题放下并在后面的“进行简单的数据库设计”章节解决这个问题。

　　图2-10显示了我们的订单信息表，显示了一些示例数据。而且是通过图形界面的pgadmin III工具显示的。

650) this.wIDth=650;" border="1" alt="Postgresql从菜鸟到专家数据存取设计" src="http://img.jb51.cc/vcimg/static/loading.png" src="http://image20.it168.com/201204_800x800/1035/3465960741943e14.png">

图2-10 在pgadmin III中显示的一些订单信息

　　我们没有在这张表中放太多数据，因为少量的数据更容易做实验。你将发现一个扩展的列，叫做oID，它不属于我们的用户数据。这是Postgersql内部使用的一个特殊列。当前版本的Postgresql默认为表建立这个列，但在“SELECT *”命令中它是被隐藏的。我们将在第八章讨论这个列。

　　通过关联(Join) *** 作关联一个表

　　我们现在在数据库中存有我们客户的细节，以及它们订单的概要细节。在很多情况下，这和使用两张电子表格没有区别：一张存储客户细节，另一种存储订单细节。现在是时候关注我们怎么组合使用这两张表，开始发觉数据库的能力了。我们可以同时从这两个表选择数据。这就叫做关联(Join)，在选择和投影之后，这是第三类最常用的资料检索 *** 作sql。

　　假设我们需要列出所有的订单以及下订单的客户。在像C一样的过程语言中，我们需要编写代码扫描其中一个表，也许从customer表开始，然后为每个客户打印他们的订单。这不难，但编写这些代码当然也非常费时和乏味。我敢保证你会很乐意知道我们可以通过sql更容易的找到答案：通过关联 *** 作。我们所要做的是告诉sql三件事情：

　　我们需要的列

　　我们需要检索数据的表

　　这两个表之间怎么关联

　　我们需要的命令在前面的章节中出现过：

　　SELECT * FROM customer,orderinfo WHERE customer.customer_ID = orderinfo.customer_ID;

　　你也许会猜想，它将从两个表中请求所有的列，并告诉sql customer表的customer_ID列保存的信息与orderinfo表的customer_ID保存的信息一样。注意方便的table.column标记让我们让我们有能力同时指出表名和这个表的列。命令中的“*”意味着所有的列。我们可以使用列的名字代替它来选择一些特殊的列，例如假设我们只需要名字和数量。

　　现在我们的数据库已有一些表格和数据，我们可以在图2-11中看看Postgresql的回应。

图2-11 通过一次 *** 作从两个表中选择数据

　　这有点点凌乱，因为为了匹配窗口，数据被自动换行了，但是你可以看到Postgresql怎么响应我们的查询，而不需要我们指出怎么精确地解决问题。

　　再往前走一点点，看看我们可以用在这两个表上的义工更复杂的查询。假设我们想看看不同客户给我们下订单的频率。很明显这需要一个高级一点的sql：

　　SELECT customer.Title,customer.fname,customer.lname,

　　COUNT(orderinfo.orderinfo_ID) AS “Number of orders”

　　FROM customer,orderinfo

　　WHERE customer.customer_ID = orderinfo.customer_ID

　　GROUP BY customer.Title,customer.lname;

　　这是个复杂点的sql，你可以发现我们不需要告诉sql怎样回答问题的细节;我们只要通过sql精确地指出问题。我们也只需要子一条简单的语句中完成了它。图2-12显示了Postgresql怎么响应。

图2-12 查看订单数量

　　一些数据库专家喜欢通过直接在命令行工具的窗口中直接敲入sql，它在某些时候非常有用，但它并不是所有人的爱好。如果你喜欢通过图形界面建立你的查询，这也不是问题。例如就像在本章早些时候指出的，你可以简单地通过ODBC驱动程序通过windows的图形用户界面(GUI，Graphical User Interface)访问数据库。图2-12显示在windows机器中的Acccess软件通过Postgresql的ODBC驱动程序连接到外部的表格，设计和执行的相同的查询。我们也会在第五章看到其他的GUI工具.。

　　在我们这个环境中，数据仍然存储在一台linux主机中，但用户基本不需要知道这些技术细节。通常，在本书中，我们会在命令行中使用教学sql，因为在进入更复杂的sql命令前，你可以在这里学到很多基础知识。当然，也欢迎您使用GUI工具而不是命令行工具来构建你的sql;这是你自己的选择。

　　设计表

　　到现在为止，我们在数据库中只有两个表，而且我们还没有真正讨论过关于我们怎么在每张表中做什么，除了在一些看似有理的非正式设计。这种包含表、列和关系的设计，正确的说法应该叫做模式(schema)。

　　如果数据很复杂，设计拥有数十个表的数据库模式将相当的有挑战性。数据库设计者因为善于完成这类工作而赚大把的钱。对于不到十个表格的关系简单的数据库，也许只需要按照一些基本的经验规则就可以得到很好的设计，而不需要非常正式的规则。

　　在本届，我们将看我们将开始建立的简单示例数据库，并指出用来决定我们需要哪些表的一些方法。

　　理解一些优秀的基本规则(Basic Rules of Thumb)

　　当一个数据库被设计，它经常会被规范化;也就是说，一堆规则被应用来确保数据被按照一定的方式打破。在十二章中，我们将以正式的方式观察数据库设计。但作为启动，我们需要的只是一些简单的基本规则。这些规则只是用来帮助你理解我们将用来在以后的章节探索sql和Postgresql的初始数据库。我们强烈建议你不仅仅只阅读这些规则然后就匆忙地设计一个有20个表格的数据库。至少要读完第12章你才能按照你的意愿做设计。

　　注：如果你对标准化形式非常感兴趣，我们建议你阅读Joe Celko的《sql for SmartIEs》。它有一些各种各样优秀的标准定义，以及很多E.F.Codd博士提出的关系模型的规则以及sql使用的示例。

　　规则一：将数据拆分成列

　　第一条规则是将每一块信息或者数据属性放入单独一列。这对于很多人来说都很自然，可以假设他们很自然地都是这么想的。在我们原始的电子表格中，我们已经很自然地将每个客户的信息拆分成不同的列，例如名字将和邮政编码区别开。

　　在电子表格中，这条规则只是让对数据的工作更简单;例如，按邮政编码排序等。然而在数据库中，必须将数据差分成不同的属性。

　　为什么这在数据库中这么重要?从实践的观点上看，你很难说清楚你需要地址列中的第29到第35字符之间的数据，因为它碰巧是邮政编码。很有可能有这种规则不适用的地方，因而你可能取到错误的数据块。另一个需要将数据正确拆分的原因是因为每一列数据必须有相同的类型，而不像电子表格，它对数据列的类型很宽容。

　　规则二：有一个唯一标记来标识每行

　　你会记起在本章开始的时候关于怎么标记电子表格里头每行数据的问题，我们纠结于什么可能是唯一的。就像说到的，这是因为没有主键。一般而言，不需要一个单独的列是唯一的，也许是两个列的组合，或者甚至是三个列才能唯一标记一行。你可能发现需要超过三列才能唯一标记一行数据，这可能很少见，也许可能是一个错误。

　　无论如何，必须有一个绝对必然的方法，当我查看某个特定的列或者一组列的内容时，我知道它将与表中其他所有的行有所不同。如果你无法找到这么一个列，或者需要找到超过三个列的组合来唯一标记每行，你需要增加一个列来完成这种目的。在我们的客户表中，我们添加了一个叫做customer_ID的列来标记每行。

　　规则三：移除重复信息

　　回到我们尝试存储订单信息到客户表的时候，由于重复组的问题，表格看上去凌乱不堪。我们必须为每个客户重复订单信息很多次。这意味着我们永远无法知道订单需要多少列。在数据库中，表的列数实际上在设计的时候就已经固定了。所以我们必须在我们存储数据前预先确定我们需要多少列，每个列的类型是什么以及每个列的名字。永远不要尝试在单独的一行中存储重复的数据组。

　　围绕这一约束的解决方法就是我们针对订单和客户数据的方法：拆分数据到不同的表中。然后在你需要同时从两个表取数据时你可以关联这些表到一起。在我们的示例中，我们使用customer_ID来关联这两个表格。更正式的，我们有了一个多对一的关系;也就是说，我们可以从一个单独的客户处获得多条订单。

　　规则四：正确地命名

　　这可能是最难很好实现的规则。我们怎么叫一个表或者列?表和列应该有简短且有意义的名字。如果你无法确定怎么叫一个东西，这通常意味着你的表和列设计不是很恰当。

　　很多数据库设计者都有他们自己的个人优秀规则或者命名约定用以确保表和列的命名在数据库中的一致性。不要让一些表名用单数而另一些用复数。例如，不要给一个表命名为office而另一个命名为departments，用office和department就好了。如果你为一个表的标识列的名字指定的规则为表名加“_ID”，请遵守它。如果你使用缩写，就总是使用缩写。如果表中的一个列是另一个表的键值(外键，我盟将在第十二章讲解)，尽量使用相同的名字。在一个复杂的数据库中，如果命名方式不一致，将非常令人讨厌，例如customer_ID、customer_IDent、cust_ID以及cust_no。

　　完成这表面上简单的正确命名这个目标非常有挑战性，但获得的结果是维护起来相当简单。

　　建立一个简单的数据库设计

　　我们可以通过实体关系图画出我们的数据库设计，或者模式。对于我们的两个表的数据库，这样的关系图应该像图2-14一样。

　　注：一个实体关系图是用一个图形方式表现我们数据的逻辑结构。它可以帮助我们形象化表现我们不同的数据实体怎么关联到另一个实体。

图2-14 单的实体关系图

　　这张图显示了我们的两张表，列和数据类型，以及每个列的大小，它还告诉我们customer_ID用来将两张表关联到一起。注意箭头从orderinfo表指向customer表，这意味着对于orderinfo的每一个条目，在customer表中最多只有一条对应的条目，但对于每个客户可能有多条订单。还要注意的是一些列有下划线，它指出这些列被确保为唯一的。这些列构成了这些表的主键。

　　你必须记清楚一对多关系是怎么关联的;如果在这里迷糊了，将带来一大堆问题。你还应该注意到我们很小心地命名用来关联两个表的列的名字均为customer_ID。这不是必要的。我们可以可以分别叫他们foo和bar如果我们愿意，但是就像前面小节所说的，命名一致性对于长期运行非常有帮助。

　　下一步是扩展我们非常简单的两表设计为稍微现实点的情况。我们将设计它为一个简单的订单管理数据库，叫做bpsimple。

　　在两个表之上扩展

　　很明显，到现在为止我们拥有的信息缺乏每个订单的详细条目。你可能记得我们故意省略每条订单的实际条目，并承诺会回顾这个问题。现在是时候为每个订单增加实际条目了。

　　我们碰到的问题是我们预先无法知道每条订单将会有多少条条目。这和当初我们不知道一个客户将有多少条订单的问题一样。每条订单可能会有一条、两条、三条或者一百条条目。我们必须拆分客户的订单以及订单的内容信息。基本上，我们要做的就像图2-15中所展示的一样。

图2-15在客户和订单项目上的一个尝试

　　与customer和orderinfo一样，我们将这些信息拆分到两个表，然后将它们关联起来。然而，在这里出现了一个小问题。

　　如果你仔细考虑订单和订单项目的关系，你可能发现不仅仅orderinf表的每条记录关联到很多条目，而且在不同的客户订了相同条目的湿乎乎，每个条目可能出现在很多订单里头。

　　我们将在第十二章考虑这个问题，但是现在，你需要知道的就是我们有一个标准的解决方案用以解决这类难题。你可以在这两个表之间建立第三个表，用以实现多对多关系。这很容易实现但不容易讲明白，所以尽管先建立表orderline用来连接订单表和表中的项目，就像图2-16所示。

图2-16关联客户和订单

　　我们已经创建了一个每条记录都关联到一条订单记录的表。其每一行我们都可以通过orderinfo_ID列确定它的来源并且通过item_ID列确定它引用的条目。每个单独的项目都可以出现在多个订单行里头，且每个单独的订单都可以包含很多订单行。每条订单行只引用一个单独的条目，且每条订单行只会出现在一个订单中。

　　你还会发现我们不需要为标记每行而添加唯一的ID列。这是因为组合的orderinfo_ID和item_ID总是唯一的。但是这里还有一个潜在的微妙问题。如果客户在一个订单里头订了一个条目两次将会发生什么?我们无法在orderline表里头输入另一行因为我们刚刚才说组合的orderinfo_ID和item_ID总是唯一的。我们是否需要添加另一个特殊的表来迎合包含不止一个相同项目的订单?幸运的是我们不需要那么做。有一个更简单的方法。我们只需要在orderline表里头添加数量列，这就令人满意了(查看下一节的图2-17)。

　　完成初始化设计

　　我们在完成第一版的数据库主题结构设计完成前还需要存储两块数据。我们需要存储每个产品的条码，以及我们仓库中每个项目的存量。

　　很可能每项产品有不止一个条码，因为当制造商明显地改变产品外包装的时候，他们通常会修改条码。例如你可能看到打包的“赠送20%”(通常是指销售上的捆绑赠送包)。生产商通常会为这类推销包改变条码，但实际上产品没有改变。因此，我们可能有很多条码到一个项目的关系。我们增加了一个表存储条码，如图2-17所示。

图2-17增加条码这个关系

　　注意从barcode表到item表的箭头的方向，因为可能每个条目有多个条码。同时还要注意barcode_ean列是主键，因为每个条码都需要唯一的一行，并且每个独立的条目都可以有多个条码，但没有条码可以同时属于多个项目(EAN是欧洲的产品条码标准)。

　　我们最后需要添加的是我们仓库里头每项条目的存量。如果大多数条目是在仓库中且仓库信息都很基本，我们可以简单地在item表里存储库存量。但是，如果我们提供很多产品单只有少量库存，且我们需要存储很多关于仓库中项目的信息，这将行不通了。例如在仓库运营中，我们需要存储产地信息，批次号已经过期日期。如果我们的项目文件里头有500,000条记录，但仓库中只有前1,000条，这将非常浪费。这也有一个标准的解法，就是使用叫辅助表的表格。我们将通过这种方式存储仓储信息到我们的示例数据库中，如图2-18所示。

图2-19 bpsimple数据库的设计

　　我们建立了一个新表存储供给信息(本例中为库存)，然后建立在仓库中的项目只需要的行，用来连接这些信息到主表中。注意stock表使用item_ID作为唯一键，它存储直接关联到项目的信息，使用item_ID关联到item表相关的行。箭头指向item表，因为它是主表，即使在本例中它不是一个多对一的关系。对于其他的表，下划线表明了主键(保证唯一的信息)。

　　按照现在的情况，我们的设计很明显过度复杂了，因为我们需要保存的扩展信息太少了。我们只是想演示模式设计的方法来看看它怎么做到我们的需求的，在本书后面的章节，我们将演示怎么访问像这里一样存在补充表格情况下的数据。对于那些喜欢偷偷往前翻的家伙，我们可以告诉你我们将使用的叫做外连接(outer join)。

　　注：在第八章，我们将看到我们怎么确保数据库中的表与表之间的这些关系被执行，在第十二章我们将再关注数据库设计的进一步细化。当我们到达第八章，我们将发现一些更高级的技术用于更好地管理数据库的一致性，而且我们将把这些增强设计加入到bpfinal模式中。

总结

以上是内存溢出为你收集整理的PostgreSQL从菜鸟到专家数据存取设计全部内容，希望文章能够帮你解决PostgreSQL从菜鸟到专家数据存取设计所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/1178530.html

PostgreSQL从菜鸟到专家数据存取设计

发表评论

评论列表（0条）

PostgreSQL从菜鸟到专家 数据存取设计

发表评论

评论列表（0条）

PostgreSQL从菜鸟到专家数据存取设计