Re: [GENERAL] using ID as a key(REPOST) - Mailing list pgsql-general

hi, there,

here somebody is challeging a very common practice (using sequence).
seems that nobody really care! can some experts give us some authoritative
analysis? seems a lot of people are more willing to tutor guys who
did not do their homework than do real thinking, guess we all need
a break ;-)
---------------------------------

oracle, sql server (identity property, closer to pg's oid
but more manipulatable) have sequence.
however, since they are not sql92, so, it is a headache.
I'm now porting linux/pg/apache/perl/cgi to NT/MSsql/iis/perl/cgi,
this is a problem -- I'm now reading doc from M$ on how to
migrate oracle to mssql to get the hint of how to migrate
pg to mssql.

however, "programmatically" is really ugly: you have to use
flock, easy to be the bottleneck. -- not sure tho, any ideas?

On Fri, 4 Feb 2000 davidb@vectormath.com wrote:

> 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@hotmail.com>
> To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.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
> >
> >
> >
> >
> >************
> >
>
>
> ************
>


************