37 常见业务场景

在本章中，我们将探讨一些常见的业务场景，使用 PostgreSQL 数据库来解决实际问题。这些案例将通过实际应用，帮助理解 PostgreSQL 的强大功能以及可以如何在业务中有效地使用数据库。

1. 客户管理系统

场景背景

假设我们要建立一个简单的客户管理系统，用于跟踪客户的信息和他们的订单。我们可能需要存储客户名称、联系方式以及历史订单信息。

数据库设计

我们可以设计如下的表：

• customers：存储客户信息
• orders：存储客户的订单信息

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(15),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

数据插入

插入一些示例数据以进行测试：

INSERT INTO customers (name, email, phone) VALUES
('Alice Zhang', 'alice@example.com', '123456789'),
('Bob Liu', 'bob@example.com', '987654321');

INSERT INTO orders (customer_id, total_amount) VALUES
(1, 250.00),
(1, 75.50),
(2, 120.00);

查询客户和订单

为了获取客户及其订单的详细信息，我们可以使用 JOIN 语句：

SELECT 
    c.name, 
    c.email, 
    o.order_date, 
    o.total_amount 
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
ORDER BY 
    c.customer_id, o.order_date;

业务分析

上述查询能够为业务提供有价值的信息，比如每个客户的购买历史和联系方式。这对销售和客户服务团队非常重要，能够帮助他们提供更加个性化的服务。

2. 内容管理系统

场景背景

在构建内容管理系统时，我们需要管理文章的发布，包括作者信息、文章内容和评论。

数据库设计

我们可以设计如下的表：

• authors：存储作者信息
• articles：存储文章内容
• comments：存储用户评论

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    bio TEXT
);

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    author_id INT NOT NULL,
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE SET NULL
);

CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    article_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE
);

数据插入

添加一些示例作者和文章数据：

INSERT INTO authors (name, email, bio) VALUES
('Chen Wei', 'chen@example.com', 'Technology enthusiast'),
('Li Mei', 'li@example.com', 'Lifestyle blogger');

INSERT INTO articles (title, content, author_id) VALUES
('The Future of Tech', 'Content about technology...', 1),
('Healthy Living Tips', 'Content about healthy living...', 2);

查询文章及评论

为了获取每篇文章及其评论的详细信息：

SELECT 
    a.title, 
    a.content, 
    au.name AS author_name, 
    c.content AS comment_content 
FROM 
    articles a
JOIN 
    authors au ON a.author_id = au.author_id
LEFT JOIN 
    comments c ON a.article_id = c.article_id
ORDER BY 
    a.article_id, c.created_at;

业务分析

上述查询能够帮助内容团队了解每篇文章的受欢迎程度（通过评论数）以及作者信息，这对内容优化和推广策略的制定有很大帮助。

3. 电商平台

场景背景

在电商平台中，我们需要处理产品的库存、订单、客户和支付信息。

数据库设计

我们可以设计如下的表：

• products：存储产品信息
• product_inventory：存储产品的库存状态
• payments：记录支付信息

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE product_inventory (
    inventory_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

数据插入

插入一些示例产品和库存数据：

INSERT INTO products (name, price) VALUES
('Laptop', 1500.00),
('Smartphone', 700.00);

INSERT INTO product_inventory (product_id, quantity) VALUES
(1, 100),
(2, 200);

查询产品及库存

为了获取产品和库存的详细信息：

SELECT 
    p.name AS product_name, 
    p.price, 
    pi.quantity 
FROM 
    products p
JOIN 
    product_inventory pi ON p.product_id = pi.product_id;

业务分析

通过这种方式，电商平台能够实时掌握商品的库存情况，确保能够满足客户的需求，从而提升销售和客户满意度。

总结

以上几个业务场景展示了 PostgreSQL 在实际应用中的灵活性和强大功能。无论是在客户管理、内容管理还是电商平台，PostgreSQL 都能够提供高效的数据存储方案，并通过复杂查询帮助业务进行深入的分析。

在下一篇中，我们将进入 项目实战 部分，结合实际的项目开发来更深入地探索 PostgreSQL 的使用。