From: | rob stone <floriparob(at)gmail(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Jerry Sievers <gsievers19(at)comcast(dot)net>, John R Pierce <pierce(at)hogranch(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL Developer Best Practices |
Date: | 2015-08-26 02:23:37 |
Message-ID: | 1440555817.3538.13.camel@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 2015-08-25 at 20:17 -0400, Melvin Davidson wrote:
> I think a lot of people here are missing the point. I was trying to
> give examples of natural keys, but a lot of people are taking great
> delight
> in pointing out exceptions to examples, rather than understanding the
> point.
> So for the sake of argument, a natural key is something that in
> itself is unique and the possibility of a duplicate does not exist.
> Before ANYONE continues to insist that a serial id column is good,
> consider the case where the number of tuples will exceed a bigint.
> Don't say it cannot happen, because it can.
> However, if you have an alphanumeric field, let's say varchar 50, and
> it's guaranteed that it will never have a duplicate, then THAT is a
> natural primary
> key and beats the hell out of a generic "id" field.
>
> Further to the point, since I started this thread, I am holding to it
> and will not discuss "natural primary keys" any further.
>
> Other suggestions for good PostgreSQL Developer database (not web
> app) guidelines are still welcome.
>
Funny how Melvin's attempt to bring order to the chaos ended up as a
discussion about primary keys.
We once hired a "genius" to design an application to handle fixed
assets. Every table had a primary key named "id". Some were integer and
some were character. So the foreign key columns in child tables had to
be named differently. Writing the joins was complex.
I also know of an airline reservation system where you are unable to
alter your e-mail address. It apparently needs a DBA type person to
make the change. I can only guess that your e-mail address is used as a
foreign key in one or more tables. As well as assigning you a frequent
flyer number they also assign another integer identifier. A bit of
common sense goes a long way when designing an application.
Cheers,
rob
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2015-08-26 07:13:31 | Re: PostgreSQL Developer Best Practices |
Previous Message | Guillaume Lelarge | 2015-08-26 02:15:59 | Re: Grouping sets, cube and rollup |