第三章 数据库设计与ORM模型

说实话，做库存管理系统这么多年，我见过太多项目死在数据库设计这一步。不是表建得不够多，就是字段冗余得让人崩溃。今天咱们就来聊聊，怎么把库存、订单、交易流水这三张核心表设计得既结实又灵活。

3.1 MySQL数据库设计原则

先说说我个人的习惯。设计数据库时，我通常会遵循几个基本原则，这些原则是我在踩过无数坑之后总结出来的。

核心原则：宁可多花一小时设计，不要花一周去重构。

  • 字段类型要精准：能用INT就别用VARCHAR，能用DECIMAL就别用FLOAT。我在项目中遇到过因为用了FLOAT存金额，最后对账差了0.01元的惨案。
  • 索引不是越多越好：每个索引都会拖慢写入速度。我一般只在WHERE、JOIN、ORDER BY涉及的字段上加索引。
  • 范式化与反范式化平衡：第三范式是基础，但有时候为了查询性能，适当冗余字段是可以接受的。比如在订单表里冗余一个商品名称，省得每次都要JOIN商品表。
  • 字段命名统一：我习惯用下划线命名法，比如created_at、updated_at。团队里统一风格，后期维护会省很多事。

小技巧：设计表时，每个表都加上id、created_at、updated_at这三个字段。这是我从第一个项目就养成的习惯，后来发现几乎所有ORM框架都默认支持这种设计。

3.2 三张核心表的ER图设计

嗯，这里我要重点讲一下。库存表、订单表、交易流水表，这三张表构成了整个系统的数据骨架。它们之间的关系，说白了就是：库存是基础，订单是行为，流水是记录。

库存表 (inventory) id (PK) product_id (FK) quantity warehouse_id created_at updated_at 订单表 (orders) id (PK) order_no (UNIQUE) user_id total_amount status created_at updated_at 交易流水表 (transactions) id (PK) order_id (FK) type amount status created_at updated_at 1:N 1:N 关系说明： • 一个商品在库存中有一条记录，但可以对应多个订单 • 一个订单可以有多条交易流水（比如支付、退款、冻结） • 库存变动必须通过交易流水记录，保证数据可追溯 • 订单状态变更必须与交易流水状态保持一致

3.3 库存表（inventory）设计详解

库存表是整个系统的基石。你想想看，如果库存数据不准，后面的订单、交易全都会乱套。我曾经在一个电商项目中，就因为库存表设计得太简单，导致超卖了几百单，那叫一个惨。

字段名类型说明索引
idBIGINT主键，自增PRIMARY
product_idBIGINT商品ID，外键INDEX
quantityINT当前库存数量-
warehouse_idINT仓库IDINDEX
versionINT乐观锁版本号-
created_atDATETIME创建时间-
updated_atDATETIME更新时间-

注意：库存表一定要加version字段做乐观锁。我之前没加这个字段，结果高并发下库存扣减出现了脏读，数据直接对不上。加了版本号之后，每次更新都检查版本号，问题就解决了。

3.4 订单表（orders）设计详解

订单表记录的是用户的每一次购买行为。这里有个设计要点：订单号一定要唯一。我习惯用时间戳+随机数生成订单号，既保证唯一性，又方便按时间排序。

字段名类型说明索引
idBIGINT主键，自增PRIMARY
order_noVARCHAR(32)订单号，唯一UNIQUE
user_idBIGINT用户IDINDEX
total_amountDECIMAL(10,2)订单总金额-
statusTINYINT状态：0待支付，1已支付，2已取消INDEX
created_atDATETIME创建时间INDEX
updated_atDATETIME更新时间-

经验之谈：订单状态用TINYINT而不是VARCHAR，查询效率会高很多。我一般会在代码里定义一个枚举类，把状态值和对应的中文描述映射好，这样既清晰又高效。

3.5 交易流水表（transactions）设计详解

交易流水表说白了就是记账本。每一笔库存变动、每一笔支付，都要在这里留下痕迹。为什么？因为出了问题好追溯啊！

字段名类型说明索引
idBIGINT主键，自增PRIMARY
order_idBIGINT关联订单IDINDEX
typeTINYINT类型：1支付，2退款，3冻结INDEX
amountDECIMAL(10,2)交易金额-
statusTINYINT状态：0处理中，1成功，2失败INDEX
remarkVARCHAR(255)备注信息-
created_atDATETIME创建时间INDEX
updated_atDATETIME更新时间-

