Reading: Data Model Example in Relational vs Document关系型数据库和文档型数据库中的数据建模

Objectives 目标

After completing this reading, you will be able to:
完成本阅读后，您将能够：

• Compare modeling the same data in relational and document databases.
  比较在关系型数据库和文档型数据库中建模相同数据。
• Describe the complexities of querying data.
  描述查询数据的复杂性。
• Describe how data duplication affects data modeling
  描述数据重复如何影响数据建模
• Describe the considerations associated with evolving the schema.
  描述与演化模式相关的考虑因素。

Introduction 引言

First, compare the information organization in a relational database to a NoSQL document database.
首先，比较关系数据库中的信息组织与 NoSQL 文档数据库中的信息组织。

A relational database organizes data into tables with predefined schemas and relationships among tables. In a relational model, you avoid duplicating any data so that the data is stored once and updated in one location. Wherever and whenever the data is needed, the data is referenced. That 's the relational aspect of the data.
关系数据库将数据组织成具有预定义模式和表之间关系的表格。在关系模型中，你避免数据重复，以便数据存储一次并在一个位置更新。无论何时何地需要数据，都会引用数据。这就是数据的 关系性方面。

On the other hand, an excellent document-based design starts with using that data.
另一方面，优秀的基于文档的设计始于使用这些数据。

Let's use the example of library books and data organization.
让我们以图书馆书籍和数据组织为例。

Example: A library book catalog 示例：图书馆书籍目录

Library book catalogs store information about books and their authors.
图书馆书籍目录存储有关书籍及其作者的信息

Using a relational database for a library books catalog 使用关系数据库为图书馆书籍目录

If you work with library book data using a relational database, you will create the Books, Authors, Genres and BookGenres tables:
如果你使用关系数据库处理图书馆书籍数据，你将创建 Books、Authors、Genres 和 BookGenres 表：

Books 书籍

Authors 作者

Genres 体裁

BookGenres 图书分类

Using a document database for a library books catalog 使用文档数据库为图书馆图书目录

In contrast, a document database is simplified and displayed as a single document with all the required information.
相比之下，文档数据库被简化并显示为一个包含所有必要信息的单个文档。

{
  "_id": 1,
  "title": "The Great Gatsby",
  "author": "F. Scott Fitzgerald",
  "isbn": "978-0743273565",
  "published_year": 1925,
  "genres": ["Fiction", "Classic"]
}

Next, compare the complexities of querying the data in relational and document databases.
接下来，比较在关系型数据库和文档型数据库中查询数据的复杂性。

Complexities of querying data 查询数据的复杂性

Let's examine the complexities of querying the same book data using a relational database compared to a NoSQL database.
让我们比较使用关系型数据库与 NoSQL 数据库查询相同书籍数据的复杂性。

Querying book data using a relational database 使用关系型数据库查询书籍数据

Now, query the data. You will need all the book details at once. You will structure your SQL query by performing a JOIN among those tables. Here's your SQL query example:
现在，查询数据。您需要一次性获取所有书籍详细信息。您将通过在这些表之间执行 JOIN 操作来构建 SQL 查询。以下是您的 SQL 查询示例：

SELECT Books.Title AS BookTitle,
       Authors.AuthorName AS Author,
       GROUP_CONCAT(Genres.GenreName) AS Genres
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID
JOIN BookGenres ON Books.BookID = BookGenres.BookID
JOIN Genres ON BookGenres.GenreID = Genres.GenreID
GROUP BY Books.Title, Authors.AuthorName;

The number of joins, use of the GROUP_CONCAT function, and the GROUP BY function make this query complex. Here's how the query breaks down.
JOIN 的数量、GROUP_CONCAT 函数的使用以及 GROUP BY 函数的使用使此查询变得复杂。以下是查询的分解方式。

• The SELECT command obtains the book title, author name, and a concatenated list of genres for each book.
  SELECT 命令获取每本书的书名、作者姓名以及一个连接起来的类型列表。
• The JOIN command joins the Books table with the Authors table on the AuthorID field to get the author’s name.
  JOIN 命令通过 AuthorID 字段将 Books 表与 Authors 表连接起来以获取作者姓名。
• The JOIN command joins the Books table with the BookGenres table on the BookID field to associate books with genres.
  JOIN 命令通过 BookID 字段将 Books 表与 BookGenres 表连接起来以将书籍与类型关联起来。
• The JOIN command joins the BookGenres table with the Genres table on the GenreID field to get genre names.
  JOIN 命令通过 GenreID 字段将 BookGenres 表与 Genres 表连接起来以获取类型名称。
• The query uses the GROUP_CONCAT function (the exact function may vary depending on your SQL database system; GROUP_CONCAT is used in MySQL and STRING_AGG in PostgreSQL) to concatenate multiple genre names into a single string.
  该查询使用 GROUP_CONCAT 函数（具体函数可能因 SQL 数据库系统而异；GROUP_CONCAT 用于 MySQL，STRING_AGG 用于 PostgreSQL）将多个流派名称连接成一个字符串。
• The query uses GROUP BY to display the results by book title and author name.
  该查询使用 GROUP BY 按书籍标题和作者名称显示结果。

