3、数据库设计与建模：从ER图到ORM模型

好，咱们进入第三个章节。说实话，数据库设计这块，是很多新手容易翻车的地方。我见过太多项目，前期图省事，表结构随便一画就开始写代码，结果做到一半发现字段不够用，关联关系乱成一锅粥……最后不得不重构，那叫一个痛苦。

所以这一章，咱们稳扎稳打。从ER图开始，把客户、联系人、跟进记录、用户这四张表的关系理清楚，然后写MySQL建表语句，再用Flask的SQLAlchemy定义ORM模型，最后做数据库迁移。每一步我都会结合自己的实战经验来讲。

3.1 ER图设计：理清实体关系

先说说ER图。说白了，它就是一张“地图”，告诉你数据之间怎么关联。咱们这个客户管理平台，核心实体就四个：客户、联系人、跟进记录、用户。

我习惯先画关系，再定字段。这样思路不会乱。

3.1.1 实体关系梳理

  • 用户与客户：一个用户可以有多个客户，一个客户只属于一个用户。这是典型的一对多关系。
  • 客户与联系人：一个客户可以有多个联系人，一个联系人只属于一个客户。也是一对多。
  • 客户与跟进记录：一个客户可以有多次跟进记录，一条跟进记录只属于一个客户。还是一对多。
  • 用户与跟进记录：一条跟进记录由某个用户创建，一个用户可以创建多条跟进记录。同样是一对多。

你看，整个模型其实很清晰。核心就是客户表，它像是一个枢纽，连接了用户、联系人和跟进记录。

核心设计原则：所有“多”的一方，都要通过外键去关联“一”的一方。外键字段通常命名为 xxx_id。

3.1.2 各表字段设计

嗯，这里要注意。字段设计不能太随意，也不能太冗余。我一般会遵循几个原则：

  • 每个表都要有一个自增主键 id
  • 都要有 created_at 和 updated_at 时间戳
  • 字段类型要选对，别什么都用 varchar(255)

下面是我设计的字段清单，你可以参考一下：

表名 字段 类型 说明
用户表 (users) id INT (自增) 主键
usernameVARCHAR(50)用户名，唯一
password_hashVARCHAR(128)密码哈希值
emailVARCHAR(100)邮箱，唯一
created_atDATETIME创建时间
updated_atDATETIME更新时间
客户表 (customers) id INT (自增) 主键
nameVARCHAR(100)客户名称
phoneVARCHAR(20)联系电话
emailVARCHAR(100)邮箱
user_idINT外键，关联用户表
created_atDATETIME创建时间
updated_atDATETIME更新时间
联系人表 (contacts) id INT (自增) 主键
nameVARCHAR(50)联系人姓名
phoneVARCHAR(20)电话
positionVARCHAR(50)职位
customer_idINT外键，关联客户表
created_atDATETIME创建时间
updated_atDATETIME更新时间
跟进记录表 (followups) id INT (自增) 主键
contentTEXT跟进内容
followup_dateDATE跟进日期
customer_idINT外键，关联客户表
user_idINT外键，关联用户表
created_atDATETIME创建时间
updated_atDATETIME更新时间

小技巧：我个人习惯把 created_at 和 updated_at 做成一个 mixin，这样每个模型都能复用，不用重复写。后面ORM部分我会演示。

3.2 MySQL建表语句编写

ER图画好了，字段也定了，接下来就是写SQL。你可能会觉得这步很简单，但我要提醒你——索引别忘了加。我在项目中遇到过好几次，表数据量一上来，查询慢得像蜗牛，一查才发现外键字段没加索引。

下面是完整的建表语句：

