How to best represent items with variable # of attributes in a database?

Question

Lets say you want to create a listing of widgets

The Widget Manufacturers all create widgets with different number and types of attributes. And the Widget sellers all have different preferences on what type and number of attributes they want to store in the database and display.

The problem here now is that each time you add in a new widget, it may have attributes on it that donot currently exist for any other widget, and currently you accomplish this by modifying the table and adding in a new column for that attribute and then modifying all forms and reports to reflect this change.

How do you go about creating a database which takes into account that attributes on a widget are fluid and can change from widget to widget.

Ideally the widget attributes should be something the user can define according to his/her preference and needs

Answer 1

I would have a table for widgets and one for widget attributes. For example:

Widgets
   - Id
   - Name

WidgetAttributes
   - Id
   - Name

Then, you would have another table which has what widgets have which attributes:

WidgetAttributeMap
  - Id
  - WidgetId
      (a value from the Id column in the Widget table)
  - WidgetAttributeId
      (a value from the Id column in the WidgetAttribute table)

This way, you can add attributes to widgets by modifying rows in the WidgetAttributeMap table, not by modifying the structure of your widget table.

Answer 2

casperOne is showing the way, although I would personally add yet one more table for the attribute values, ending up with

Widgets
  -WidgetID (pk)
  -Name

WidgetAttributes
  -AttributeID (pk)
  -Name

WidgetHasAttribute
  -WidgetID (pk)
  -AttributeID (pk)

WidgetAttributeValues
  -ValueID (pk)
  -WidgetID
  -AttributeID
  -Value

In order to retrieve the results, you want to join the tables and perform an aggregate concatenation, so you can end up with data looking like (for example):

Name      Properties
Widget1   Attr1:Value1;Attr2:Value2;...etc

Then you could split the Properties string in your Business Logic Layer and use as you wish.

A suggestion on how to join the data:

SELECT w.Name, wa.Name + ':' + wav.Value 
FROM ((
    Widgets w 
  INNER JOIN 
    WidgetHasAttribute wha 
   ON w.WidgetID = wha.WidgetID)
  INNER JOIN WidgetAttributes wa
   ON wha.AttributeID = wa.AttributeID)
  INNER JOIN WidgetAttributeValues wav
   ON (w.WidgetID = wav.WidgetID AND wa.AttributeID = wav.AttributeID)

You can read more on aggregate concatenation here.

As far as performance is concerned, it shouldn't be a problem as long as you make sure to index all columns that will be frequently read - that is

• All the ID columns, as they will be compared in the join clauses
• WidgetAttributes.Name and WidgetAttributeValues.Value, as they will be concatenated