Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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!

share|improve this question
2  
Impossible to answer unless you post code. If you don't know, just pick one and see how it goes. You are writing Ruby code, so why worry so much about what's going on in the database? You can always change the mapping later. It doesn't sound like you are going to have a large database in production any time soon. –  kevin cline Feb 21 '12 at 23:10
    
I would say they are all ingredients and perform the business logic higher up but that's just me...and like @kevincline says you can change it later. Since they sound like the same table essentially it wouldn't even be too hard. –  Rig Feb 22 '12 at 12:51
    
I guess I just hate the idea of having nullable fields in my table, but maybe I just need to get over it... –  Jim Feb 22 '12 at 14:22

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
I love this answer. Practical advise for my situation with the added bonus of some advice to make things easier in the future. I haven't checked out NoSQL DBs at all, but I'll investigate MongoDB - sounds good, and since this entire project is really a learning experience, I am definitely open to trying new things. –  Jim Feb 27 '12 at 21:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.