-- 用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(128) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 客户表
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    user_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 联系人表
CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    position VARCHAR(50),
    customer_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_customer_id (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 跟进记录表
CREATE TABLE followups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    followup_date DATE NOT NULL,
    customer_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_customer_id (customer_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意：ON DELETE CASCADE 的意思是，如果父表记录被删了，子表里关联的记录也会自动删除。比如删了一个客户，他所有的联系人和跟进记录都会被清掉。这个要看业务需求，有些场景可能更适合用 SET NULL 或 RESTRICT。

3.3 Flask SQLAlchemy ORM模型定义

好，SQL写完了，但咱们实际开发中不会直接拼SQL。用ORM框架，代码更优雅，也更安全。Flask里最常用的就是 SQLAlchemy。

先定义一个基类 mixin，把公共字段抽出来：

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class TimestampMixin:
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

然后定义各个模型。你看，这样写是不是比SQL清晰多了？

class User(db.Model, TimestampMixin):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)

    # 关系
    customers = db.relationship('Customer', backref='owner', lazy='dynamic')

    def __repr__(self):
        return f'<User {self.username}>'


class Customer(db.Model, TimestampMixin):
    __tablename__ = 'customers'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    phone = db.Column(db.String(20))
    email = db.Column(db.String(100))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    # 关系
    contacts = db.relationship('Contact', backref='customer', lazy='dynamic')
    followups = db.relationship('Followup', backref='customer', lazy='dynamic')

    def __repr__(self):
        return f'<Customer {self.name}>'


class Contact(db.Model, TimestampMixin):
    __tablename__ = 'contacts'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    phone = db.Column(db.String(20))
    position = db.Column(db.String(50))
    customer_id = db.Column(db.Integer, db.ForeignKey('customers.id'), nullable=False)

    def __repr__(self):
        return f'<Contact {self.name}>'


class Followup(db.Model, TimestampMixin):
    __tablename__ = 'followups'

    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.Text, nullable=False)
    followup_date = db.Column(db.Date, nullable=False)
    customer_id = db.Column(db.Integer, db.ForeignKey('customers.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    # 关系
    user = db.relationship('User', backref='followups')

    def __repr__(self):
        return f'<Followup {self.followup_date}>'

关于 lazy 参数：lazy='dynamic' 表示当你访问 customer.contacts 时，返回的是一个查询对象，而不是直接加载所有数据。这样你可以继续加过滤条件，比如 customer.contacts.filter_by(name='张三')。如果数据量不大，用 lazy=True 也行，看场景。

3.4 数据库迁移与初始化

模型定义好了，怎么同步到数据库？手动执行SQL？别傻了，用 Flask-Migrate 吧。它基于 Alembic，可以自动生成迁移脚本，还能回滚。

先安装依赖：

pip install flask-migrate

然后在应用里初始化：

from flask import Flask
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:password@localhost/crm_db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db.init_app(app)
migrate = Migrate(app, db)

接下来就是命令行操作了。我习惯按这个顺序来：

  1. 初始化迁移仓库：flask db init（只需执行一次）
  2. 生成迁移脚本：flask db migrate -m "创建用户、客户、联系人、跟进记录表"
  3. 应用迁移：flask db upgrade

如果发现模型写错了，想回退：

flask db downgrade

我曾经踩过的坑：有一次我改了模型字段，直接 flask db migrate 后没检查生成的脚本就 upgrade 了，结果发现它把整个表删了重建……数据全没了。所以，每次 migrate 后，一定要去 migrations/versions/ 目录下看一眼生成的脚本，确认没问题再 upgrade。

最后，如果你想快速初始化测试数据，可以写一个 seed 脚本：

def seed_data():
    # 创建用户
    user = User(username='admin', password_hash='hashed_pwd', email='admin@example.com')
    db.session.add(user)
    db.session.commit()

    # 创建客户
    customer = Customer(name='张三公司', phone='13800138000', user_id=user.id)
    db.session.add(customer)
    db.session.commit()

    # 创建联系人
    contact = Contact(name='李四', phone='13900139000', position='经理', customer_id=customer.id)
    db.session.add(contact)
    db.session.commit()

    # 创建跟进记录
    followup = Followup(content='第一次电话沟通，客户有意向', followup_date=datetime.now().date(),
                        customer_id=customer.id, user_id=user.id)
    db.session.add(followup)
    db.session.commit()

嗯，到这里，数据库设计与建模这一章就差不多了。你想想看，从ER图到SQL，再到ORM模型，最后到迁移初始化，整个链路是不是很清晰？下一章咱们就开始写业务逻辑了，到时候这些模型就会真正派上用场。

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