Here is an example output:
以下是一个示例输出：

Querying book data using a NoSQL document database 使用 NoSQL 文档数据库查询书籍数据

You already know the output you need and that the output is a first-order criterion in a document database. Remember that your book document looks like this:
您已经知道所需的输出，并且该输出是文档数据库中的一级标准。请记住，您的书籍文档看起来是这样的：

{
  "_id": 1,
  "title": "The Great Gatsby",
  "author": "F. Scott Fitzgerald",
  "isbn": "978-0743273565",
  "published_year": 1925,
  "genres": ["Fiction", "Classic"]
}

When creating a document database query, using MongoDB, for example, you specify empty brackets as the first argument denoting you want to see all documents. The second argument, called projection, allows you to choose which fields to present to the client information. You won 't always need to show all of the fields all of the time. And you'll notice the simplicity of this query, as it didn 't need any joins with other collections.
在创建文档数据库查询时，例如使用 MongoDB，您将空括号指定为第一个参数，表示您希望查看所有文档。第二个参数，称为投影，允许您选择向客户端展示哪些字段。您并不总是需要展示所有字段。您还会注意到这个查询的简单性，因为它不需要与其他集合进行任何连接。

Here is your query:
这是您的查询：

Db.books.find({}, { title:1, author: 1, genres: 1 })

The data you need to fulfill the request is already in the document and does not require any joins with other collections.
满足该请求所需的数据已存在于文档中，无需与其他集合进行任何连接。

Important! What about data duplication? 重要！数据重复问题如何处理？

Data duplication is a common practice and is known as "denormalization." Data duplication can improve read performance by avoiding complex joins and queries. However, you will face challenges with data consistency and increased storage requirements.
数据重复是一种常见做法，称为“反规范化”。数据重复可以通过避免复杂的连接和查询来提高读取性能。然而，您将面临数据一致性和存储需求增加的挑战。
For example, imagine that the author, J.R.R. Tolkien, wants to be known by his full name, John Ronald Reuel Tolkien, and you need to make this change in a relational database and a document database. How would you implement this change in these two different types of databases?
例如，假设作者 J.R.R.托尔金希望以全名 John Ronald Reuel Tolkien 为人所知，你需要在一个关系型数据库和一个文档型数据库中做出这个变更。你将如何在这两种不同类型的数据库中实现这个变更？

Next, explore the considerations associated with evolving a schema.
接下来，探讨与模式演变相关的问题。

Schema Evolution Considerations 模式演变考虑因素

Changing the schema in a database can be necessary to accommodate evolving application requirements. However, a significant difference exists in how a relational and document database enables schema changes.
在数据库中更改模式可能是为了适应不断发展的应用程序需求。然而，关系型数据库和文档型数据库在实现模式更改方面存在显著差异。

In relational databases, changing the schema typically involves modifying existing tables, adding new tables, or altering relationships between tables. Here 's an example:
在关系型数据库中，更改模式通常涉及修改现有表、添加新表或更改表之间的关系。以下是一个示例：

ALTER TABLE table_name ADD column_name data_type;

After making schema changes, you might need to migrate existing data to match the new schema, which can involve data transformation and migration scripts.
在进行了模式更改后，您可能需要将现有数据迁移到以匹配新模式，这可能涉及数据转换和迁移脚本。

In document databases like MongoDB, the schema is typically more flexible, and you can often add or remove fields to documents (individual documents, as there is generally no collection-wide enforced schema) without a predefined structure. Here 's an example:
在像 MongoDB 这样的文档数据库中，模式通常更灵活，您可以通常向文档中添加或删除字段（单个文档，因为通常没有强制执行的跨集合模式）而无需预定义结构。这里有一个示例：

Db.books.update ({ _id: 1 },{$set: {Newfield: "Some value"}});

Summary 摘要

In this reading, you learned that:
在本阅读中，你学习了：

• A relational database organizes data into tables with predefined schemas and relationships among tables.
  关系型数据库将数据组织成具有预定义模式和表之间关系的表。
• A good document-based design starts with the usage of data.
  优秀的基于文档的设计始于数据的运用。
• The number of SELECT, JOIN, GROUP, and additional commands contribute to the complexity of building a relational database query.
  SELECT、JOIN、GROUP 以及其他命令的数量会增加构建关系数据库查询的复杂性。
• For document database queries, you won 't always need to show all of the fields all of the time, and you'll notice the simplicity of these queries, as the queries might not need joins with other collections.
  对于文档数据库查询，你并不总是需要展示所有字段，而且你会注意到这些查询的简单性，因为查询可能不需要与其他集合进行连接。
• In relational databases, changing the schema typically involves modifying existing tables, adding new tables, or altering relationships between tables.
  在关系数据库中，更改模式通常涉及修改现有表、添加新表或更改表之间的关系。
• In document databases, such as MongoDB, the schema is typically more flexible, and you can often add or remove fields to documents without a predefined structure.
  在文档数据库中，例如 MongoDB，模式通常更加灵活，你通常可以在没有预定义结构的情况下向文档中添加或删除字段。

Congratulations! You have completed this reading and are ready for the next topic. 恭喜！您已完成本次阅读，可以进入下一个主题。