Ruby on Rails Database – Best Practices for Data Model Design

databaseMySQLruby-on-rails

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! 🙂

Best Answer

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.

Related Solutions

Rails Inheritance – To Use STI or Not?

While permanently null fields and large unwieldy tables are conceptually ugly, they generally don't actually cause any real inefficiency. The extra space used by null fields is not an important consideration for most systems, and the query speed will not be slower if you set up your indexes well.

And the benefits of STI are often worthwhile (database efficiency by minimizing joins, and shared code simplicity and DRYness). So if you are going to use a traditional SQL system, just learn to ignore the conceptual ugliness and use STI. Your use case is what STI was designed for.

That said, if you are not too deep in your development cycle for this project to be attached to your database system (and it sounds like you're not), and you are willing to invest some time to research/learning, you really might want to consider an alternative to SQL (there are many good ones out there). Currently the most popular alternative (and my personal favorite) is MongoDB.

MongoDB is document-based rather than schema-based. This means it's not as rigid about what can be stored in it. Because MongoDB has no enforced structured schema, in your project you could just have a single collection of ingredients ("collections" in MongoDB are akin to "tables" in SQL databases). Each ingredient in this collection could have shared fields as defined in an Ingredient superclass (these fields would not defined in the db itself). And the subclasses Malt, Hops, and Yeast could have their own individualized fields (again, as defined directly in these model subclasses, not in the db itself). This is exactly what you are looking for -- it gives you all the convenience of STI without its conceptual ugliness.

Additional bonus: You might find that using a NoSQL database system like this will help simplify other areas of your app as well. In many (most?) common web business domains, NoSQL is a better choice than SQL, because web domains tend to be particularly document-intensive (so documents are a natural choice for storage mechanism).

Summary: If you are attached to using a SQL database because of administration or some part of your app that relies on it, or if don't have the time to invest in learning something new, just go with STI. If you are not attached to using a SQL database and you have the time to learn something new, I'd highly recommend switching to MongoDB or another document-based NoSQL database.

Rails – How to use modal form to add object in one model, then reflect that change on main page

I haven't done this with creating new items, but I have done something similar for edits. I'm not sure if this is the 'cleanest' way of doing it, but it worked for me.

The following works with Rails 3.1, jQuery and coffeescript. If you're using Rails 3.2, I think you now have to put .erb on the end of the coffeescript file. You can use js2coffee to convert the below easily (if you're not using coffeescript).

I defined the dialog code (in /app/views/drops/edit.js.coffee) as:

$("#edit-drop-form").dialog
  autoOpen: true
  height: 460
  width: 380
  title: 'Edit Drop'
  buttons:
    "Cancel": ->
      $("#edit-drop-form").dialog "close"
    "Save": ->
      $.post "/drops/<%= @drop.id %>.json", $("#edit-drop-form form").serializeArray(), (data, text, xhr) ->
        if (xhr.status == 200)
          # Update the DOM here!
          $("#notice").empty().append("Drop updated successfully!")
          $("#edit-drop-form").dialog "close"
  open: ->
    $("#edit-drop-form").html "<%= escape_javascript( render('form') ) %>"

In the above example, the 'form' partial will be rendered in the popup and a json handler in the 'update' action of my Drops controller will process the request.

To change this for creating new items, add a 'json' handler for the create action and have a similar javascript file to the above (but called new.js.coffee). You'll also need to adjust the path for the 'post' to the create action.

Where my comment says "Update the DOM here", is where you'll want to update your select. Simply create a json handler for the 'index' action of IngredientsController, call '$.get "/ingredients.json"' and replace the contents of your select based on the data that returns.

Best Answer

Related Solutions

Rails Inheritance – To Use STI or Not?

Rails – How to use modal form to add object in one model, then reflect that change on main page

Related Topic