[solved] about enum and set in MySQL, a newbie question.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
christian_phpbeginner
Forum Contributor
Posts: 136
Joined: Sat Jun 03, 2006 2:43 pm
Location: Java

[solved] about enum and set in MySQL, a newbie question.

Post by christian_phpbeginner »

Hi, when do we use enum or set in mySQL database ?

Please review, whether I have used and understood enum or set correctly. Okay, what I have understood about enum and set in MySQL. For example, I have a table of continents, and a table of countries. The 3rd table is the tblSummary.

tblContinent
continent_ID
continentName

tblCountry
country_ID
countryName

Normally, I might just do this:

tblSummary
summary_ID
country_ID
continent_ID

But my question: is it correct if I set the country_ID and continent_ID as enum or set in the tblSummary ?

tblSummary
summary_ID
'country_ID'
'continent_ID'

I am refering to what phpmyadmin gave infos:
1 If field type is "enum" or "set", please enter the values using this format: 'a','b','c'...
If you ever need to put a backslash ("\") or a single quote ("'") amongst those values, precede it with a backslash (for example '\\xyz' or 'a\'b').

2 For default values, please enter just a single value, without backslash escaping or quotes, using this format: a
Do the infos in phpmyadmin mean like the example given above ?

Thanks,
Chris
Last edited by christian_phpbeginner on Mon Aug 14, 2006 10:36 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your description does not benefit from using an enum in this manner.
User avatar
christian_phpbeginner
Forum Contributor
Posts: 136
Joined: Sat Jun 03, 2006 2:43 pm
Location: Java

Post by christian_phpbeginner »

feyd wrote:Your description does not benefit from using an enum in this manner.
hi feyd,

Okay, my bad, sorry ! Please just answer me, when do we use enum or set ? Do we use it when for example like : MALE or FEMALE thing, which we don't want to store that value in another table ?

Thanks,
chris
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You'll want an enum when there is a finite set of answers. If the set of answers is variable by being based on table data, it shouldn't be an enum.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I was reading about enum the other day.. apparently it can support up to ~65k different data. I'm not sure how long they can be. But it would appear to be pretty efficient.
User avatar
christian_phpbeginner
Forum Contributor
Posts: 136
Joined: Sat Jun 03, 2006 2:43 pm
Location: Java

Post by christian_phpbeginner »

Thanks for the responses so far. At least I have a bit understanding about enum in MySQL now.

I'll definitely train myself later. Thank you.

Chris
Tom420
Forum Newbie
Posts: 15
Joined: Sun Aug 13, 2006 1:50 am
Location: Sherbrooke, Québec, Canada

Post by Tom420 »

ENUM

Good for colums for which there is a finite number of answers but only one may be used at once.

gender = 'male','female'

Gender may only be either male of female, never both (let's not discuss exceptions).

Code: Select all

INSERT INTO profils SET gender='male'
In an HTML form, an enum is often represented by radio buttons or a drop-down list in which you can only select a single item.

SET

Good for colums for which there is a finite number of answers and one or more (or none) may be used at once.

languages = 'english','french','spanish'

Some speak one of those languages, some speak two, some speak all of the three.

Code: Select all

INSERT INTO profils SET languages='english,french'
In an HTML form, a set is often represented by checkboxes, or by a list in which you can select multiple items.
Post Reply