如何设计SQL模型存储多级数据并通过Flask SQLAlchemy实现JSON格式检索

针对你需要存储分类组、用户、状态这种多级数据，还要能以JSON格式检索的需求，我整理了三种实用方案，结合Flask SQLAlchemy给出具体实现：

方案一：规范化关系型设计（推荐） #

这是最符合关系型数据库设计范式的做法，适合需要频繁查询、更新单个实体（比如修改用户状态、单独查询某类用户）的场景，数据一致性和扩展性都很好。

模型定义 #

from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import joinedload

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///multi_level.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# 状态表：存储可复用的状态类型
class Status(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)  # 取值：open/active/closed

# 用户表：关联状态和分类组
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    status_id = db.Column(db.Integer, db.ForeignKey('status.id'), nullable=False)
    category_group_id = db.Column(db.Integer, db.ForeignKey('category_group.id'), nullable=False)
    
    # 建立ORM关联关系
    status = db.relationship('Status', backref=db.backref('users', lazy=True))
    category_group = db.relationship('CategoryGroup', backref=db.backref('users', lazy=True))

# 分类组表
class CategoryGroup(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(50), unique=True, nullable=False)  # 取值：Employed/Student/Self-Employed

初始化数据 #

你可以在Flask Shell里执行以下代码添加测试数据：

db.create_all()

# 添加状态
status_open = Status(name='open')
status_active = Status(name='active')
status_closed = Status(name='closed')
db.session.add_all([status_open, status_active, status_closed])
db.session.commit()

# 添加分类组
employed_group = CategoryGroup(category='Employed')
student_group = CategoryGroup(category='Student')
db.session.add_all([employed_group, student_group])
db.session.commit()

# 添加用户
jason = User(
    name='Jason Doe', 
    email='jason@doe.com', 
    status=status_open, 
    category_group=employed_group
)
lisa = User(
    name='Lisa Smith', 
    email='lisa@smith.com', 
    status=status_active, 
    category_group=student_group
)
db.session.add_all([jason, lisa])
db.session.commit()

查询并转为JSON #

使用joinedload避免N+1查询问题，然后手动序列化嵌套结构：

@app.route('/group/<int:group_id>')
def get_group_details(group_id):
    # 预加载关联的用户和状态，提升查询效率
    group = CategoryGroup.query.options(
        joinedload(CategoryGroup.users).joinedload(User.status)
    ).get_or_404(group_id)
    
    # 序列化为你需要的JSON格式
    group_json = {
        'id': group.id,
        'category': group.category,
        'users': [
            {
                'id': user.id,
                'name': user.name,
                'email': user.email,
                'status': {
                    'id': user.status.id,
                    'status': user.status.name
                }
            } for user in group.users
        ]
    }
    return jsonify(group_json)

优缺点 #

• ✅ 优点：数据无冗余，一致性强；支持复杂查询（比如筛选所有状态为active的学生用户）；便于单独更新实体。
• ❌ 缺点：嵌套查询的序列化代码稍多；层级过深时关联会变复杂。

方案二：单表+JSON字段（适合少更新/整体读取场景） #

如果你的数据主要是整体读取，很少单独修改用户或状态，这种方案更简单，直接把嵌套数据存在JSON字段里，无需关联多张表。

模型定义 #

class CategoryGroup(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(50), unique=True, nullable=False)
    users = db.Column(db.JSON, nullable=False)  # 直接存储用户列表的JSON数据

添加数据 #

employed_group = CategoryGroup(
    category='Employed',
    users=[
        {
            'id': 1,
            'name': 'Jason Doe',
            'email': 'jason@doe.com',
            'status': {'id': 1, 'status': 'open'}
        },
        {
            'id': 2,
            'name': 'Mike Brown',
            'email': 'mike@brown.com',
            'status': {'id': 2, 'status': 'active'}
        }
    ]
)
db.session.add(employed_group)
db.session.commit()

查询并返回JSON #

直接读取JSON字段即可，无需额外序列化：

@app.route('/single-table-group/<int:group_id>')
def get_single_table_group(group_id):
    group = CategoryGroup.query.get_or_404(group_id)
    return jsonify({
        'id': group.id,
        'category': group.category,
        'users': group.users
    })

优缺点 #

• ✅ 优点：查询和序列化极简；适合一次性获取完整数据的场景。
• ❌ 缺点：数据冗余（相同状态会重复存储）；无法单独查询或修改单个用户；多数数据库无法对JSON内的字段建立索引，查询性能受限。

方案三：混合模式（兼顾规范与灵活） #

如果某些实体（比如状态）是可复用的枚举值，而其他实体（比如用户列表）不需要频繁单独查询，可以采用混合模式：将复用性高的实体单独建表，嵌套数据存在JSON字段里。

模型定义 #

# 状态表依然独立存储
class Status(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True, nullable=False)

# 用户表单独存储，关联状态
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    status_id = db.Column(db.Integer, db.ForeignKey('status.id'), nullable=False)
    status = db.relationship('Status', backref=db.backref('users', lazy=True))

# 分类组表存储用户ID的JSON列表
class CategoryGroup(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(50), unique=True, nullable=False)
    user_ids = db.Column(db.JSON, nullable=False)  # 比如[1,2,3]

查询并转为JSON #

根据user_ids批量查询用户，再序列化：

@app.route('/mixed-group/<int:group_id>')
def get_mixed_group(group_id):
    group = CategoryGroup.query.get_or_404(group_id)
    users = User.query.options(joinedload(User.status)).filter(User.id.in_(group.user_ids)).all()
    
    group_json = {
        'id': group.id,
        'category': group.category,
        'users': [
            {
                'id': user.id,
                'name': user.name,
                'email': user.email,
                'status': {'id': user.status.id, 'status': user.status.name}
            } for user in users
        ]
    }
    return jsonify(group_json)

优缺点 #

• ✅ 优点：减少了状态数据的冗余；兼顾了分类组查询的便捷性。
• ❌ 缺点：无法直接对用户属性进行复杂查询；更新用户列表需要修改JSON字段。

选择建议 #

• 如果你需要频繁操作单个用户或状态，或者要做复杂的数据筛选，优先选方案一。
• 如果数据以整体读取为主，很少单独更新子实体，选方案二更省心。
• 如果部分实体复用性高，部分实体无需单独查询，可以考虑方案三。

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