如何在Python的SQLite3中存储食谱食材ID列表并实现关联

问题1：直接在recipes表的ingredients字段存储ID列表是否可行？ #

• 技术上可以实现：你可以把列表序列化成JSON字符串存储到该字段，SQLite 3.9+也内置了JSON函数支持简单的JSON内容查询，但该方案属于反范式设计，存在大量弊端，非常不推荐：
  • 无法直接使用外键约束校验食材ID的合法性，容易存入不存在的食材ID
  • 联表查询难度极高，比如你要查询所有用到「土豆」的食谱，需要解析字符串才能匹配，性能极差
  • 统计、修改食材列表的操作复杂度高，不符合关系型数据库的设计规范

问题2：新增固定15个食材ID字段的方案是否可行？ #

• 关联逻辑上可以实现：你可以给ingredient1到ingredient15每个字段都设置外键关联ingredients表的ID字段，从而完成数据关联，但该方案同样属于数据库设计反模式，不推荐使用：
  • 强行限制了单份食谱最多只能有15种食材，后续如果有超出的需求需要修改表结构，扩展性极差
  • 涉及食材的查询、统计逻辑极其冗余，比如查询用到某类食材的食谱，需要写15个OR条件匹配所有字段，维护成本极高
  • 大量字段会处于空置状态，浪费存储空间，也不符合第一范式的要求

推荐标准解决方案：使用多对多关联表 #

食谱和食材属于典型的多对多关系，标准设计是新增一张中间关联表存储两者的对应关系，建表参考代码如下：

import sqlite3

conn = sqlite3.connect('recipes.db')
c = conn.cursor()

# 原有表调整：去掉recipes表的ingredients字段
c.execute('''CREATE TABLE recipes(
    ID INT PRIMARY KEY, 
    name TEXT NOT NULL
)''')
c.execute('''CREATE TABLE ingredients(
    ID INT PRIMARY KEY, 
    nazwa TEXT NOT NULL, 
    kcal REAL NOT NULL
)''')
# 新增多对多关联表
c.execute('''CREATE TABLE recipe_ingredients(
    recipe_id INT NOT NULL,
    ingredient_id INT NOT NULL,
    quantity REAL, -- 可选字段，存储该食材在食谱中的用量
    PRIMARY KEY (recipe_id, ingredient_id),
    FOREIGN KEY (recipe_id) REFERENCES recipes(ID),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(ID)
)''')

该方案的优势：

• 没有食材数量限制，支持单份食谱对应任意数量的食材
• 可以直接通过联表查询实现所有业务需求，比如查询某食谱的所有食材、查询某食材对应的所有食谱、统计食材的使用频次等，性能优异
• 外键约束可以保证数据的合法性，不会出现无效的关联ID

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