Database design for items with variable attributes? [closed]

Question

I'm creating an application that will need to store items and categories. The information added to the database will be user submitted and the user can add items and in the future categories for items.

E.g. A user can add a CD to the Music category or a DVD to the Movies category. I don't want to limit the categories that can be added or the items that can be added to those categories. The user can pretty much add any item.

Right now I'm using SQL Server and I have an Item table with ItemId (primary), Name, Description and CategoryId (foreign) linking to a Category table with similar columns.

My problem is that a CD and DVD have different attributes like 'running time', 'age rating', 'number of tracks' so I can't store them in the same table because they would have redundant columns. What if the user added a car with 'engine size', 'color'...etc?

So I've researched and I think I have the following options:

1) Dynamically create a new table for each category that is added and store all items within that category together in the same table.

Problem: I hear dynamically creating tables is a bad design decision. It is harder to manage and find what I need.

2) In the item table create a 'ItemAttributeData' string column where I can store a custom string such as an XML document containing the attributes for that particular item.

Problem: These attributes aren't query-able from SQL and would have to be processed manually in the code.

3) Go with a NoSQL solution such as MongoDB or azure table storage (it's an ASP.NET app) and create an items collection where each item can have a different set of columns.

Problem: I lose the relational mapping from categories to items and other tables like 'Users' (I think?)

4) Combine RDBMS and NoSQL such that the schematic-less attributes are stored in a NoSQL itemAttributes collection and the shared item properties are stored in the relational database. Then link them using the itemId.

What do you think the is best proposed solution in terms of extensibility and performance?

Answer 1

If I was you I would go for another option: an EAV model.

You can easily create some views for CD's and DVD's from that model that resemble flat tables for each category.

Once you get the hang of it, it is quite simple. And it performs well.