Re: Best practices for "Gender Column"
From: joel garry <joel-garry_at_home.com>
Date: Tue, 7 Jul 2009 14:35:07 -0700 (PDT)
Message-ID: <73857a80-8bb5-4c54-9ac6-b21f774d2b9c_at_i4g2000prm.googlegroups.com>
On Jul 7, 2:15 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
> "jose" <josed..._at_gmail.com> wrote in message
>
> news:c00970a7-a56c-4066-8782-ccecdb81ddea_at_d32g2000yqh.googlegroups.com...
>
> > This days, I'm participating in the design of a new application for a
> > Biotechnology company. I found the following:
> > -In some applications they use for (Gender/sex column) NUMBER(1) and
> > this could be Null because in some cases they don't know the Gender
> > until they get the genotype, but in this applications the values are
> > (1 = Male , 2 = Female and Null ), the problem with this solution is
> > that in some cases when the people is reading the information always
> > is asking 1 is for male o for female ?
> > -In the new application I suggest to use VARCHAR2(1) for this column
> > and this must be NOT NULL, and the possible values must be M=Male
> > F=Female and U= Unknown. I think is better use a value against a Null
> > because of the indexes, I mean in case we decided to index by this
> > column the null values will never be indexed.
>
> > I'm posting this to here any comments, thanks, Uriel.
>
> Looks ok to me, you could then add a check constraint to the column (add
> CONSTRAINT gender_name CHECK (gender IN ('M','F','U')) and you could add a
> foreign key constraint to a "gender" table, with the varchar2(1) column as
> PK constraint and an additional column that describes the gender type.
> But what about hermaphrodite organisms? ;-)
>
> Matthias
Date: Tue, 7 Jul 2009 14:35:07 -0700 (PDT)
Message-ID: <73857a80-8bb5-4c54-9ac6-b21f774d2b9c_at_i4g2000prm.googlegroups.com>
On Jul 7, 2:15 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
> "jose" <josed..._at_gmail.com> wrote in message
>
> news:c00970a7-a56c-4066-8782-ccecdb81ddea_at_d32g2000yqh.googlegroups.com...
>
> > This days, I'm participating in the design of a new application for a
> > Biotechnology company. I found the following:
> > -In some applications they use for (Gender/sex column) NUMBER(1) and
> > this could be Null because in some cases they don't know the Gender
> > until they get the genotype, but in this applications the values are
> > (1 = Male , 2 = Female and Null ), the problem with this solution is
> > that in some cases when the people is reading the information always
> > is asking 1 is for male o for female ?
> > -In the new application I suggest to use VARCHAR2(1) for this column
> > and this must be NOT NULL, and the possible values must be M=Male
> > F=Female and U= Unknown. I think is better use a value against a Null
> > because of the indexes, I mean in case we decided to index by this
> > column the null values will never be indexed.
>
> > I'm posting this to here any comments, thanks, Uriel.
>
> Looks ok to me, you could then add a check constraint to the column (add
> CONSTRAINT gender_name CHECK (gender IN ('M','F','U')) and you could add a
> foreign key constraint to a "gender" table, with the varchar2(1) column as
> PK constraint and an additional column that describes the gender type.
> But what about hermaphrodite organisms? ;-)
>
> Matthias
And of course, what if the genotype doesn't match the phenotype? And the phenotype is malleable... as always, it depends on deeper requirements analysis.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/jul/07/busbys-event-was-called-8216loud-democratic-rally8/?northcounty&zIndex=127859Received on Tue Jul 07 2009 - 16:35:07 CDT