Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Table Design for a column with only a few records

    Hi,

    I've a table and a column in this table will only be used/has a value for about 1% of all records.
    What will be the best design priciple for such a column?

    cheers
    Charlie

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    either
    define the column, and allow null values
    OR
    take a more zealous approach (if you believe all nulls are bad) and create a table just to hold that one value.

    the second approach is probably more space efficient (as you only store the column when required, although there will be some negligible overhead)
    the first approach probably more run time efficient (as there is one less join to process).

    there are those who advocate that null brakes the rules of relational db design.
    ...and there are those who don't believe that a NULL value has any place in a DB schema
    ...and there are those who think NULL's are perfectly acceptable values (as long as you now why you are allowing NULL's in the first place and handle them correctly)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What is the datatype of the column?
    Space is likely not a significant concern. I'd almost always recommend option 1, not being a "null purist".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem View Post
    ... null brakes the rules of relational db design.
    certainly slows 'em down a bit, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by r937 View Post
    certainly slows 'em down a bit, eh
    oops sorry about that, been playing to much wordscraper.....
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    ...been playing too much wordscraper....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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