Re: [GENERAL] using ID as a key

Hi Sheila,

For general database design considerations (not specific to Postgres) I
disagree with the others on the use of serials and sequences. These
things never migrate well from platform to platform, they often break, and
dealing with them is a nightmare if you ever have to do any bulk data
copying.
ID generation ought to be handled programmatically.

As far as referential integrity is concerned, that is supposed to be
implemented in the next release, due out real soon now. Personally I'm
waiting for that release.

Finally, since you proclaim that you are new to database design, I cannot
pass up this opportunity strike out against the forces of evil. Unless you
ABSOLUTELY cannot avoid it, never use more than one field as your primary
key. Oh, it seems like a good idea at design time, but it's not. If you
use
complex primary keys, you and your programmers will regret it for the rest
of
the application's useful life.

For what it's worth . . . I would do it as follows:

CREATE TABLE tbl_agency
(
nagencyid INT NOT NULL,
szotherdata VARCHAR(30)
);
CREATE UNIQUE INDEX tbl_agency_pk ON tbl_agency (nagencyid);

CREATE TABLE tbl_employee
(
nemployeeid INT NOT NULL,
nagencyid INT,
szotherdata VARCHAR(30)
);
CREATE UNIQUE INDEX tbl_employee_pk ON tbl_employee (nemployeeid)

David Boerwinkle

-----Original Message-----
From: sheila bel <sheilabel(at)hotmail(dot)com>
To: pgsql-general(at)postgreSQL(dot)org <pgsql-general(at)postgreSQL(dot)org>
Date: Friday, February 04, 2000 10:50 AM
Subject: [GENERAL] using ID as a key

>Hi,
>
>I'm new to data base design so please bare with me if my
>question is so basic..
>I'm designing a database, two of the tables are EMPLOYEES and
>AGENCIES. I need an ID for each of them. I would also need to
>have the agencyID in the EMPLOYEES table so that I can identify
>which agency they belong to something like a foreign key. I know
>postgreSQL does not support it so how do I implement this ?
>What kind of data type should I use for the ID ? In general I
>do not know how to implement IDs and use them as keys.
>I've read the user manual and a bit of the programmer's
>manual several times but haven't found anything that will
>apply to this situation.
>
>Please Help. Thank You.
>
>-Sheila
>
>
>
>
>************
>