Re: PostgreSQL Developer Best Practices

On 26-08-2015 10:13, Allan Kamau wrote:
> 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 [1]
>
> 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.

I recall the words of my professor at last lecture of Databases was
telling us that model of thinking as he told: nomenclature is wrong and
not good and we should avoid it in any cost if we can.

Cheers,
Hristo