SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Spell out values or not?

    Is it better to spell out values in a field or use something like numbers?

    For example...

    Gender
    a.) Male/Female
    b.) M/F
    c.) 0/1
    d.) 1/2


    Marital Status
    a.) Single/Married
    b.) S/M
    c.) 1/2


    Residency
    a.) Resident/Non-Resident
    b.) 1/2

    and so on...


    Also, if you say something like "Use a Boolean for Gender" then do you create a Look-up Table as well?

    I'm asking this more from the standpoint of determining if I should be creating PHP Functions to translate database values and display what I want, or if I should rely on the database to do all of the work for me?

    Thanks,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would spell them out

    simpler, isn't it

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

  3. #3
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ^ what he said

    +

    it makes your code easier to understand next year when you have to change it. And you wouldnt consider naming your fields 'field1' 'field2' would you !!

    if($row['Gender'] == 'Male'){
    is so much quicker to understand than
    if($row['Field1'] == '0'){
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mandes View Post
    ^ what he said

    +

    it makes your code easier to understand next year when you have to change it. And you wouldnt consider naming your fields 'field1' 'field2' would you !!

    if($row['Gender'] == 'Male'){
    is so much quicker to understand than
    if($row['Field1'] == '0'){

    How would you ensure Data Integrity if things are spelled out?

    Some people recommend using an Integer field like I have now, but also have a Lookup Table with the spelled out names.

    Thoughts on that?


    Another option would be to use the ENUM Data-Type...


    Or I could just hope my code holds and that someone doesn't go into the back-end and types "Woman"...


    Debbie

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Some people recommend using an Integer field like I have now, but also have a Lookup Table with the spelled out names.
    who are these people? are you posting these questions elsewhere as well?

    to ensure integrity, use a lookup table, and use the spelled out names without a number

    as for ENUM, it is the spawn of the devil, and should be avoided
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    who are these people?
    Just a few people I know, and also some articles I saw online.


    are you posting these questions elsewhere as well?
    Nope.


    to ensure integrity, use a lookup table, and use the spelled out names without a number
    So create a one field table like this...
    Code:
    gender
    ---------
    gender
    Isn't that a real drag when you build queries?

    What if I need 10 Lookups? Then I'd have to do 10 INNER JOINS?!


    as for ENUM, it is the spawn of the devil, and should be avoided
    Isn't that what we are doing with a one-field table?!


    Debbie

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Isn't that a real drag when you build queries?
    nope, it's simple

    it's also completely obvious when you read the sql what the intention is


    Quote Originally Posted by DoubleDee View Post
    What if I need 10 Lookups? Then I'd have to do 10 INNER JOINS?!
    possibly... but if you use spelled-out names instead of numbers, you won't have to do any joins at all

    do not make the mistake of building a "one true lookup table" (search that phrase for reasons why)


    Quote Originally Posted by DoubleDee View Post
    Isn't that what we are doing with a one-field table?!
    no

    ENUM is different, and evil
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    possibly... but if you use spelled-out names instead of numbers, you won't have to do any joins at all
    I'm not understanding you.

    If I have a User table, and in it is a Gender field, and I want to be sure users can only choose "Male" or "Female" and that I only display those two chooses, then I would need a Lookup Table.

    And as I understand you, it should only be one field (i.e. "gender")

    So in order to use that Lookup Table I would need to do...
    Code:
    SELECT id, name, gender
    FROM user AS u
    INNER JOIN gender AS g
    ON u.gender=g.gender;
    Right?


    Debbie

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    If I have a User table, and in it is a Gender field, and I want to be sure users can only choose "Male" or "Female" and that I only display those two chooses, then I would need a Lookup Table.
    correct, you need the lookup table so that data integrity is assured for inserts to your users table



    Quote Originally Posted by DoubleDee View Post
    So in order to use that Lookup Table I would need to do...
    no, you don't need that join
    Code:
    SELECT id, name, gender
    FROM user AS u
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    correct, you need the lookup table so that data integrity is assured for inserts to your users table

    no, you don't need that join
    Code:
    SELECT id, name, gender
    FROM user AS u
    My brain is fading...

    Since my front-end is HTML/PHP, and I want to ensure that a User selects either "Male" or "Female" and that only one of those two values is written into my database, then do I just use something like a Drop-down Menu to ensure that one of those two values is used?

    Or - as I was thinking above - do I somehow need to grab values from a Look=up Table and write those into the User table?


    Debbie

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    do I somehow need to grab values from a Look=up Table
    yes, that's how you construct the dropdown menu

    but when the form is submitted, the field value is automatically checked for correctness (i.e. you don't use a separate lookup query) by the foreign key in the users table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, that's how you construct the dropdown menu

    but when the form is submitted, the field value is automatically checked for correctness (i.e. you don't use a separate lookup query) by the foreign key in the users table
    I'm getting more confused here...

    I create my User table with these fields...
    Code:
    - id
    - name
    - gender
    - eye_color

    And then I create a Lookup Table with this one field...
    Code:
    - gender

    In my web-form I have some form control that offers "Male" and "Female" as choices.

    When the form is submitted, I do an INSERT like this...
    Code:
    	$q3 = "INSERT INTO user(id, name, gender, eye_color)
    			VALUES(?, ?, ?, ?, ?)";
    How is my Look-Up Table enforcing anything?

    Do I need to add some constraints?

    And *if* the database is enforcing that "gender" must be either "Male" or "Female" then what happens if that isn't the case?

    Now that I think about it, I've actually always relied on my Front-End to do the data validation and ensure that the right data goes into the right fields?

    Sounds like you are talking about something different...


    Debbie

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    How is my Look-Up Table enforcing anything?
    by use of a foreign key to the lookup table



    Quote Originally Posted by DoubleDee View Post
    Do I need to add some constraints?
    yes, the foreign key



    Quote Originally Posted by DoubleDee View Post
    And *if* the database is enforcing that "gender" must be either "Male" or "Female" then what happens if that isn't the case?
    you get an error saying that foreign key integrity has been violated, and the insert fails

    note you can catch the error in php and issue your own user-friendly error message (but don't ask me how to do that, as i don't do php)



    Quote Originally Posted by DoubleDee View Post
    Now that I think about it, I've actually always relied on my Front-End to do the data validation and ensure that the right data goes into the right fields?
    front-end validation can be bypassed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    front-end validation can be bypassed
    So how far do I take validation on the back-end?

    Do I need a data validation constraint on every field?



    Debbie

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, not on every column

    user's last name, for instance, would be horrendous to set up a lookup table for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, not on every column

    user's last name, for instance, would be horrendous to set up a lookup table for
    But so you would recommend a Lookup Table on anything that has a finite universe, e.g. gender, marital status, U.S. States, (possibly) year born, etc?

    Is there a way in MySQL you can write other Constraints that check things like isNumeric, isValidZip, isCurrency, etc?


    Debbie

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    But so you would recommend a Lookup Table on anything that has a finite universe, e.g. gender, marital status, U.S. States, (possibly) year born, etc?
    anything? no, it's up to you which ones require strict conformance

    for instance, year born i would simply have as numeric (and the form field would be a text box, not a dropdown) -- so if my user enters 1492, so be it

    it really depends on you and your needs



    Quote Originally Posted by DoubleDee View Post
    Is there a way in MySQL you can write other Constraints that check things like isNumeric, isValidZip, isCurrency, etc?
    in general, no

    although the obvious "is numeric" constraint is enforced by the datatype of the column being one of the numeric datatypes (like INTEGER)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Debbie

    You can avoid these lookup tables by just making sure that you check for validity within the php script that writes to the database. Youre going to have to have a check in there for the examples above that cant be handled by the database alone, so you may as well put all the checks in the same place and be done with it.

    Anybody that suggests that you replace meaningful code for numbers needs shooting and putting out of their misery IMO ;-) , the only reason why you would do such a thing would be to obfuscate the code to prevent (well make it harder anyway) someone from understanding your scripts. But that includes you too !!

    So you code that receives the posted data from your form will check each field for validity before attempting to insert the data in the table. The year born example could check that there are only 4 numbers and that the year entered is between 10 -90 years ago. For zipcodes, email addresses, telephone numbers etc there are readily available regex's for checking these and PHP has filter fuctions too for this purpose.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mandes View Post
    You can avoid these lookup tables by just making sure that you check for validity within the php script that writes to the database.
    i completely disagree

    the lookup tables, involving foreign keys for data integrity, are best practice

    to advise someone to "avoid" them is, what shall i say, very disagreeable

    other stuff, like zip code regular expressions, yes, you would/could/should do those
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK Rudy enlighten me .... please explain why she should have a lookup table that validates the gender if she has already validated the gender 100% in php before she writes to the database.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mandes View Post
    OK Rudy enlighten me .... please explain why she should have a lookup table that validates the gender if she has already validated the gender 100% in php before she writes to the database.
    because php might not be the only way to enter data into the table

    because validating it with php is extra work to code, requiring code modifications whenever the set of valid values has to change

    there's two good reasons, i'm sure there are others
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Mmmm, weak arguement IMO

    OK I agree if you were using lots of different scripts written in different languages then using foreign key refs to validate would be a good idea, but with the case in hand most of the data will have to be validated in PHP (or whatever language is being used) as SQL cant validate it on its own, so adding one extra line to validate the gender would be niether here or there, and would be less than the coding required to handle the error generated by a failed foreign key validation.

    Thanks for the enlightenment anyway
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming


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
  •