Rails data model - best practices question

Question

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

Update:

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! :)

Answer 1

Rails has, built-in, the concept of Single-Table Inheritance. Read up on it and use it, it's perfect for this situation.

In essence, what it does is allow you to have a type field in your table which literally defines the type (class name) of the object defined in the row. Fields that don't have matching attributes on objects of a particular type are nulled and ignored. In other words: it does everything that Emmad suggests for you.

It is such a fundamental part of the framework that if you have a field named Type, which isn't implementing STI, then you need to add some code to make sure the framework knows that.

One word of warning though: there is a danger with this technique of ending up with a table with dozens of fields which are only used by a few records. If this starts to happen, consider taking some of those fields out into separate tables. That is, you can still have a single main table, but if Spices have 20 fields not used by other types, you can store that in another table with a 1-to-1 relationship to the Spice object.

Answer 2

Does each type have different fields or does the one field hold a different meaning depending on the ingredient category? You can have a text field to hold the extra information (sorry, I don't have a name for it; maybe Additional Preparation?) to the ingredient. Meat (Chopped), Pepper (Freshly Ground). You could query by this field if necessary.

The other option is to link to a different table for each ingredient category. I don't see a need for it unless you want to have some sort of lookup to make sure users select "Freshly Ground" instead of "Ground" or "ground". The other reason would be if each category had several fields that were different instead of just one field with different meanings. Options on a car would be very different than options on a motorcycle.

Answer 3

It makes sense that you combine the different ingredients in 1 table - You should:

1. have a unique ID for each row independent from the ingredient name, for example, the literal 'fruit' is not a good key.

2. define non-used fields as null/blank

3. don't run calculations on non-used fields (this is correct in your case indeed)

4. have a column that indicates the ingredient type

Hope this helps