Re: PostgreSQL Developer Best Practices

On Wed, Aug 26, 2015 at 5:23 AM, rob stone <floriparob(at)gmail(dot)com> wrote:

> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I am in favour of using BIGINT "id" for the primary key in each table I
create.
I found out that in the fields in my tables that I thought would be unique
end up not being so in the longer term.
Also these values may need to be updated for some reason.

I have been using PRIMARY KEY(id) where id is of type BIGINT on each table
I create.
I use a sequence to provide a default value to this field.
I create one such sequence DB object per table and the use it in the table
definition.
For example if I have a sequenceDB "some_schema.some_table_seq" for table
"some_schema.some_table".
In the table definition of "some_schema.some_table" I have the field "id"
as follows.

id BIGINT NOT NULL DEFAULT NEXTVAL('some_schema.some_table_seq')

When I use this "id" field as a foreign key in another table, I would
prefix it with the name of its parent table followed by a couple of
underscores as shown below.
FOREIGN KEY(some_table__id)REFERENCES some_schema.some_table(id)ON UPDATE
CASCADE ON DELETE CASCADE

For the composite keys that are unique (for now) I create a unique
constraint.

Allan.