I’m very new at rails and wondering if ActiveRecord supports (many-to-
many)-to many relationships.
For example: I have recipes, ingredients, and cookingmethods. Recipes
have many ingredients, and ingredients are in many recipes. For each
ingredient in a recipe, there is a different cooking method. For
example, potatoes in a salad are diced, but potatoes in potato chips
are sliced. Example database tables:
recipes: id, name
recipes_ingredients: id, recipe_id, ingredient_id
ingredients: ingredient_id, name
cookingmethods: id, name
recipes_ingredients_cookingmethods: recipe_ingredient_id,
cookingmethod_id
I looked at through associations, but those seemed to deal only with
many-to-many relationships.
To do this, you have your ingredients, recipes and cooking_methods
tables,
as well as a join table… let’s call this recipe_ingredients. This
would
have foreign key columns, one for each of the other tables.
There. It’s quite easy really. You just have to break down three-way
relationships into two way ones, and that’ll solve any (database)
relationship problems.
My inner database normalization conscience has some misgivings about
this schema. Suppose the number of cooking methods is large compared
to the number of recipe/ingredients. So potatoes in potato chips can
be sliced, diced, grated… Then I’ll end up duplicating the recipe/
ingredients foreign keys a lot in the join table.
Suppose further that people can comment on each recipe/ingredient/
cooking method combination. I would have to add a “comments” column to
recipes_ingredients. Then for each comment, I’ll have to duplicate the
keys for recipes/ingredients/cookingmethod…this is clearly
unnormalized.
So I think my original schema gives me more flexibility in the long
run. Any way I can get rails to work with the original schema (see
first post in thread)?
keys for recipes/ingredients/cookingmethod…this is clearly
unnormalized.
normalisation is not necessarily the best design in all cases… but I
digress…
So I think my original schema gives me more flexibility in the long
run. Any way I can get rails to work with the original schema (see
first post in thread)?
Of course. You need to promote your ingredient/cookingmethod table to
a proper first-class model. If you find yourself talking (or thinking)
about a join table a lot, it’s usually a sign that it wants to be a
model.
You’ll also want to read up on has_many :through.
Let’s call that model Preparation. You then have:
class Ingredient
has_many :preparations
has_many :cooking_methods, :through=>:preparations
has_many :recipes, :through=>:preparations
end
class Recipe
has_and_belongs_to_many :preparations
has_many :ingredients, :through=>:preparations
end
class CookingMethod
has_many :preparations
has_many :ingredients, :through=>:preparations
end
class Preparation
has_and_belongs_to_many :recipes
belongs_to :ingredient
belongs_to :cooking_method
end
5 tables total, one for each of the models, plus a preparations_recipes
table.