我的方法:
一个食谱有一个名称,并且可以有多个成分。一个成分包括数量、单位和名称(成分表),例如500ml水。
表格recipe - id,主键 - 名称 (一个食谱可以有多个成分,一个成分可以在多个食谱中)
表格recipe_ingredient
- 外键(recipe.id) - 外键(amounts.id) - 外键(units.id) - 外键(ingredients.id)
表格amounts
- id,主键 - 数量(例如500)
表格units
- id,主键 - 单位(例如ml)
表格ingredients
- id,主键 - 成分(例如水)
代码:
recipe_ingredient = db.Table('recipe_ingredient',
db.Column('idrecipe', db.Integer, db.ForeignKey('recipe.id')),
db.Column('idingredient', db.Integer, db.ForeignKey('ingredients.id')),
db.Column('idunit', db.Integer, db.ForeignKey('units.id')),
db.Column('idamount', db.Integer, db.ForeignKey('amounts.id'))
)
class Recipe(db.Model):
id= db.Column(db.Integer, primary_key=True, autoincrement=True)
name= db.Column(db.VARCHAR(70), index=True)
ingredient= db.relationship("Ingredients", secondary=recipe_ingredient, backref='recipe')
amounts = db.relationship("Amounts", secondary=recipe_ingredient, backref='recipe')
units= db.relationship("Units", secondary=recipe_ingredient , backref='recipe')
class Ingredients(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
ingredient = db.Column(db.VARCHAR(200))
class Units(db.Model):
id= db.Column(db.Integer, primary_key=True, autoincrement=True)
unit= db.Column(db.VARCHAR(45), nullable=False)
class Amounts(db.Model):
id= db.Column(db.Integer, primary_key=True, autoincrement=True)
amount= db.Column(db.VARCHAR(45), nullable=False)
更新:
class RecipeIngredient(db.Model):
__tablename__ = 'recipe_ingredient'
recipe_id = db.Column(db.Integer, db.ForeignKey('recipe.id'), primary_key=True)
ingredient_id = db.Column(db.Integer, db.ForeignKey('ingredient.id'), primary_key=True)
amount = db.Column(db.Integer, db.ForeignKey('amount.id'))
unit = db.Column(db.Integer, db.ForeignKey('unit.id'))
recipes = relationship("Recipe", back_populates="ingredients")
ingredients = relationship("Ingredient", back_populates="recipes")
class Recipe(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(70), index=True)
ingredients = relationship("RecipeIngredient", back_populates="recipes")
class Ingredient(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(200))
recipes = relationship("RecipeIngredient", back_populates="ingredients")
class Unit(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(45), nullable=False)
class Amount(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
number = db.Column(db.VARCHAR(45), nullable=False)
我添加了以下对象。
pizza = Recipe(name='Pizza')
flour = Ingredient(name='Flour')
water = Ingredient(name='Water')
g = Unit(name='g')
ml = Unit(name='ml')
a500 = Amount(number='500')
a100 = Amount(number='100')
r1= RecipeIngredient(recipe_id=pizza.id, ingredient_id=flour.id, amount=a500.id, unit=g.id)
r2= RecipeIngredient(recipe_id=pizza.id, ingredient_id=water.id, amount=a100.id, unit=ml.id)
pizza.ingredients的结果:
[<RecipeIngredient 1, 1>, <RecipeIngredient 1, 3>]
如果我想获取一个配料的名称,需要在这个模型中添加什么内容呢?使用下面的代码:pizza.ingredients[0].name
amount
作为 int 列是有意义的,但unit
作为 varchar 不是很合适,特别是没有任何检查。例如,对于盎司:oz
、oZ
、Oz
和OZ
都是可能的 - 显然有三个是错误的,但它们是可能的 - 因此架构应该防止这种情况发生。 - kevlarr