技术评估 (Technical Evaluations)

It is a common situation where the value of a field is restricted to a list of valid options.

在一种常见的情况下，字段的值仅限于有效选项的列表。

For examples,

举些例子，

• Genders: male, female , …

  性别 ： male ， female ，…

• Currencies: USD, SGD, CAD, AUD, …

  货币 ： USD ， SGD ， CAD ， AUD …

• Countries: us, uk , sg… (200+ options)

  国家 ： us ， uk ， sg ...(200+选项)

• Statuses: pending , processing , failed , completed , …

  状态 ： pending ， processing ， failed ， completed ...

• Genres: romance , comedy , action , …

  类型 ： romance ， comedy ， action ……

数据验证和完整性 (Data validation and integrity)

Although data validation can be done at the APPLICATION LOGIC LAYER either

尽管可以在APPLICATION LOGIC LAYER上完成数据验证

• on the client-side (form validations)
  在客户端(表单验证)
• OR on the server-side (API or model validations),
  或在服务器端(API或模型验证)，

such validations can be bypassed by going straight to the lower layer.

可以直接进入下一层来绕过此类验证。

The safer way to ensure data integrity is by using DATABASE LEVEL CONSTRAINTS or VALIDATIONS.

确保数据完整性的更安全方法是使用“ 数据库级别约束”或“ 验证” 。

This article would be discussing 4 techniques in which we can restrict the values to a set of valid options in PostgreSQL (also applicable to other databases)

本文将讨论4种技巧 ，其中我们可以将值限制为PostgreSQL中的一组有效选项(也适用于其他数据库)

1. Enumerated Types

   枚举类型

2. Simple CHECK constraints

   简单的CHECK约束

3. CHECK constraints with FUNCTION

   使用FUNCTION检查约束

4. FOREIGN KEYS

   外键

Each of the approaches has distinguished PROS, CONS, and therefore, is suitable for different use cases.

每种方法都具有不同的PROS和CONS ，因此适用于不同的用例 。

评估方面 (Evaluation aspects)

• ReadabilityHow easy to read and understand the validation

  可读性 多么容易 阅读 和 理解 验证

• UsabilityHow easy to retrieve the list of options.The number of options each can support.

  可用性 如何轻松 检索 选项列表。每个选项可以支持的数量。

• MaintainabilityHow easy to ADD, UPDATE, REMOVE items from the list of options

  可维护性 如何轻松地 从选项列表中 添加，更新，删除 项目

The discussion would assume that

讨论将假定

• The codebase is versioned

  代码库已版本化

• The database is managed with a migration strategy

  通过迁移策略管理数据库

• Each version of the codebase supports certain versions of the database schema

  每个版本的代码库都支持某些版本的数据库架构

1. ENUM —枚举类型 (1. ENUM — Enumerated Types)

This is the most straight-to-the-point approach. We declare a new data type and assign it to the target fields.

这是最直接的方法。 我们声明一个新的数据类型并将其分配给目标字段。

E.g. Create a type called gender with 2 possible values male, female, and use it to denote the type of the gender field in the users table.

例如，创建一个具有2个可能值male ， female的类型，称为gender ，并使用它来表示users表中gender字段的类型。

ENUM是架构的一部分 (ENUM is part of the schema)

• Any changes to ENUM would require schema changes (database migrations) and potential data changes (data migrations)

  对ENUM所做的任何更改都需要架构更改 (数据库迁移)和潜在的数据更改 (数据迁移)

• NO inversion-of-control

  没有控制反转

优点 (Pros)

• ENUM provides explicit purpose-specific data typesGood for abstraction

  ENUM提供了明确的针对特定目的的数据类型

  E.g. Saying a field is of

  例如，说一个领域是

  GENDER type is easier to relate than saying that field is of TEXT type with 2 possible values

  GENDER 类型比说字段是TEXT 更容易关联 输入2个可能的值

• The table schema is readable

  表模式是可读的

• The declared ENUM types are reusable for other fields or in other tables

  声明的ENUM类型可用于其他字段或其他表中

• The list of ENUM values can be retrieved to support the view layer

  可以检索ENUM值列表以支持视图层

缺点 (Cons)

• The list of options CANNOT be controlled by end-users since ENUM is part of the schema

  选项列表不能由最终用户进行控制，因为ENUM是架构的一部分

• If additional information related to the value is required, additional tables would still be required
  如果需要与该值有关的其他信息，则仍将需要其他表

• It requires additional looks into the ENUM definition when querying

  查询时需要额外查看ENUM定义

  This is due to the

  这是由于

  ENUM definition is stored in system catalogs instead of in the table definition. However, this does NOT impose any major performance issues.

  ENUM定义存储在系统目录中，而不是表定义中。 然而，这并不对任何重大的性能问题。

• An additional query is required to see the list of options.

  需要其他查询才能看到选项列表 。

• String operations and functions do not work on ENUMThis is due to ENUM being a separate data type from the built-in data types like NUMERIC or TEXT. This can be overcome by casting ENUM values to TEXT when being operated. However, it can be a pain when using ORM.

  字符串操作和函数在ENUM上不起作用这是由于ENUM是与NUMERIC或TEXT类的内置数据类型不同的数据类型。 可以通过在操作时将ENUM 值 强制转换 为 TEXT来解决。 但是，使用ORM可能会很痛苦。

• Extra effort and code to declare and maintain enum outside of the table.
  在表外声明和维护枚举需要额外的精力和代码。

合适的用例 (Suitable use-cases)

