SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to handle an "Other" field properly (foreign keys, etc.)

    In my front end application I have a drop-down selection field called "customer status" with options like "Good","Suspended","Overdue","Lead" etc. These different options are stored in a table called "cust_status". The choice will be stored in a table called "customers". Typically I would store it as the foreign key from the cust_status table. However I have to have an "Other" field where you can enter in anything you want. So should I 1) have two fields in the "customers" table; one that stores the foreign key if a presented choice was chosen and a second to store the other value if that was entered. OR 2) one field, don't store the foreign key but store its actual value as a varchar. This way both the presented options and the random other text could be stored in the same field but you loose the dependency between the two tables.? Which would be a more proper way to handle this?

    Thanks,

    Jed

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    personally, i would go with the two field approach. i like having the enforced constraints on that type of field. but the other way is also perfectly acceptable in my opinion.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would also go with 2 columns

    make sure that 'Other' is also one of the FKs

    too bad MySQL doesn't (yet) support CHECK constraints, because you'd want one of those to make sure that if 'Other' was the chosen status, then the 'Other' column was not null, and vice versa
    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
  •