Single table inheritance, EAV or NoSQL?

Question

I'm looking for a good sane approach for my application's data model. Most threads focus on e-commerce products and my scenario is somewhat different.

The goal is storing received items from customers and reporting back on them. Although initial reports will be simple lists (these are your items and their key/value pairs) I want to prepare for future queries on the collections as a whole, so I'm uncertain EAV is a good approach.

Each item is of a (recurring) type, and each item will have a flexible amount of key/value pairs. We have about 15 item types and they're not very likely to grow fast.

The amount of key/value pairs will be low, between 4 and 20 and is (per requirement) not based on the item type, but on the customer the items belong to.

To clarify, a customer might want me to store the condition of the item where another might want me to store the color or a replacement-id. It really depends. So it is very likely I will have NULL values in my set anyway as each item type will get all the key/values of that customer anyway. That makes me think Single Table Inheritance (with NULL values) is acceptable, but it would have to be field_1 field_2 etc - with some sort of mapping because the customer will decide on name of their desired key(s). Which doesn't seem right to me?

A collection of customer items (and their key/values) will usually be >20 and <500, so the data sets per customer are certainly not enormous.

Perhaps it would be good approach to go (semi)EAV in MySQL for the application itself (creating the customer and their desired fields) but move the actual collected item records as a 'document' into a NoSQL database (Redis et all) for further processing?

Or is that over complicating things that can/should be solved in a RDBMS?

I also came across this http://backchannel.org/blog/friendfeed-schemaless-mysql which seems like a solution but not sure since my numbers are so low.

Answer 1

Having done projects in all three approaches you list, I don't know that I can give you a hard and fast direction, but I can offer up some advice on moving forward and picking the right approach.

Tips:

1. Try not to mix technologies if you can. From personal experience I've found this to be very difficult. Also you'll read quite often with big sites that need to scale that they always mention keeping the architecture simple. They'll start with a mixture of something like MySQL and MongoDB and then ditch MongoDB just to keep things simple.

2. Your question seems to be suffering from a bit of analysis paralysis. "I could do XYZ, but that means that ABC is not possible." I would suggest fixing some points/facts about your system and then start building prototypes. You seem to have good ideas, but you won't know how good they really are until you start building.

3. Realize all decisions have consequences. The first 80% of your solution will probably be fairly quick and easy. The last 20% will consist of compromises and might be pretty ugly. Just prepare for that and be ok with it.

4. From my experience the document database (NoSQL) approach is still a bit of a risk. From having done a solid year of document development, by far the hardest thing was modeling the data. If you want to go down that road make sure you study data modeling. It will save you a lot of pain.