locked
Best way to add 40 new bit like columns to the existing table RRS feed

  • Question

  • Hi everybody,

    We have heavily used Items table which is not properly normalized. The number of columns in this table is ~250 and we can not have more columns there as the client application can only have 254 columns max. We need to add about 40 new bit like fields.

    The alternatives we see here are:

    XML column

    Comma-delimited column

    Two integer fields to have bit arithmetic - but too complex to look at.

    Extra table (1:1 relationship and many complications from the application design).

     

    We're using SQL 2005 and up.

    What will you suggest?

     

    ------------------------------------------

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, July 12, 2011 5:41 PM
    Answerer

Answers

  • After I posted this we discussed another idea (all the ideas were from my colleague). We're thinking of having a EAV addition such as 

    ItemID FieldName

    I think for that particular problem it will be the best solution. The problem is that we need to add required fields for the guest. So, if an item is associated with a guest record, we need to list which fields are required for guests to fill in (there are about 40 different fields where a few are required). So, by associating Required Fields with each item as 1 to many it seems like we're solving the problem we were trying to solve. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, July 13, 2011 2:45 PM
    Answerer

All replies

  • Based on your narrative I would go for the second one
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 13, 2011 6:33 AM
  • XML and Comma-delimited are the same thing, except XML has more information, is more extensible, and is supported.

    Two integer fields to have bit arithmetic -is a standard way of combining options, and being it is math, is probably the fastest.

    Extra table is nice because it is used appropriately.

    The issue seems to be not in ADDiong the COLUMNs, but in using them. if feasible, ADD them and CREATE a VIEW that implements one of the other solutions, bit arithmetic being the best as bit math is very easy on the processor.

    If that is not possible, i like the 1:1 option best, because it makes no compromises, and the two TABLEs can be joined together by a VIEW for the applications that don't have this silly limitation.

    Wednesday, July 13, 2011 10:53 AM
    Answerer
  • After I posted this we discussed another idea (all the ideas were from my colleague). We're thinking of having a EAV addition such as 

    ItemID FieldName

    I think for that particular problem it will be the best solution. The problem is that we need to add required fields for the guest. So, if an item is associated with a guest record, we need to list which fields are required for guests to fill in (there are about 40 different fields where a few are required). So, by associating Required Fields with each item as 1 to many it seems like we're solving the problem we were trying to solve. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, July 13, 2011 2:45 PM
    Answerer
  • Well, if it works. :)

    Wednesday, July 13, 2011 3:16 PM
    Answerer