If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > A very basic question: data type for gender

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2002
Posts: 37
A very basic question: data type for gender

To my knowledge, many data types can be used for gender, such as char(1), int, boolean. What is the one in terms of the best practice?

Thanks for your input.
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
char(1) because it takes up less space than int

boolean is also good but some databases have poor support for boolean syntax


rudy
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2002
Posts: 37
Thanks Rudy for your reply.

I am currently having the char(1) for gender. Thinking about the boolean type might yield a better performance in terms of selection operation. I guess I shall keep the date type as what it is now.

Vernon


Quote:
Originally posted by r937
char(1) because it takes up less space than int

boolean is also good but some databases have poor support for boolean syntax


rudy
Reply With Quote
  #4 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Quote:
Originally posted by vwu98034
Thanks Rudy for your reply.

I am currently having the char(1) for gender. Thinking about the boolean type might yield a better performance in terms of selection operation. I guess I shall keep the date type as what it is now.

Vernon
While perhaps efficient, Boolean isn't a very meaningful choice for gender is it? If gender=TRUE is the person Male or Female?
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: London, England
Posts: 42
Data type for Gender

You also have to allow for U=Unknown, and even maybe different varieties of Transsexuals. So char(1) with a Reference Table which
can be used in a drop-down table is a nice approach.

Barry
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
i like the idea of a bunch of different values -- male, female, gay, lesbian, male transsexual female, female transsexual male, asexual, etc. (see the novel "Distress" by Greg Egan)

however, i must disagree with u=unknown

if it's unknown, use NULL -- that's what it's for


rudy
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: London, England
Posts: 42
[QUOTE][SIZE=1]Originally posted by r937
i like the idea of a bunch of different values -- male, female, gay, lesbian, male transsexual female, female transsexual male, asexual, etc. (see the novel "Distress" by Greg Egan)
>however, i must disagree with u=unknown
>if it's unknown, use NULL -- that's what it's for
But then you have problems with joins.

Barry
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
nope

joins can be written to anticipate nulls

they're called outer joins
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: London, England
Posts: 42
Data Types for Gender

Quote:
Originally posted by r937
nope

>joins can be written to anticipate nulls
>they're called outer joins
IMHO that's a messy design approach which I would never recommend,
and does not represent best practice.
This kind of discussion should always lead to the clarification of best practice for the topic under discussion.
Otherwise all we are doing is swapping personal opinion which is a waste of time.

If you feel outer joins represent best practice we should put it to the vote.

Barry
Reply With Quote
  #10 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
you are certainly entitled to your opinion

you do your best practice, i'll do mine

nulls are not "messy", nor are outer joins, and i'm not interested in putting them to a vote

if you like U=unknown, then please, as the kids say nowadays, knock yourself out


rudy
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Mar 2001
Location: Lexington, KY
Posts: 606
NULLs are ugly and in SQL cause all sorts of problems*.

An example is here:
http://www.pgro.uk7.net/fp2a.htm

*Chapter 10, What You Don't Know can Hurt You: Missing Information in F. Pascal, PRACTICAL ISSUES IN DATABASE MANAGEMENT, (Addison Wesley, 2000).
__________________
Thanks,

Matt
Reply With Quote
  #12 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
that guy sure hates sql, doesn't he?

i really liked date's quote at the end:

Trying to formulate expressions "directly" in SQL
is too much for the average human brain


hmm, i wonder what the calculus would be for a simple type table lookup, i'm having trouble picturing the semantics of the query...


Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Mar 2001
Location: Lexington, KY
Posts: 606
I learned relational calculus in my database course in college but cannot for the life of me remember how to do it any more.

All I remember was that it had a lot of square brackets [].

Wait, that is relational *algebra*.

__________________
Thanks,

Matt
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Posts: 13
Re: A very basic question: data type for gender

Use 1 byte fixed character field constrained to be M or F or NULL.

Do NOT use a 1 for male and 0 for female etc.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Posts: 1
Lightbulb

Quote:
Originally posted by r937
i like the idea of a bunch of different values -- male, female, gay, lesbian, male transsexual female, female transsexual male, asexual, etc. (see the novel "Distress" by Greg Egan)

however, i must disagree with u=unknown

if it's unknown, use NULL -- that's what it's for


rudy
Rudy,

Actually, there's a problem with that idea which is that gay, lesbian and bisexual have to do with sexual orientation and cannot be included in this coding for obvious reasons but if you are still wondering. Gender and sexual orientation are not the same thing.

Gender is how you identify yourself and sexual orientation is how you choose to express yourself sexually.

Asexual refers to animals not human beings, the proper term for human beings would be celibate. Because asexual means that it reproduces by itself and I don't know of any human beings who reproduce on their own, do you? I know ameobas do, for instances.

Transsexuals cannot be female and male at the same time or viceversa since the very act of changing one's gender surgically goes from one gender to the other. You may be thinking of transgendered where the lines of gender are often blurred for unknown.

In short, it's always a good idea to do one's homework thouroughly to avoid erroneous results and not to mention extremely innacurate data.

Wolf (a self-educated transgendered)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On