Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: Multiple NULLs in a table

    To make the long story short, we have a situation where there can be different datatypes for a column. For example, 'AttributeValue' can be a datetime, int, float etc. My DBA is suggesting the following design,

    tblAttribute

    AttributeId, AttributeDataTypeId, AttributeValueInt, AttributeValueFloat, AttributeValueDateTime etc

    For any AttributeId, only one of the value columns will have a value and the rest will be NULLs. I feel that this is a bad design. (FYI, We decided against SQLVariant for specific reasons). I have suggested, we create a seperate table for each datatype, like

    tblAttribute: AttributeId, AttributeDataTypeId

    tblAttributeValueFloat: AttributeId INT, AttributeValue FLOAT
    tblAttibuteValueDateTime: AttributeId INT, AttributeValue DATETIME
    etc for each datatype.

    Depending on the AttributeDataTypeId in the Attribute table go to the specific table and pick up the value. This way, we don't need to have too many NULLs in a row. Which of the 2 do you think is a better design? And if you agree with me, what would be the points to support it.

    Your time is greatly appreciated

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your DBA's design is simpler, but may result in slower performance if amount of data is considerable (10M+)

    Your design is very good, but will be more complex for queries.

    Sorry for such "straight" answer, but it depends

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    rdjabarov- Thanks for your reply. If you have 10M+ records in the table, why will it degrade the performance (if we follow my DBAs approach)?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Because AttributeValueInt, AttributeValueFloat, AttributeValueDateTime etc will have to allow nulls and even if you index them, the execution plan may be based on index scan which in your case will be almost identical to table scan.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    I get it now. Thanks for your help, rdjabarov.

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Such methots are good for small tables - for templates, forms, etc. If you are going to have table 10M+ rows - forget it. You'll regret about it late but ...

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    snail- Are you referring to having all the columns in one table or having multiple tables?

  8. #8
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by sbaru
    snail- Are you referring to having all the columns in one table or having multiple tables?
    Tell more details about an information you are going to keep and I guess somebody would give you good advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •