Обсуждение: Primary keys: can they be alphanumerical?

Dear All,

I'm using postgreSQL to create a database of breast cancer patient data.  I
am currently designing my tables.

I would like to know what is the general concensus on having alphanumerical
primary keys.

The reason for this is that, with a 15 character long alphanumerical key, I
could include hidden information in the key which could help users to
quickly get information without even having to search the database.  For
example, the first to characters could be a two-letter code for the
refering hospital, the next four a numerical patient number and subsequent
characters refer to treating  clinicians etc.

Is this a major 'faux-pas'?  Am I introducing unwanted redundancy in the
key?  Will it make indexing a lot slower?  Should primary keys be purely
numerical?

I would be grateful for any comments/suggestions.

Best regards,

Stuart.

PS.  could you cc me a copy of your posting(s).


+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
| Tel. (0171) 878 4041    | Courtauld Building                   |
| Fax. (0171) 878 4040    | 91 Riding House Street               |
+-------------------------+ London, W1P 8BT                      |
| stuart@ludwig.ucl.ac.uk | UNITED KINGDOM.                      |
+-------------------------+--------------------------------------+

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.

Brett W. McCoy
                                         http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"The Number of UNIX installations has grown to 10, with more expected."
   -- The UNIX Programmer's Manual, 2nd Edition, June, 1972

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