• Genders: male, female , …

  性别 ： male ， female ，...

• Statuses: pending , processing , failed , completed , …

  状态 ： pending ， processing ， failed ， completed ...

2.简单的检查约束 (2. Simple CHECK constraints)

This approach fits straight to the Validation purpose.

这种方法完全符合验证目的。

CHECK约束是模式的一部分 (CHECK constraints are part of the schema)

• Any changes to the list of options would require schema changes (database migrations) and potential data changes (data migrations)

  对选项列表的任何更改都需要架构更改 (数据库迁移)和潜在的数据更改 (数据迁移)

• NO inversion-of-control

  没有控制反转

优点 (Pros)

• What you see is what you get
  你所看到的就是你得到的
• Significantly shorter than other approaches
  比其他方法明显短
• The inline constraint definition is easily inspected without additional queries
  内联约束定义易于检查，而无需其他查询

• The field is also of TEXT type, on which TEXT operators and functions can be applied

  该字段也是TEXT类型，可以在其上应用TEXT运算符和函数

缺点 (Cons)

• The constraint would blow up if the list of options exceeding a certain threshold. E.g. 200+ options for country codes.
  如果选项列表超过特定阈值，则约束将破裂。 例如，国家代码的200多个选项。

• The constraint is NOT reusable for other fields or tables with similar purposes. For examples, the list of supported gender or country are probably consistent across different tables.

  该约束不可用于具有类似目的的其他字段或表。 例如，受支持的gender或country列表在不同表格中可能是一致的。

• The list of supported values CANNOT be retrieved

  支持的值列表不能被检索

合适的用例 (Suitable use-cases)

• Statuses: pending , processing , failed , completed , …

  状态 ： pending ， processing ， failed ， completed ...

3.使用FUNCTION检查约束 (3. CHECK constraints with FUNCTION)

FUNCTION brings reusability into inline CHECK constraints.

FUNCTION将可重用性引入到内联CHECK约束中。

Most of the PROS, CONS, and suitable use-cases are similar to Simple CHECK constraints except

大多数PROS ， CONS和合适的用例类似于Simple CHECK约束，除了

• Reusability

  可重用性

• Additional code and effort is required
  需要额外的代码和精力

4.外键 (4. Foreign Keys)

In this approach, the list of options is stored as records of a table instead of being part of the schema.

在这种方法中，选项列表存储为表的记录 ， 而不是 架构的一部分 。

Foreign keys are used to ensure the validity of the values in the target tables.

外键用于确保目标表中值的有效性。

选项列表不是架构的一部分 (The list of options is NOT part of the schema)

• Inversion-of-control IS POSSIBLE

  控制反转是可能的

  Inversion-of-control IS POSSIBLEThe responsibility of maintaining the list of options can be delegated to users.

  控制反转是可能 的维护选项列表的责任可以 委托 给用户。

• Any changes to the list of options would NOT require schema changes (database migrations). It would ONLY require potential data changes (data migrations).

  对选项列表的任何更改 都不需要更改架构 (数据库迁移)。 它仅需要潜在的数据更改 (数据迁移)。

优点 (Pros)

• It supports lists with many options

  它支持带有许多选项的列表

  The countries or genres examples would be a good fit

  国家或体裁的例子很合适

• The application logic would be agnostic of the values in the list of options.

  应用程序逻辑将与选项列表中的值无关 。

• It allows reusability on other fields and tables just by adding foreign keys

  只需添加外键，即可在其他字段和表上重用

• The field is also of TEXT type, on which TEXT operators and functions can be applied

  该字段也是TEXT类型，可以在其上应用TEXT运算符和函数

缺点 (Cons)

• Additional tables to maintain — which is a SIGNIFICANT cost

  要维护的其他表-这是一笔可观的费用

  Additional tables to maintain — which is a SIGNIFICANT costLet’s imagine adding a new table just to store the list of genders

  要维护其他表格-这是一笔可观的费用让我们想象一下添加一个新表格只是为了存储性别列表

合适的用例 (Suitable use-cases)

The FOREIGN KEYS approach is extremely useful when the number of options is significant or the list of options frequently changes. This is especially important when you need more information than just value validations (e.g. exchange rates for currencies, names of countries, …).

当选项数量很多或选项列表频繁更改时，FOREIGN KEYS方法非常有用。 当您需要更多信息而不仅仅是价值验证(例如，货币汇率，国家名称，...)时，这尤其重要 。

• Currencies: USD, SGD, CAD, AUD, …

  货币 ： USD ， SGD ， CAD ， AUD …

• Countries: us, uk , sg… (200+ options)

  国家 ： us ， uk ， sg ...(200+选项)

• Genres: romance , comedy , action , …

  类型 ： romance ， comedy ， action ……

摘要 (Summary)

Let’s put the approaches side-by-side.

让我们并列介绍这些方法。

结论 (Conclusion)

We have discussed 4 ways to restrict values to a list of options in PostgreSQL. Again, none of them is a silver bullet. The pros and cons should be considered in given contexts to decide suitable approaches. I hope this comparison would help you make better choices in such situations.

我们讨论了将值限制为PostgreSQL中的选项列表的4种方法。 同样，它们都不是灵丹妙药。 在给定的上下文中应考虑利弊，以决定合适的方法。 我希望这种比较可以帮助您在这种情况下做出更好的选择。

Is there anything missing? Let me know in the responses.

缺少什么吗？ 在答复中让我知道。

Thanks for reading!

谢谢阅读！