3.6 SQLAlchemy ORM模型定义

好了，表设计完了，接下来咱们用SQLAlchemy把这些表映射成Python类。我个人非常喜欢SQLAlchemy，它让数据库操作变得像操作普通对象一样自然。

from sqlalchemy import Column, BigInteger, Integer, String, Decimal, DateTime, SmallInteger, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime

Base = declarative_base()

class Inventory(Base):
    __tablename__ = 'inventory'
    
    id = Column(BigInteger, primary_key=True, autoincrement=True)
    product_id = Column(BigInteger, nullable=False, index=True)
    quantity = Column(Integer, default=0, nullable=False)
    warehouse_id = Column(Integer, nullable=False, index=True)
    version = Column(Integer, default=0)  # 乐观锁
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(BigInteger, primary_key=True, autoincrement=True)
    order_no = Column(String(32), unique=True, nullable=False)
    user_id = Column(BigInteger, nullable=False, index=True)
    total_amount = Column(Decimal(10, 2), nullable=False)
    status = Column(SmallInteger, default=0)  # 0待支付 1已支付 2已取消
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关联交易流水
    transactions = relationship("Transaction", back_populates="order")

class Transaction(Base):
    __tablename__ = 'transactions'
    
    id = Column(BigInteger, primary_key=True, autoincrement=True)
    order_id = Column(BigInteger, ForeignKey('orders.id'), nullable=False, index=True)
    type = Column(SmallInteger, nullable=False)  # 1支付 2退款 3冻结
    amount = Column(Decimal(10, 2), nullable=False)
    status = Column(SmallInteger, default=0)  # 0处理中 1成功 2失败
    remark = Column(String(255))
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关联订单
    order = relationship("Order", back_populates="transactions")

关键点说明：

  • back_populates让两个模型可以双向访问，比如order.transactions就能拿到这个订单的所有流水
  • onupdate=datetime.utcnow会在每次更新时自动更新时间戳，省得手动维护
  • 库存表的version字段是实现乐观锁的关键，更新时记得检查版本号

3.7 实际使用示例

来看看怎么用这些模型。假设我们要创建一个订单并扣减库存：

from sqlalchemy.orm import Session

def create_order_and_deduct_inventory(db: Session, user_id: int, product_id: int, quantity: int):
    # 1. 查询库存（带乐观锁）
    inventory = db.query(Inventory).filter(
        Inventory.product_id == product_id
    ).with_for_update().first()
    
    if not inventory or inventory.quantity < quantity:
        raise Exception("库存不足")
    
    # 2. 创建订单
    order = Order(
        order_no=f"ORD{datetime.utcnow().strftime('%Y%m%d%H%M%S')}{random.randint(1000,9999)}",
        user_id=user_id,
        total_amount=Decimal('99.99'),
        status=0
    )
    db.add(order)
    db.flush()  # 获取order.id
    
    # 3. 创建交易流水
    transaction = Transaction(
        order_id=order.id,
        type=1,  # 支付
        amount=Decimal('99.99'),
        status=0  # 处理中
    )
    db.add(transaction)
    
    # 4. 扣减库存（检查版本号）
    rows = db.query(Inventory).filter(
        Inventory.id == inventory.id,
        Inventory.version == inventory.version
    ).update({
        'quantity': Inventory.quantity - quantity,
        'version': Inventory.version + 1
    })
    
    if rows == 0:
        db.rollback()
        raise Exception("库存更新失败，请重试")
    
    db.commit()
    return order

避坑指南：我曾经在扣减库存时忘了加版本号检查，结果并发场景下库存变成了负数。后来加上乐观锁，每次更新都检查版本号，这个问题再也没出现过。记住：库存操作一定要加锁，要么用数据库的行锁，要么用乐观锁。

好了，数据库设计和ORM模型这块就讲到这里。这三张表的设计思路，说白了就是：库存管数量，订单管行为，流水管记录。只要把这三者的关系理清楚，后面的业务逻辑实现就会顺畅很多。

专注资料整理

← 上一章 📖 返回目录 下一章 →