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.
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.
--max