Schema 设计模式
-- 电商 Schema 示例（规范化）
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(64) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
category_id INT REFERENCES categories(id)
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','completed','cancelled')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10, 2) NOT NULL, -- 下单时的价格快照
PRIMARY KEY (order_id, product_id)
);
-- 软删除模式
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;
-- 查询：WHERE deleted_at IS NULL