How do you model custom attributes of entities?

Question

Let's say we're having an application which should be able to store all kind of products. Each product has at least an ID and a Name but all other attributes can be defined by the user himself.

1. E.g. He could create a productgroup Ipods which would contain attributes capacity and generation
2. E.g. He could create a productgroup TShirts with the attributes size and color
3. We need to store the definition of a product and the concrete products itself.
4. We want to ensure that it is easily possible to aggregate (GROUP BY) by product attributes. E.g. select the total sum of capacity for each generation of ipods
5. The solution must not require schema changes (added requirement due to input from Bill Karwin - see his answer as well!)

How would you model your schema in respect to the above requirements?

Note: Requirment 4. is important!

Thanks everyone for contributing and discussing the approach. I have seen some solutions to this problem in the past but none of them made grouping easy for me :(

Answer 1

CREATE TABLE for new products and ALTER TABLE by adding/removing columns as the user performs the operations. Use the schema to know which properties each product has. This satisfies all four of your requirements.

You'd also need a table to store the other tables names or prefix the tables with something that you can query against sysobjects for the tables:

select [name] from sysobjects where [name] like 'product_%' AND xtype='U'

Answer 2

Sounds like you are looking to design a product catalog database.

I recommend this approach. http://edocs.bea.com/wlp/docs40/catalog/schemcat.htm

Answer 3

The grouping is not going to be easy because what aggregate operator are you going to use on "color"? Note that it is not possible to use your requirement 4 on case 2.

In any case, the aggregating is only difficult because of the variation in data types and can be mitigated by approaching it in a more typesafe way - knowing that it never makes sense to add apples and oranges.

This is the classic EAV model and it has a place in databases where carefully designed. In order to make it a bit more typesafe, I've seen cases where the values are stored in type-safe tables instead of in a single free form varchar column.

Instead of Values:

EntityID int
,AttributeID int
,Value varchar(255)

You have multiple tables:

EntityID int
,AttributeID int
,ValueMoney money

EntityID int
,AttributeID int
,ValueInt int

etc.

Then to get your iPod capacity per generation:

SELECT vG.ValueVarChar AS Generation, SUM(vC.ValueDecimal) AS TotalCapacity
FROM Products AS p
INNER JOIN Attributes AS aG
    ON aG.AttributeName = 'generation'
INNER JOIN ValueVarChar AS vG
    ON vG.EntityID = p.ProductID
    AND vG.AttributeID = aG.AttributeID
INNER JOIN Attributes AS aC
    ON aC.AttributeName = 'capacity'
INNER JOIN ValueDecimal AS vC
    ON vC.EntityID = p.ProductID
    AND vC.AttributeID = aC.AttributeID
GROUP BY vG.ValueVarChar

Answer 4

I'd recommend either the Concrete Table Inheritance or the Class Table Inheritance designs. Both designs satisfy all four of your criteria.

In Concrete Table Inheritance:

1. Ipods are stored in table product_ipods with columns ID, Name, Capacity, Generation.
2. Tshirts are stored in table product_tshirts with columns ID, Name, Size, Color.
3. The definition of the concrete product types are in the metadata (table definitions) of product_ipods and product_tshirts.
4. SELECT SUM(Capacity) FROM product_ipods GROUP BY Generation;

In Class Table Inheritance:

1. Generic product attributes are stored in table Products with columns ID, Name.

   Ipods are stored in table product_ipods with columns product_id (foreign key to Products.ID), Capacity, Generation.

2. Tshirts are stored in table product_tshirts with columns product_id (foreign key to Products.ID), Size, Color.
3. The definition of the concrete product types are in the metadata (table definitions) of products, product_ipods, and product_tshirts.
4. SELECT SUM(Capacity) FROM product_ipods GROUP BY Generation;

---

See also my answer to "Product table, many kinds of product, each product has many parameters" where I describe several solutions for the type of problem you're describing. I also go into detail on exactly why EAV is a broken design.