Текстовые идентификаторы в проектировании БД PostgreSQL

/orders/123
/products/345/variants/1

Что делает текстовые идентификаторы привлекательными?

Когда идентификатор — это не просто идентификатор

Разве UUID не являются решением всей этой проблемы?

Примеры из реальной жизни

CREATE TABLE sessions (
    token TEXT PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(user_id),
    ...
);

CREATE TABLE products (
    sku TEXT PRIMARY KEY,
    label TEXT NOT NULL,
    ...
);

CREATE TABLE documents (
    document_id TEXT PRIMARY KEY,
    ...
);

CREATE TABLE session_logs (
    log_id BIGINT GENERATED ALWAYS AS IDENTITY,
    token TEXT NOT NULL REFERENCES sessions(token),
    ...
);

CREATE TABLE product_reviews (
    review_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_sku TEXT NOT NULL REFERENCES products(sku),
    ...
);

CREATE TABLE customer_orders (
    order_id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL REFERENCES customers(customer_id),
    ...
);

CREATE TABLE document_revisions (
    revision_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document_id TEXT NOT NULL REFERENCES documents(document_id),
    ...
);

Эффективное повторное введение текстовых идентификаторов

1. Введите суррогатный ключ

CREATE TABLE products (
    product_id INT GENERATED BY DEFAULT AS IDENTITY,
    sku TEXT NOT NULL,
    label TEXT NOT NULL,
    ...
);

CREATE INDEX products_by_sku ON products(sku);

CREATE TABLE product_reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    ...
);

2. Используйте таблицы сопоставления для большей гибкости

* 	Update identifiers without affecting the parent entity.
* 	Maintain a history of text identifiers linked to a specific entity.

CREATE TABLE products (
    product_id INT GENERATED BY DEFAULT AS IDENTITY,
    label TEXT NOT NULL,
    ...
);

CREATE TABLE product_skus (
    product_sku_id INT GENERATED BY DEFAULT AS IDENTITY,
    product_id INT REFERENCES products(product_id),
    sku TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

CREATE UNIQUE INDEX unique_product_skus ON product_skus (product_id, sku) WHERE deleted_at IS NULL;

CREATE TABLE product_variants (
    variant_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(product_id),
    sku TEXT NOT NULL,
    name TEXT NOT NULL,
    ...
);

3. Декомпозируем значение текстового идентификатора

CREATE TABLE orders (
    order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    region_id INT NOT NULL REFERENCES regions(region_id),
    order_date DATE NOT NULL,
    ...
);

CREATE TABLE regions (
    region_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    code TEXT NOT NULL,
    ...
);

INSERT INTO regions (name, code) VALUES
('Europe, the Middle East, and Africa', 'EMEA'),
('Asia Pacific', 'APAC');

To generate a user-friendly order number, you can create a function:

CREATE OR REPLACE FUNCTION get_order_number(p_order_id INT)
RETURNS TEXT AS $$
DECLARE
    v_region_code TEXT;
    v_formatted_order_id TEXT;
    v_order_number TEXT;
BEGIN
    SELECT r.code INTO v_region_code
    FROM orders o
    JOIN regions r ON o.region_id = r.region_id
    WHERE o.order_id = p_order_id;

    IF NOT FOUND THEN
        RETURN NULL;
    END IF;

    v_formatted_order_id := TO_CHAR(p_order_id, 'FM00000');
    v_order_number := 'ORD-' || v_region_code || '-' || v_formatted_order_id;

    RETURN v_order_number;
END;
$$ LANGUAGE plpgsql;

4. Обратимые текстовые идентификаторы

[42] -> JgaEBgznCpUZo3Kk
[42, 430004] -> lTiYlvsGkh59m1PQ

5. Используйте Создать столбцы

CREATE OR REPLACE FUNCTION get_formatted_order_id(p_region_id INT, p_order_id INT)
RETURNS TEXT AS $$
DECLARE
    v_region_code TEXT;
BEGIN
    v_region_code := CASE p_region_id
        WHEN 1 THEN 'EMEA'
        WHEN 2 THEN 'APAC'
        ELSE 'OTHER'
    END;

    RETURN 'ORD-' || v_region_code || '-' || LPAD(p_order_id::TEXT, 5, '0');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE TABLE orders (
    order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    region_id INT NOT NULL,
    order_date DATE NOT NULL,
    formatted_order_id TEXT GENERATED ALWAYS AS (
        get_formatted_order_id(region_id, order_id)
    ) STORED
);

Заключение