From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Stuart Rison <stuart@ludwig.ucl.ac.uk>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Primary keys: can they be alphanumerical?
Date: Mon, 6 Jul 1998 16:38:53 +0300
Message-ID: <l03110700b1c68194911a@[147.233.159.109]> (raw)
In-Reply-To: <Pine.BSI.3.91.980706092124.19636B-100000@access1.lan2wan.com>
References: <l03110707b1c67506245f@[128.40.242.190]>
At 16:25 +0300 on 6/7/98, Brett W. McCoy wrote:
> On Mon, 6 Jul 1998, Stuart Rison wrote:
>
> > I would like to know what is the general concensus on having alphanumerical
> > primary keys.
>
> I use alphanumeric primary keys all the time. I keep large databases
> with FDA regulatory information in them, and the FDA assigned approval
> number, usually a letter, followed by two digits indicating the year of
> approval or the type of approval (like for a generic drug), followed by four
> digits indicating the sequence of apporval for that year. I use this
> same key as a a foreign key for, say, patent records, which is usuallu a
> one-to-many join.
To add: if the data in the primary key is already in other fields (that is,
if you have the referring hospital field, the patient number, etc. In other
fields - you can create a multi-field key. At least from the point of view
of classical RDBMS theory, it's perfectly acceptable. It saves redundancy.
However, if your intention is to use it in a primary-foreign connection -
that is, if you want to use this primary key as a foreign key in another
table, you can now see for yourself that it would be redundant. That is, if
your key is made of three fields, and it's actually repeated in every row
that relies on this key, you have a lot of redundancy. The redundancy
doesn't get smaller just because you change three fields into one
concatenated one...
In that case, I think the ideal thing to do is to create a numerical
primary key, used merely for relational purposes. You can still create an
index on the combination of fields that interests your user, or several
indices if they want to query by some, not all, of the fields you
mentioned. They need never refer to the actual primary key field. Your job
is to make them query by one index, and get the relations by another.
Of course, then, when they want to query on a related table, not on the
main table, and they want to use the referral information, you have to do a
join rather than a direct query. In that case, you're to decide: is the
redundancy problem serious enough to sacrifice speed? Will you need the
join anyway, because that's how the queries happen to go (always referring
to the father table)?
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, stuart@ludwig.ucl.ac.uk
Subject: Re: [SQL] Primary keys: can they be alphanumerical?
In-Reply-To: <l03110700b1c68194911a@[147.233.159.109]>
References: <l03110707b1c67506245f@[128.40.242.190]> <l03110700b1c68194911a@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox