On 26-08-2015 10:13, Allan Kamau wrote:
On Wed, Aug 26, 2015 at 5:23 AM, rob stone <floriparob@xxxxxxxxx>
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@xxxxxxxxxxxxxx)
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
