Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!138.195.8.3.MISMATCH!news.ecp.fr!news.in2p3.fr!in2p3.fr!proxad.net!feeder1-2.proxad.net!74.125.46.134.MISMATCH!postnews.google.com!i4g2000prm.googlegroups.com!not-for-mail From: joel garry Newsgroups: comp.databases.oracle.server Subject: Re: Best practices for "Gender Column" Date: Tue, 7 Jul 2009 14:35:07 -0700 (PDT) Organization: http://groups.google.com Lines: 42 Message-ID: <73857a80-8bb5-4c54-9ac6-b21f774d2b9c@i4g2000prm.googlegroups.com> References: <4a53bb07$0$2850$ba620e4c@news.skynet.be> NNTP-Posting-Host: 12.232.112.189 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1247002508 23982 127.0.0.1 (7 Jul 2009 21:35:08 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 7 Jul 2009 21:35:08 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: i4g2000prm.googlegroups.com; posting-host=12.232.112.189; posting-account=tpQovAkAAABNoH5bwsZAiff2L0zxGwdv User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Jul 7, 2:15=A0pm, "Matthias Hoys" wrote: > "jose" wrote in message > > news:c00970a7-a56c-4066-8782-ccecdb81ddea@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 =3D Male , 2 =3D Female and Null ), the problem with this solution i= s > > 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=3DMale > > F=3DFemale and U=3D Unknown. I think is better use a value against a Nu= ll > > 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 a= s > 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 -- @home.com is bogus. http://www3.signonsandiego.com/stories/2009/jul/07/busbys-event-was-called-= 8216loud-democratic-rally8/?northcounty&zIndex=3D127859