如何在MySQL中设计发票与报价单表并获取指定格式API数据？

一、数据库表结构设计 #

针对发票和报价单的一对多关系（单张单据对应多个商品），采用主表+明细表的经典关联设计，同时搭配客户表支撑客户名称查询：

1. 客户表（clients） #

存储客户基础信息，用于关联获取客户名称：

2. 发票模块表结构 #

发票主表（invoices）

存储发票头部核心信息：

发票商品明细表（invoice_items）

存储单张发票对应的所有商品明细，必须保存开票时的商品快照信息（避免商品信息变更导致发票数据失真）：

3. 报价单模块表结构 #

与发票模块结构对称，仅调整业务相关字段：

报价单主表（quotes）

报价商品明细表（quote_items）

二、数据查询与API返回实现 #

1. SQL查询示例（以发票为例） #

通过关联查询获取发票头部+明细数据：

SELECT
    i.invoice_no AS `Invoice No`,
    c.client_name AS `Client Name`,
    i.invoice_date AS `Date`,
    i.order_no AS `Order No`,
    i.deliver_note_no AS `Deliver Note No`,
    ii.item_no AS `Item No`,
    ii.description AS `Description`,
    ii.quantity AS `Quantity`,
    ii.unit_price AS `Unit Price`
FROM invoices i
JOIN clients c ON i.client_id = c.client_id
JOIN invoice_items ii ON i.invoice_no = ii.invoice_no
WHERE i.invoice_no = 'INV-2024-0001';

2. 后端数据组装（伪代码示例） #

查询结果会返回多行数据（每个商品一行），需要在后端将其组装为你需要的JSON结构：

# 假设查询结果是一个字典列表
query_results = [
    {
        "Invoice No": "INV-2024-0001",
        "Client Name": "ABC Corp",
        "Date": "2024-05-20",
        "Order No": "ORD-2024-0005",
        "Deliver Note No": "DN-2024-0010",
        "Item No": 1,
        "Description": "Laptop Pro",
        "Quantity": 2,
        "Unit Price": 999.99
    },
    {
        "Invoice No": "INV-2024-0001",
        "Client Name": "ABC Corp",
        "Date": "2024-05-20",
        "Order No": "ORD-2024-0005",
        "Deliver Note No": "DN-2024-0010",
        "Item No": 2,
        "Description": "Wireless Mouse",
        "Quantity": 2,
        "Unit Price": 29.99
    }
]

# 组装目标JSON结构
if not query_results:
    return {}

# 提取头部信息（取第一行的公共字段）
invoice_data = {
    "Invoice No": query_results[0]["Invoice No"],
    "Client Name": query_results[0]["Client Name"],
    "Date": query_results[0]["Date"],
    "Order No": query_results[0]["Order No"],
    "Deliver Note No": query_results[0]["Deliver Note No"],
    "Details": []
}

# 提取所有明细项
for item in query_results:
    invoice_data["Details"].append({
        "Item No": item["Item No"],
        "Description": item["Description"],
        "Quantity": item["Quantity"],
        "Unit Price": item["Unit Price"]
    })

# 最终返回的JSON就是invoice_data

3. 关键注意事项 #

• 事务一致性：创建发票/报价单时，必须同时插入主表和明细表，避免数据部分插入失败。
• 快照存储：明细表必须保存商品的即时信息（如单价、描述），不能仅关联商品表ID，否则商品信息变更后会导致历史单据数据错误。
• 性能优化：如果单据量极大，可对invoice_no、quote_no等字段建立索引，提升关联查询效率。

内容的提问来源于stack exchange，提问作者Chirag Maurya