数据库设计示例_示例中设计关系数据库

编程入门行业动态更新时间:2024-10-28 18:33:52

数据库设计示例

> 前言 (› Preface)

Several years ago I was a junior developer with high ambitions to create web apps. Once I got an idea to analyze all money that I spend to understand my expenses better. I needed an application which could store payments, show statistics graphs, and give more than just a colourful circle graph with all payments in a month.

几年前，我是一名初级开发人员，具有创建Web应用程序的雄心壮志。一旦有了一个想法，我就可以分析我花的所有钱以更好地了解自己的支出。我需要一个可以存储付款，显示统计图并给出一个月内所有付款的彩色圆形图的应用程序。

For these reasons, I decided to develop an app and include all the features that I wanted.I am going to discuss with you the design of a relational database, which I made for this app.

出于这些原因，我决定开发一个应用程序并包含我想要的所有功能。我将与您讨论关系数据库的设计，这是我为此应用程序设计的。

›我需要存储什么数据？ (› What data do I need to store?)

I explored this question with an example. I imagined that I bought the “Martian” book in the book shop, which located in the book shop “Book lovers”. Then, I paid for the book 5$ in cash on 20 Aug 2020 at 3:04 pm.

我用一个例子探讨了这个问题。我以为我是在书店“书迷”里买的“火星人”书。然后，我于2020年8月20日下午3:04用现金支付了这本书5美元。

Accordingly, at this moment, there are several parameters of the purchase:

因此，此刻，有几个购买参数：

the name of the product — the “Martian” book,
产品名称-“火星人”书，
the price — 5$,
价格-5美元，
the location of the shop — “Book lovers” shop,
商店的位置-“书迷”商店，
the payment type — cash,
付款方式-现金，
the currency — USD,
货币-美元，
date and time — 20 Aug 2020, 3:04 pm.
日期和时间-2020年8月20日，下午3:04。

Figure 1. Purchase parameters.

图1.购买参数。

I had been thinking about whether I need to specify an exact product or not. In a book shop, you can buy many things besides books. And what about shopping in a supermarket? A list of products can be super long.

我一直在考虑是否需要指定确切的产品。在书店里，您不仅可以买书，还可以买很多东西。那在超市购物呢？产品列表可能会很长。

›整理桌子 (› Organizing Tables)

I decided to create an abstract model for a product. I divided this parameter (product name) into category and subcategory tables, and connected them by primary and foreign keys. For the preceding example, the category is shop and subcategory is book shop.

我决定为产品创建一个抽象模型。我将此参数(产品名称)划分为类别和子类别表，并通过主键和外键将它们连接起来。对于前面的示例，类别是商店， 子类别是书店。

Figure 2. Two new tables: categories and subcategories.

图2.两个新表：类别和子类别。

Another option is to store categories and subcategories in one table. Although storing both categories and subcategories simplifies the overall model, setting them in one table can complicate selecting categories from the table — an example of this table in the Script section.

另一种选择是将类别和子类别存储在一个表中。尽管存储类别和子类别都可以简化整体模型，但是将它们设置在一个表中会使从表中选择类别变得复杂(在Script部分中此表的示例)。

Payment methods’ (cash or card) and currencies’ (USD, EUR, etc) values are known and immutable. Therefore, for storing the payment methods and currencies, I created methods and currencies tables respectively.

付款方式(现金或卡)和货币(美元，欧元等)的值是已知的并且不可变。因此，为了存储付款方式和货币，我分别创建了方法和货币表。

Figure 3. Two new tables: methods and currencies.

图3.两个新表：方法和货币。

Price, location, date and time are always different. There is no need to store these parameters in separated tables.

价格，位置，日期和时间总是不同的。无需将这些参数存储在单独的表中。

The last entry is payments, which contains all the preceding parameters.

最后一个条目是Payments ，其中包含所有前面的参数。

Figure 4. A new table: payments.

图4.一个新表：付款。

›脚本 (› Script)

The next step is to define columns and constraints.

下一步是定义列和约束。

The subcategories table contain an additional category_id column, which is a foreign key constraint, for connecting to the categories table.

子类别表包含一个附加的category_id列，该列是外键约束，用于连接到类别表。

All id columns have the serial pseudo data type. Using the integer data type is also correct.

所有id列均具有串行伪数据类型。使用整数数据类型也是正确的。

A name column in categories, currencies, and methods are unique across the tables. A name of subcategory is not unique because a subcategory can refer to many categories.

类别，货币和方法中的名称列在表中是唯一的。子类别的名称不是唯一的，因为子类别可以引用许多类别。

By default, all columns in PostgreSQL are null. I added not null constraint to specific columns, in which information is valuable and required for analysis.

默认情况下，PostgreSQL中的所有列均为null 。我没有在特定的列中添加非null约束，在这些列中，信息是有价值的且需要进行分析。

Execute the following script to create the tables:

执行以下脚本来创建表：

I included drop table if exists table_name cascade to make it easier to apply new changes. Therefore, when you add an attribute, for example, you do not need to destroy a table manually.

我添加了drop table(如果存在table_name级联) ，以便更轻松地应用新更改。因此，例如，当添加属性时，不需要手动销毁表。

An alternative table for storing categories and subcategories is:

用于存储类别和子类别的备用表是：

id is an identifier of both a category and subcategory.
id是类别和子类别的标识符。
name is a name of category and subcategory.
name是类别和子类别的名称。
category_id refers only to a category.
category_id仅指类别。

An example of the products table:

产品表的示例：

Table 1. The products table filled with random values

表1.用随机值填充的产品表

›结果 (› Result)

The payments table is a key table of this app. This table connects to categories, subcategories, methods, and currencies tables by foreign keys.

付款表是此应用的关键表。该表通过外键连接到类别， 子类别 ，方法和货币表。

Figure 5. The final relational database model.

图5.最终的关系数据库模型。

›进一步阅读 (› Further Reading)

›关于作者 (› About Author)

Jane is a Go programmer and technical writer in software engineering. She has been writing technical material for 5 years in both English and Russian. She completed her specialist degree in Information Security from Novosibirsk State Technical University, and specializes in the information security of automated systems. You can follow her on Twitter and see her other written work at publications.enthusiastic.io.

Jane是一位Go程序员和软件工程技术作家。 她用英语和俄语写作技术材料已有5年了。 她获得了新西伯利亚国立技术大学的信息安全专业学位，并专门研究自动化系统的信息安全。 您可以在 Twitter上 关注她， 并在 publications.enthusiastic.io 上 查看她的其他书面作品 。