I'm writing a Rails cookbook app as a learning exercise. I've created a table for Recipes which holds info for the recipe (name, author, etc...). I also have a table for Ingredients which will be a master ingredients list, and then a table in-between which holds info specific to that recipe/ingredient combo (for example, the ingredient table would have a listing for Onion, the in-between table would have the amount, technique (slice, chop, etc...) and whatever else for a particular recipe).
Anyway - this is all working fine, but as I add ingredients to the table, I realize there are quite a few categories of ingredients (Spices, Vegetables, Meats, Fruits, etc...) and they have different bits of info I want to record about them. For example, meats and veggies might have the technique field (chop, dice, mince, etc) but spices might have a "spice_type" field to record if it is dry or fresh. Meats might require a "doneness" field (well-done, rare, etc..) while fruits do not...
So, the crux of my question - is it better to have one large ingredients table with fields for every category, only using the ones I need for a particular row, or is it better to create five tables, one for each type of ingredient?
I started coding it as separate tables, as it seemed proper since I am holding different fields for each type, but then I realized how much work it will be to code 5 x models, 5 x controllers, 5 x views, etc...
SO, better to stuff it all in one table (after all, they are all ingredients!), have each record just leave the non-applicable fields nil, and make my views/etc.. dynamic to adapt to whatever type of ingredient is being worked with (essentially hiding or displaying the appropriate fields)?
I hope this makes sense, whenever I get on this site I turn into a rambling fool ;)
Thanks for the help
I was making some beer yesterday and thinking about my beer recipes along the lines of my cookbook app, and realized the beer recipes are a much better example of what I am trying to figure out, so below is the same question posed in a different paradigm:
In my beer recipes, I have three types of Ingredients. There is malt, hops, and yeast.
They are common in the sense that they are all ingredients, and will all have a few common attributes such as Name, Brand, and Price. However, there are specific attributes to each that the others don't share - for example, Malt has an SRM attribute (essentially color), while Hops has an AA (alpha acids) attribute, and yeast has a Flocculation attribute.
So do I go STI and lay out a table like this? id, type, name, brand, price, srm, aa, flocculation
Additionally, the "in-between" table will contain different info on each. All ingredients will have an Amount attribute, but hops will have a BoilTime and malts will have a MashTemp, etc...
So do I use one table with STI and one "in-between" table to join? Do I make the "in-between" table STI as well?
I have a lot of reading to do! :)