下载掌阅APP，畅读海量书库
立即打开
本节的目标是简略地介绍一下如何打造一个关系型系统来存储C360数据。本节不能作为建设此类系统架构的完整介绍。我们期望的是引入尽量少的概念来帮助你理解使用关系型系统做C360应用程序的复杂度。
为了说明从数据建模到查询的过程，我们会使用与表2-1中相同的数据。方便起见，我们在这里也提供了数据，如表3-1所示。回顾这些数据产生的过程、含义和详细情况，可以重温2.2节。
表3-1：在本章中用以阐明技术选择的样本数据
我们会用SQL和Postgres两种技术来介绍关系型实现。SQL是Structured Query Language（结构化查询语言）的缩写，是用来和关系型数据库通信的编程语言。我们选用了Postgres关系型数据库管理系统（RDBMS），因为它源于开源社区并且被广泛运用。
在概念模型得到认可之后，如图2-1所示，你可以开始设计关系型数据库了。通常来说，你会想画一个实体关系图，即ERD。ERD是数据模型的逻辑表示，也是关系型数据库典型的设计开端。
在图3-2中，每个方形代表了一个实体，最终会变成关系型数据库中的一张表。每个实体的属性，或者说描述性属性，列在每个方形的内部。如数据中所见，每个实体都会有一个唯一标识符。客户会被customer_id唯一标识，账户用account_id，以此类推。客户也有名字，在更大的应用中，还会有其他的属性。
图3-2：C360应用程序的关系型实现的ERD
图3-2中实体之间的菱形代表实体之间的关系。关系之间的基数展示在菱形的上下或左右。在该数据中，我们有两种关系：一对多和多对多。
让我们从一对多关系开始，即客户和信用卡。在这个例子中，一位客户可以有多张信用卡，但是一张信用卡只能有一个客户。这种一对多的关系描述了客户和信用卡之间的基数，在图3-2中用1和n表示。
数据中的另外一种关系类型是多对多关系。我们的数据中有两种多对多关系：客户到账户，以及客户到贷款。从数据中可以得知，一位客户可以有多个账户，一个账户可以有多位客户。贷款同理。我们说客户到贷款是多对多关系，并在图3-2中用n和m表示这种关系。
在建表和插入数据之前，我们需要把逻辑数据模型翻译成物理数据模型。具体来说，我们需要把图3-2中ERD里的实体和关系转换为有主键和外键的表。
在这个实现中，我们需要两种类型的键：主键和外键。主键是一种唯一标识的数据，例如一个客户ID或者信用卡号，可以用其来访问表中的信息。外键是一种唯一标识的数据，用来访问不同的表中的信息，例如，将客户ID与他们的信用卡信息一起存储。我们在客户的表中同时存储客户ID和信用卡信息，这样我们就可以用它在不同的表中来获取所有信息。
让我们看一下图3-2中的键和数据是如何映射到图3-3中的物理数据模型的。
图3-3：C360应用程序的关系型实现的物理数据模型
我们至少要浏览图3-3中的四张表——每个实体一张表。具体来说，每种实体类型在图3-3中都有一张表：客户（customer），账户，贷款和信用卡。对于它们中的每张表，都有额外的属性来描述这个实体。对每个实体来说最重要的属性都是它的主键。每个主键都用PK在行的一边标明。相应地，每张表的主键是customer_id、acct_id、loan_id和cc_num。我们会用这些唯一标识符来访问表中一行具体的信息。
在我们讨论图3-3中另外两张表之前，我们来看一下信用卡表。这张表同时有一个主键和一个外键。我们使用这张表的外键来跟踪ERD中的一对多关系。customer_id是外键（使用FK标明），会提供给我们将信用卡信息关联回一位唯一的客户的能力。在物理数据模型中建立一对多关系，就如同加一个外键将你指引回另一张实体表一样简单。
我们最后再来看下物理数据模型中的拥有和借有表。它们是连接表（join table），让我们能够在物理上存储数据中的多对多关系。拥有表存储客户和他们拥有的账户之间的多对多关系。借有表存储客户和他们的贷款之间的多对多负债关系。由于每个客户只能拥有一个账户，只能借有一次贷款，所以这些连接表的主键是两个外键的组合。
例如，拥有表存储了关于表中每一行的至少两个信息：客户的唯一标识符和账户的唯一标识符。给定该表的一条记录，我们可以访问客户的唯一标识符来连接回客户表，也可以访问账户的唯一标识符来连接回账户表。这个连接表是表示关系系统中多对多连接的一种常见方式。
基于物理数据模型，我们一起来创建表并把表3-1中的样本数据插入数据表。
首先，我们想创建客户表。它的最终数据模型如图3-4所示。
图3-4：关系型实现的客户表
创建客户表的SQL语句如下：
数据中有五位客户。我们把这五位客户的数据插入客户表：
关系型数据库中的数据有五个实体，如图3-5所示。
图3-5：关系型数据库中的客户数据
接下来创建另外三张实体表，它们分别是账户、贷款和信用卡。它们的最终数据模型如图3-6所示。
图3-6：关系型实现的账户、贷款和信用卡表
我们从创建账户和贷款两张表开始：
接下来给账户和贷款表插入数据：
这里最后一张需要在关系型数据库里创建的实体表就是信用卡表。因为信用卡和客户之间有一对多关系，所以还需要插入客户的ID作为外键。我们用下面的语句创建表：
回顾表3-1中的数据，可以找到每张信用卡以及拥有它的客户。根据这个信息，我们可以写出下面的语句来把数据插入关系型数据库：
到此为止我们的关系型数据库一共有四张表及其数据，如图3-7所示。
在我们的关系实现中，要创建的最后两个表是用于从客户到账户和贷款的多对多的连接。首先，让我们创建一个表来连接客户和账户，如图3-8所示。
我们在SQL中用如下语句创建这张表：
图3-7：关系型数据库里四张实体表的数据
图3-8：客户和账户的连接表
回顾表3-1中的数据，可以找出如下数据并插入到拥有表中：
现在拥有表中有一些数据了（图3-9），就可以看到客户和账户之间的数据是如何关联起来的。
图3-9：客户、账户及连接表数据
创建关系型数据库的最后一步是创建借有表，用以关联客户到他们的贷款，或者反过来。最后的数据模型就是这张连接表的，如图3-10所示。
图3-10：从客户到贷款的连接表
在SQL里我们通过下述语句在关系型数据库中创建该表：
我们最终可以提取表3-1中最后的关联关系，并把所有有贷款的客户插入借有表：
我们的关系型数据库的数据全景图如图3-11所示。
图3-11：完整的关系型数据库数据映射
现在数据已经在关系型数据库里了，我们要用四个基本问题对我们的C360应用程序提问：
1.这位客户使用了哪些信用卡？
2.这位客户拥有哪些账户？
3.这位客户借有哪些贷款？
4.我们对这位客户有什么了解？
对于关系型系统，有两个原因使得我们需要以特定的顺序问这四个问题。首先，我们想用一种自然的渐进的方式来向数据库索取一个人的详细信息。其次，我们对这些问题进行了结构化设计，使技术实现建立在每个语句之上，以最后的SQL语句作为结束。
问题1：这位客户使用了哪些信用卡
首先，使用关系型数据查找customer_0所拥有的信用卡。在信用卡表中可以直接找到这个查询所需的数据。如果仅需要信用卡信息，可以用如下SQL查询来检索表：
这条查询会返回如下数据：
但更有可能的是你真的想查看客户的数据以及他们的信用卡信息。这就需要连接客户表和信用卡表。在SQL里，可以这样做：
这个查询会返回如下数据：
由于客户和信用卡之间是一对多的关系，因此在查询客户和他们的信用卡信息时只需要一个连接语句。当我们需要查看关于客户及其账户的数据时，事情就变得有点棘手了。
问题2：这位客户拥有哪些账户
接着，让我们查询关系型数据库来回答这个问题：customer_0拥有哪些账户？对于这个问题，我们将需要使用连接表拥有表来连接客户表和账户表。其SQL查询如下：
这个查询从访问客户表中的customer_0的数据开始。接下来，我们找到拥有表中所有匹配customer_id的外键对。对于这位客户，拥有表中只有一个实体，因为customer_0只有一个账户。从这里开始，跟随账户的外键到账户表中检索账户信息。结果数据类似于此：
问题3：这位客户借有哪些贷款
下面的问题使用同样的方式，使用借有连接表从客户表追踪到贷款表。这个问题要求客户信息和贷款细节一起返回。对于这个问题，我们使用customer_4的数据。SQL语句如下：
结果数据如下：
问题4：我们对这位客户有什么了解
这些问题中的每一个都是在为C360应用程序的主查询建立所需的各个部分：对于一位特定客户，告诉我有关他的一切信息。这个查询将前面三个查询中的每一个都集中到一个语句中。下面的SQL语句使用我们关系数据库中的所有六个表来查找一个客户的所有信息。让我们在最后这个例子中再次使用customer_0：
这将会把数据库中关于customer_0的数据转换成如下结果：
我们在本节中演示的四个问题只接触到了SQL查询语言最浅显的部分。我们只用到了SQL的最基本元素：SELECT-FROM-WHERE，以及基本的连接。尽管我们的问题听起来很简单，所需的查询却变得越来越复杂。要在这个系统中跟踪数据，了解哪些数据与哪个客户有关，就更难了。