another msql datatype questions

male/female

[Sarah Atkinson]

One field identify gender.
I was thinking of using char for it and just having it either M or F. but might another bool style veriable be better one that I set the value to maybe being either M or F? maybe something that would prevent an invalid char from being entered someway.

[moltar]

ENUM('male','female')

[Sarah Atkinson]

what is the differnece between set and enum?

[moltar]

A SET is a string object that can have zero or more values.
An ENUM is a string object with a value chosen from a list of allowed values.

In other words, SET allows for multiple choices when ENUM allows only a single choice - which should work for you, since a person can only be either male or female, not both.

[ramoneguru]

Would it be easier to store that as BINARY and then just print out an 'M'/'F' or 'Male'/'Female' based on the 0/1 that is retrieved?

Male = 0
Female = 1

if (colmn_1 == '0')
echo "Male" //or whatever you want
else
echo "Female"

eh, just a thought....
--Nick

[moltar]

ramoneguru, I don't see why that would be easier. But even if it would be easier for you, I think ENUM is more readable when you view the table without any specific interface. For example through phpMyAdmin or just command prompt.

if (colmn_1 == '0')
echo "Male" //or whatever you want
else
echo "Female"

You just complicated the solution by 4 lines of additional code.

[JasonHamilton]

enum is better for readability in the database, however do some research on it - it's not so great when it comes to indexing.

[moltar]

JasonHamilton, can you please clarify. I tried to do a search and couldn't find anything related to what you said. It'd be interesting to know, because I always use

ENUM

s. Thank you.

[Sarah Atkinson]

A SET is a string object that can have zero or more values.
An ENUM is a string object with a value chosen from a list of allowed values.

In other words, SET allows for multiple choices when ENUM allows only a single choice - which should work for you, since a person can only be either male or female, not both.

So would set be good for something like "What states have you lived in durring the past 5 years?"

Then someone could select (Maine, Ohio, Florida) But it would just be stored in one field?

if this is the case then would it be a long string or an array?

Sarah

[dcrombie]

I'd just use 'M' and 'F'. It's not like that's going to cause any confusion ;)

[moltar]

For states it'd be better to have a separate table with state names and refer to them by id in your original table. For example:

states table:

state_id ¦ state_name 
1 ¦ Alabama 
2 ¦ Alaska 
3 ¦ Arizona 

customer table:

cust_id ¦ name ¦ gender ¦ state_id 
1 ¦ Bob ¦ M ¦ 2 
2 ¦ Lucy ¦ F ¦ 1 
3 ¦ Pat ¦ M ¦ 3 

[Sarah Atkinson]

I was just trying to think of an exsample where you would use SET instead of ENUM....

also moltar your state id wouldn't work if a person lived in more then one state in the past 5 years.

I guess maybe a better exsample following my train of thought would be
"What types of food do you like?"
with posible awnsers being:
Spicy
Mexican
Fast
Chinese
Korean
Sushi

Sarah

[moltar]

In that case you need a third table that holds the IDs of the foods the person chose.

Generally I'd say it depends on the situation. If the option list is relatively short and the options will not be updated often, then go with either ENUM or SET.

If you need a dynamic option list (new types of foods added constantly) or you have an extensive option list then go with a separate table(s).