关系数据库记录通常需要在各种状态之间过渡；例如，active，pending，deleted等。
可以使用各种数据库结构来存储此状态。
天真的解决方案
最幼稚的数据库设计只会将此status字段存储为varchar类型
-- Postgres
DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
product_id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
sku VARCHAR(50) NOT NULL,
status VARCHAR(20) NOT NULL
);
更好的解决方案
这种改进的解决方案利用ENUM类型来定义状态。
-- Postgres
DROP TYPE IF EXISTS product_status CASCADE;
CREATE TYPE product_status AS ENUM ('in stock', 'on order', 'unavailable', 'deleted');
DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
product_id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
sku VARCHAR(200) NOT NULL,
status product_status
);
这将status的可能值限制为“库存”，“订单”，“不可用”或“已删除”。
在varchar上使用enum类型有几个好处：
-
数据完整性：确保值始终在特定值集内。 VARCHAR是不可能的（除非您添加
CHECK约束）。 -
性能：
enum值根据其在enum型声明中的顺序进行内部分类，而不是其词典订单。这可能会导致更有效的分类和索引。 -
可读性：
enum类型可以通过清楚允许字段的值来使您的代码更加可读和自我记录。 -
存储：
enum值是作为整数存储的，它可以比varchar更具空间。
但是，，在enum类型中添加新值需要ALTER TYPE语句，如果您的数据库很大，这可能是一个繁重的操作。
元数据
这些枚举状态值在产品方面具有以下语义：
| value | 在（仓库）库存 | 在后订单上 | 可购买 | 可见的顺序历史 |
|---|---|---|---|---|
in stock |
是 | 否 | 是 | 是 |
on order |
否 | 是 | 是 | 是 |
unavailable |
否 | 否 | 否 | 是 |
deleted |
否 | 否 | 否 | 否 |
这些现在需要在业务逻辑中实现。
类似：
# status.py
from __future__ import annotations
from dataclasses import dataclass
@dataclass
class ProductStatus:
"""A data model for product status"""
is_in_stock: bool
is_on_back_order: bool
is_buyable: bool
is_active: bool
@classmethod
def create(cls, status: str) -> ProductStatus:
"""Create a `ProductStatus` instance derived from the given string"""
match status.lower():
case "in stock":
return ProductStatus(
is_in_stock=True,
is_on_back_order=False,
is_buyable=True,
is_active=True,
)
case "on order":
return ProductStatus(
is_in_stock=False,
is_on_back_order=True,
is_buyable=True,
is_active=True,
)
case "unavailable":
return ProductStatus(
is_in_stock=False,
is_on_back_order=False,
is_buyable=False,
is_active=True,
)
case "deleted":
return ProductStatus(
is_in_stock=False,
is_on_back_order=False,
is_buyable=False,
is_active=False,
)
case _:
raise ValueError(f"Unable to determine product status '{status}'")
这足够好，但是它确实在数据库和代码库之间分配了域。
如果我们可以在数据库中更好地表示状态，那会更好。
添加状态列
为了更好地将这些状态值存储在数据库中，我们可以在product表中添加一些列：
-- Postgres
DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
product_id SERIAL PRIMARY KEY,
title VARCHAR(250) NOT NULL,
sku VARCHAR(200) NOT NULL,
is_in_stock BOOLEAN NOT NULL,
is_on_back_order BOOLEAN NOT NULL,
is_buyable BOOLEAN NOT NULL,
is_active BOOLEAN NOT NULL
);
这是一个改进，因为我们现在拥有每个产品记录的状态属性。
但是，仍然存在一些限制。
我们不能在各种状态标志中添加任何元数据。如果我们需要一个需要其他状态标志的状态，我们还需要添加更多列。这将需要在我们的大型product表上进行ALTER操作。
标准化数据库
最好的解决方案是从product表中抽象产品状态。
为此，我们通过在product_status表中添加外键来使数据库结构归一化：
-- Postgres
DROP TABLE IF EXISTS product_status CASCADE;
CREATE TABLE product_status (
product_status_id SERIAL PRIMARY KEY,
product_status_usid VARCHAR(50) NOT NULL UNIQUE, -- unique string identifier
description VARCHAR(250) NULL,
is_in_stock BOOLEAN NOT NULL,
is_on_back_order BOOLEAN NOT NULL,
is_buyable BOOLEAN NOT NULL,
is_active BOOLEAN NOT NULL
);
DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
product_id SERIAL PRIMARY KEY,
title VARCHAR(250) NOT NULL,
sku VARCHAR(200) NOT NULL,
product_status_id INTEGER NOT NULL,
FOREIGN KEY (product_status_id) REFERENCES product_status (product_status_id)
);
接下来，让我们为各种状态值和关联的状态标志创建记录。
-- Postgres
INSERT INTO product_status
(product_status_usid, description, is_in_stock, is_on_back_order, is_buyable, is_active)
VALUES
('in stock', 'Product is in stock', true, false, true, true),
('on order', 'Product is on back order', false, true, true, true),
('unavailable', 'Product is unavailable', false, false, false, true),
('deleted', 'Product is deleted', false, false, false, false)
;
SELECT * FROM product_status;
给我们的：
product_status_id | product_status_usid | description | is_in_stock | is_on_back_order | is_buyable | is_active
-------------------+---------------------+--------------------------+-------------+------------------+------------+-----------
1 | in stock | Product is in stock | t | f | t | t
2 | on order | Product is on back order | f | t | t | t
3 | unavailable | Product is unavailable | f | f | f | t
4 | deleted | Product is deleted | f | f | f | f
(4 rows)
并添加一些垃圾产品数据：
INSERT INTO product
(title, sku, product_status_id)
VALUES
('EcoBoost Portable Charger', 'SKU-ECB-1234', 1),
('AquaPure Water Filter', 'SKU-AQPF-5678', 2),
('SolarGlow Garden Lights', 'SKU-SGL-9101', 3),
('FitFlex Yoga Mat', 'SKU-FFYM-1121', 4),
('BreezeAir Conditioner', 'SKU-BAC-3141', 1),
('CrispSound Bluetooth Speaker', 'SKU-CSBS-5161', 2),
('SmoothBlend Juicer', 'SKU-SBJ-7181', 3),
('QuickCook Microwave Oven', 'SKU-QCMO-9201', 4),
('UltraView Binoculars', 'SKU-UVB-1221', 1),
('ProFit Running Shoes', 'SKU-PFRS-3241', 1)
;
用户的价值
唯一的字符串标识符（USID）product_status_usid值可用于减少查询时的认知负载。
例如：
SELECT
product.title,
product.sku,
product_status.description status
FROM
product
JOIN
product_status
ON
product.product_status_id=product_status.product_status_id
WHERE
product_status_usid='in stock'
;
title | sku | status
---------------------------+---------------+---------------------
EcoBoost Portable Charger | SKU-ECB-1234 | Product is in stock
BreezeAir Conditioner | SKU-BAC-3141 | Product is in stock
UltraView Binoculars | SKU-UVB-1221 | Product is in stock
ProFit Running Shoes | SKU-PFRS-3241 | Product is in stock
(4 rows)
一眼就比
更容易理解
SELECT
product.title,
product.sku,
product_status.description status
FROM
product
JOIN
product_status
ON
product.product_status_id=product_status.product_status_id
WHERE
product.product_status_id=1
;
同样，当参考代码中的这些外键记录时，我们不想将主密钥整数值用作常数（因为这些是严格说话的不是常数）标识符。相反，我们想为此使用USID。
可扩展性
添加新状态
我们是否需要在系统中添加新状态（例如pre-order），它就像在product_status表中添加新记录一样简单。我们可能也想扩展其结构。幸运的是，更改product_status桌子比对大型product桌子做的更快且风险较小。
-- Postgres
ALTER TABLE
product_status
ADD COLUMN
is_pre_order BOOLEAN NOT NULL DEFAULT false
;
INSERT INTO
product_status
(
product_status_usid,
description,
is_in_stock,
is_on_back_order,
is_buyable,
is_active,
is_pre_order
)
VALUES
(
'pre-order',
'Product is available for pre-order',
false,
false,
true,
true,
true
)
;
添加状态日志
这个抽象为我们提供的另一个好处是能够轻松扩展我们的体系结构的能力。
例如，将表格添加到日志状态更改。
-- Postgres
DROP TABLE IF EXISTS product_status_log CASCADE;
CREATE TABLE product_status_log (
product_id INTEGER NOT NULL,
product_status_id INTEGER NOT NULL,
logged_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
FOREIGN KEY (product_id) REFERENCES product (product_id),
FOREIGN KEY (product_status_id) REFERENCES product_status (product_status_id)
);
CREATE INDEX idx_product_status ON product_status_log (product_id, product_status_id);
我们有一个不错的日志
SELECT
product_status.product_status_usid status,
log.logged_at
FROM product
JOIN product_status_log log
ON product.product_id=log.product_id
JOIN product_status
ON log.product_status_id=product_status.product_status_id
WHERE
product.sku='SKU-SGL-9101'
ORDER BY
log.logged_at ASC
;
status | logged_at
-------------+-------------------------------
in stock | 2023-08-07 22:46:21.388738+02
on order | 2023-08-07 22:46:57.509255+02
in stock | 2023-08-07 22:47:01.686259+02
on order | 2023-08-07 22:47:19.070394+02
in stock | 2023-08-07 22:47:26.662571+02
unavailable | 2023-08-07 22:47:31.837687+02
deleted | 2023-08-07 22:47:37.574532+02
(7 rows)
欢呼！