Database design for items with variable attributes?

Question

I am creating an application that will have to store elements and categories. Information added to the database will be submitted by the user, and the user can add items to future item categories.

eg. The user can add a CD to the Music or DVD category in the Movies category. I do not want to limit the categories that can be added, or the elements that can be added to these categories. The user can pretty much add any item.

I am currently using SQL Server, and I have a table of items with ItemId (primary), Name, Description and CategoryId (foreign), referencing a Category table with similar columns.

My problem is that CDs and DVDs have different attributes, such as "runtime", "age rating", "number of tracks", so I cannot store them in one table because they will have redundant columns. What if the user added a car with "engine size", "color", etc.?

So, I researched, and I think I have the following options:

1) Dynamically create a new table for each category to be added, and save all the items in this category together in one table.

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

2) In the element table, create a row column ItemAttributeData where I can save a custom row, such as an XML document containing the attributes for this particular element.

Problem: These attributes cannot be processed by SQL queries and must be processed manually in code.

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

Problem: I lose relational mapping from categories to elements and other tables, for example, "Users" (I think?)

4) Combine the RDBMS and NoSQL so that attributes without a schema are stored in the NoSQL itemAttributes collection of elements, and the shared item properties are stored in a relational database. Then link them using itemId.

What do you think is best offered in terms of extensibility and performance?

Answer 1

If I were you, I would choose another option: the EAV model .

You can easily create multiple CD and DVD presentations from this model that resemble flat tables for each category.

Once you hang it, it's pretty simple. And it works well.

Answer 2

I never use it myself, but PostgreSql + hStore might be an interesting solution . You save Sql, and you can use semi-structured data inside the column. This column is a repository of keys / values ​​rather limited (for example, not nested), but indexed and searchable.