SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru pinch's Avatar
    Join Date
    Mar 2005
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Server - 'Gender' column

    What is the most efficient type for a 'gender' column with 'M' and 'F' options?

    -Should the 'gender' column be an index into a another lookup table?
    -Should it be a 'char' column with some sort of constraint?

    Probably an inconsequental decision but I still want to do things correctly.

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well, if you are literally going to have 'M' and 'F' values, then CHAR is really the only option

    perhaps you were thinking of a numeric alternative? i like this a lot less

    as for whether it should be an "index" into a "lookup" table, you are perhaps thinking of the foreign key concept, referencing the primary key of a gender table, and the answer is yes, you should do that

    in my opinion, 'M' and 'F' work best, because you don't have to join to the referenced table to know what they are, while still allowing the FK to ensure that you get only the valid values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru pinch's Avatar
    Join Date
    Mar 2005
    Posts
    688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in my opinion, 'M' and 'F' work best, because you don't have to join to the referenced table to know what they are, while still allowing the FK to ensure that you get only the valid values
    I think I undersand. By using 'M' and 'F' you can tell what the mean without doing a JOIN, but add the foreign key so that you constrain the possible values.

    Thanks!

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,623
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You could just constrain the column itself, no need for a foreign key to do all that.

    My vote on encoding gender is to use chromosones--XX and XY. It's much cooler.

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in my opinion, 'M' and 'F' work best, because you don't have to join to the referenced table to know what they are, while still allowing the FK to ensure that you get only the valid values
    To clarify; do you mean that the FK would still be used but that it would be 'm' or 'f', and reference to the gender table which would be set out like this

    Code:
    CREATE TABLE IF NOT EXISTS gender
    ( gender char (1) NOT NULL
    , PRIMARY KEY (gender)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
    bazz

  6. #6
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not use ENUM SET data type?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    both ENUM and SET are evil, and to be avoided in favour of more standard constructs

    yes, bazz, 'M' and 'F' would be foreign keys

    the gender table would probably have a description column to spell it what the keys mean (it's not always so obvious)

    i like FKs ~way~ better than constraints

    to state just one benefit: if you need to add an additional value, you can usually do so if you are the business owner and have been given control panel access to all your tables, you just insert a new value in the gender table, and bob's your uncle

    whereas with a constraint, you have to fill out the paperwork to get the IT department to prioritize and reserve a block of time for the DBA to schedule a change to the schema (ALTER TABLE)



    p.s. XX and XY is indeed a cool scheme (should be VARCHAR(3) then, to cater for XXY)
    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
  •