SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Michigan
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using magic numbers or storing redundant data?

    Say I have a couple of columns in a database table, named BizType and AccountType. To satisfy business rules, BizType needs to be either a "Parent" business or a "Sub" business. Second, AccountType needs to be either "Business", "Personal" or "Both".

    BizType
    My first inclination was to make 1 represent "Parent" and 2 represent "Sub", and then store a series of 1s and 2s in the data column. However, after I looked how the database table looked using these "magic numbers", I was afraid it would cause another developer a bit of a headache in trying to determine what these numbers represented, even though I did put the representation in the table comments. So I changed all the 1s to Parent and the 2s to Sub and put a constraint on the table so that BizType must equal Parent or Sub. However, I still wasn't happy with how the table worked, mainly as a result of storing redundant data. I'm having trouble though justifying creating a second table that stored Parent and Sub with IDs of 1 and 2 and storing those IDs as foreign keys in my original table as these aren't likely to ever change. Also, although another developer is more likely to realize these are foreign keys and find the related table, I donít see this situation as significantly more beneficial than using magic numbers, as these values are unlikely to change.

    Account Type
    I had the same problem with this column. I was going to assign 1 to Business, 2 to Personal and 3 to Both. I didn't like using magic numbers so I decided to just stored Business, Personal and Both. But now I don't like storing redundant data, but cannot justify creating a second table to store the three values, as they are not likely to change because of business rules. Also, the future developers might still look at the table and say "What do these numbers stand for?" I don't see creating a second table storing the three values as being much more beneficial than using magic numbers.


    If anyone has ever come across a similar situation and could give me some direction/advice on what might be an appropriate way of solving this problem that would be spectacular. I know the cardinal rule of databases is to not store redundant data, but I wonder if this might be one of those exceptions, as magic numbers are frowned upon as well, although more so in programming.

    Thank you all for you time!


    Travis Rhynard

  2. #2
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use enums instead.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    nooooooooooooooooo!!!!

    sorry, that slipped out

    i personally think enums are evil, but that's a different thread



    travis, you twice mentioned redundancy as a reason for not storing a description

    i should like to point out that the column of descriptions has no more redundancy than the column of corresponding numbers

    in this day of cheap disk space, i thought we had evolved past the need to "encodify" things to save a few bytes (like the old cobol programmers did when laying the seeds for the y2k debacle)


    what would be spectacular is storing the descriptions themselves

    also, your "guarantee" that these will be the only values notwithstanding, you will want to reference them to, yes, a separate table which would have two or three rows, the allowed values for the decriptions

    by "reference them" i mean of course to declare a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Michigan
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what would be spectacular is storing the descriptions themselves

    also, your "guarantee" that these will be the only values notwithstanding, you will want to reference them to, yes, a separate table which would have two or three rows, the allowed values for the decriptions

    by "reference them" i mean of course to declare a foreign key
    So to clarify I should create a description table:

    tblDescription
    ID Description
    1 Parent
    2 Sub
    3 Business
    4 Personal
    5 Both

    ?

    Or should I create two seperate tables, a BusinessDescription table and an AccountDescription table?

    tblBusinessDescription
    ID Description
    1 Parent
    2 Sub

    tblAccountDescription
    1 Business
    2 Personal
    3 Both


    And then my orginal table would look like:

    tblOriginal
    BizType AccountType ..... ..... .....
    Parent Personal
    Parent Business
    Sub Personal
    ..... .....
    ..... .....

    Thank you both for the replies!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you would have separate description tables

    the first idea, which combines both sets of descriptions in one table, is a step in the wrong direction (do a search for "one true lookup table")

    neither the BizTypes table nor the AccountTypes table would have a numeric column, the description would be the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Michigan
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Scenario:

    End users determine that it would be more beneficial to display Enterprise instead of Business in the accounts table. In Oracle, I would have to update the Original table first, changing all of the "Business" accounts to "Enterprise" and then make the change in the description table. I'm assuming there's a way to change it in the description table and have it update the original table automatically. Am I correct in this assumption?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, your assumption is correct

    it is called ON UPDATE CASCADE and you declare this when defining the foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    Michigan
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using Oracle 9i and it seems to have a ON DELETE CASCADE, but not an ON UPDATE CASCADE...it seems I may have to use an ON UPDATE trigger? Are you familiar with Oracle and TOAD?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    familiar as in "used a long time ago" -- loved toad, hated oracle

    since maintenance on these "type" tables is so infrequent (as you said, "they are not likely to change because of business rules"), i would just run separate updates when necessary

    (instead of writing a trigger for an action that the database should be doing for you -- sorry, had to vent)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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