Re: PostgreSQL Developer Best Practices - Mailing list pgsql-general
From | Condor |
---|---|
Subject | Re: PostgreSQL Developer Best Practices |
Date | |
Msg-id | 4799532b5543504f8b8ae9c3e1f85f3b@stz-bg.com Whole thread Raw |
In response to | Re: PostgreSQL Developer Best Practices (Allan Kamau <kamauallan@gmail.com>) |
List | pgsql-general |
On 26-08-2015 10:13, Allan Kamau wrote: > On Wed, Aug 26, 2015 at 5:23 AM, rob stone <floriparob@gmail.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@postgresql.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
pgsql-general by date: