SQL Server Dynamic Columns Problem

 

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.

  • 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.
  • Create a table with all possible columns in all of the gadget types.
  • Create separate table for each type of gadgets.
  • Any other better way you recommend?

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

    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 (eg a stored procedure).

    Your options:

  • Good one. Could even force schema etc
  • You cannot make those column NOT NULL, so you will loose some data integrity there
  • As long as you do not allow search for more then one type of gadget, it is good enough, but option 1 is better
  • 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

    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.

    链接地址: http://www.djcxy.com/p/68626.html

    上一篇: 在sql查询中添加N个动态列

    下一篇: SQL Server动态列问题