SQL Server Dynamic Columns Problem

Question

I use a table GadgetData to store the properties of gadgets in my application. There gadgets are basically sort of custom control which have 80% of the properties common like height, width, color, type etc. There are are some set of properties per gadget type that the unique to them. All of this data has to store in database. Currently I am storing only common properties. What design approach should I use to store this kind data where the columns are dynamic.

1. Create table with common properties as Columns and add extra column of type Text to store the all unique properties of each gadget type in XML format.
2. Create a table with all possible columns in all of the gadget types.
3. Create separate table for each type of gadgets.
4. Any other better way you recommend?

(Note: The number of gadget types could grow even beyond 100 and )

Answer 1

Depending on how different the "Gadgets" are I wouldn't like option 2 there would be a lot of nulls floating around, which could get bad if you had a column which was mandatory for one gadget but not even used for another.

I would only go option 3 if the number of gadgets changes infrequently since it would require altering the database each time.

The unmentioned option is to store the Gadgets with a child table which holds the gadgets unique values. But this would require a fair amount of work to return gadgets details, or multiple Database calls.

Leaving option 1, except I would use SQL servers XML type instead of text, you can then use XQuery within your stored procedures.

Answer 2

Option 3 is a very normalized option, but will come back and bite you if you have to query across multiple types - every SELECT will have another join if a new type is added. A maintenance nightmare.

Option 2 (sparse table) will have a lot of NULL values and take up extra space. The table definition will also need updating if another type is added in the future. Not so bad but still painful.

I use Option 1 in production (using an xml type instead of text). It allows me to serialize any type derived from my common type, extracting the common properties and leaving the unique ones in the XmlProperties column. This can be done in the application or in the database (e.g. a stored procedure).

Answer 3

Your options:

1. Good one. Could even force schema etc
2. You cannot make those column NOT NULL, so you will loose some data integrity there
3. As long as you do not allow search for more then one type of gadget, it is good enough, but option 1 is better
4. I would use ORM

Notes:

If you would like to keep your database 'relational', but are not afraid to use ORM tools, then I would use one. I which case you can store the data (almost) as you want, but have it properly handled as long as you map them correctly. See:

• Single Table Inheritance
• Concrete Table Inheritance

If you need SQL-only solution, then depending on your RDBMS, I would probably use XML column to store all the data that is specific to the gadget type: you can have validation, extend easily with new attributes. Then you can have all in one table, search quickly on all common attributes, and also pretty easily search for one gadget' type attributes as well

Answer 4

If all types of gadgets have many common mandatory properties that can be stored in one table and just several optional properties, you'd better use first approach: thus you'll use best of relational schema and ease your life with XML. And don't forget to use XML Schema collection linking XML column to it: you'll have full indexing and XQuery capabilities.

If gadget types has very different descriptions and only 1-3 common columns among 5 or more different set of properties, use 3rd approach.

---

But concerning the situation of 100+ types of gadgets I'd use 1st approach: it has flexibility supported with good performance and ease of support and further development.