let's say you are designing a table with student data.

one of the fields will be gender, which has a fairly constant set of
possible values - male, female, hermaphrodite, unknown

As such, should a "people_gender" table be created of type

INT gender_id
VARCHAR(40) gender_name

or should there be an enum column in the student table with the 4
possible values?

A larger more general question is: should you _ever_ use the enum data
type? Why or why not?

There is surprisingly little in the archives of this group regarding
this.

The Quiet Center wrote:
I think that a special table for constraining gender values is overkill,
and I would opt for setting the value directly in the student data table.

If you keep the separate table, make sure to set a uniqueness-constraint
on the gender_name column.

By the way: Even if you keep your people_gender table, you could consider
dropping the gender_id column. Then your gender data in the student data
table would have a foreign key to the remaining, sole gender_name column
in people_gender. This would make queries shorter and more readable,
because of less joins. Some people will come screaming that you can save
some bytes by encoding the gender in the student data table, but common...


The ISO SQL standard has ny ENUM data type, which makes it problematic.
The standard way of doing it is using CHECK-constraints:

CREATE TABLE students (
student_id INT NOT NULL PRIMARY KEY,
gender VARCHAR(40) NOT NULL CHECK (
gender in ('male','female','hermaphrodite','unknown')
)
)

Note: You might use NULL as the special unknown-"value".

--
Troels

On 23 Feb, 19:54, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote:
[...]
But then again, you might want to display gender in various languages.
Gender is by the way described in http://en.wikipedia.org/wiki/ISO_5218.

I assume the OP's using mysql (I don't know any other dbms's where it
is commonly used) which don't support check constraints. However,
enum's are highly confusing since they are compared by position, not
value. Example:

create table t1 ( x enum ('a','b') not null primary key)
engine=innodb;
create table t2 ( x enum ('c') not null primary key, foreign key (x)
references t1 (x) ) engine=innodb;
insert into t1 (x) values ('a');
insert into t2 (x) values ('c');

mysql> select * from t1;
+---+
1 row in set (0.01 sec)

mysql> select * from t2;
+---+
1 row in set (0.00 sec)

/Lennart

On 23.02.2010 21:11, Lennart wrote:
The question is whether these different *representations* should go into
the database. For a TIMESTAMP you do not store the value in all formats
that you need. Space and performance is only one reason - the more
important reason is that the user interfacing software must decided how
to represent data. The database should only store *properties* of entities.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 25 Feb, 08:06, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
[...]
How is the representation of a timestamp relevant here?

How can the user interface figure out the name of something in a
particular language?

/Lennart

On 02/25/2010 08:08 PM, Lennart wrote:
That was an example to demonstrate the point: the fact that a gender
should be output in different languages dies not mandate placing names
on all languages in the database. The name of a gender in some language
is the same as a particular timestamp format. The database needs to
hold the value, not a particular representation.

For that typically resource files are used. You only need some form of
key from the DB but not the name of the gender. In once case you might
want to write the full name, in another place you want just an
abbreviated name of the gender and in yet another place of the UI you
use an icon. All this is better handled outside the database.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 25 Feb, 22:57, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
[...]
Whether one should keep the resource file in the file system or in the
data model will of course depend, but I see your point.


/Lennart