Rails - to use STI or not...that is the question

Question

Six months ago, I asked a question about modeling data for my app, and received some advice pointing me towards STI (see Rails data model - best practices question for the details).

I played around with it, got it working somewhat, and then got distracted and put the project on hiatus.

I just started developing it again from scratch, with the benefit of 6 more months of programming/ROR experience, and I have once again hit this wall when it comes to modeling ingredients for my beer recipes.

To give a quick summary, assume I have three ingredient types (malt, hops, and yeast) - each one shares a few attributes (name, price, vendor) but each also has attributes specific to that ingredient type. They are all related (they are all ingredients) but they do have different "behavior" (for example, grains are mashed and there would be logic to deal with that which wouldn't apply to hops/yeast, etc...)

The ingredients are just different enough that I am contemplating just having three separate tables in the db...but what about controllers? Could I use a single Ingredients controller to manage the separate models?

I've read about alternatives to STI (class table inheritance, and multi table inheritance) but all the solutions seem kludgy. Are there any alternatives that give me the convenience of STI (such as getting all ingredients, regardless of type, with a single table query) without the drawbacks (null fields, unwieldy tables when you keep adding subclasses and fields, etc)

Sorry, I know this question is a bit fuzzy, but I feel like I can't find a clean solution and I'm now trying to figure out which method out there is the lesser evil. I'm sure others have dealt with this, and I'd love to hear the pros/cons of different solutions. Thanks!

Answer 1

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.