pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Primary keys: can they be alphanumerical?
3+ messages / 3 participants
[nested] [flat]

* Primary keys: can they be alphanumerical?
@ 1998-07-06 12:41 Stuart Rison <stuart@ludwig.ucl.ac.uk>
  1998-07-06 13:25 ` Re: [SQL] Primary keys: can they be alphanumerical? Brett W. McCoy <bmccoy@lan2wan.com>
  1998-07-06 13:38 ` Re: [SQL] Primary keys: can they be alphanumerical? Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 2 replies; 3+ messages in thread

From: Stuart Rison @ 1998-07-06 12:41 UTC (permalink / raw)
  To: pgsql-sql

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.                      |
+-------------------------+--------------------------------------+





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Primary keys: can they be alphanumerical?
  1998-07-06 12:41 Primary keys: can they be alphanumerical? Stuart Rison <stuart@ludwig.ucl.ac.uk>
@ 1998-07-06 13:25 ` Brett W. McCoy <bmccoy@lan2wan.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Brett W. McCoy @ 1998-07-06 13:25 UTC (permalink / raw)
  To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Primary keys: can they be alphanumerical?
  1998-07-06 12:41 Primary keys: can they be alphanumerical? Stuart Rison <stuart@ludwig.ucl.ac.uk>
@ 1998-07-06 13:38 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 3+ messages in thread

From: Herouth Maoz @ 1998-07-06 13:38 UTC (permalink / raw)
  To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1998-07-06 13:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-06 12:41 Primary keys: can they be alphanumerical? Stuart Rison <stuart@ludwig.ucl.ac.uk>
1998-07-06 13:25 ` Brett W. McCoy <bmccoy@lan2wan.com>
1998-07-06 13:38 ` Herouth Maoz <herouth@oumail.openu.ac.il>

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