Re: PostgreSQL Developer Best Practices
Adrian,
Stop being so technical. When we/I speak of natural keys, we are talking about the column
that would NATURALly lend itself as the primary key.
No one ever said a number is not natural. just that there is no need to duplicate uniqueness
with a separate number.
IOW: If we have an account table, then the account_id or account_no
would be the primary key. There is no need to have a separate serial id as the primary key.
Likewise, if we have a car table, then registration (or vehicle_id) is preferred.
EG: Good
CREATE TABLE car
(
registration_no varchar(30) not null,
car_make varchar(25) not null,
model varchar(15) not null;
build_year date not null;
owner varchar(50),
CONSTRAINT car_pk PRIMARY KEY (registration_no)
);
bad
CREATE TABLE car
(
id serial not null,
registration_no varchar(30) not null,
car_make varchar(25) not null,
model varchar(15) not null;
build_year date not null;
owner varchar(50),
CONSTRAINT car_pk PRIMARY KEY (id)
);
The benefit in avoiding arbitrary and simple values for the key is that it makes
the database design much more logical.
Consider:
SELECT c.registration_no,
c.car_make,
p.part_no
FROM car c
JOIN parts p ON ( p.registration_no = c.registration_no)
WHERE registration_no = <some_var>;
versus:
SELECT c.registration_no,
c.car_make,
p.part_no
FROM car c
JOIN parts p ON ( p.id = c.id)
WHERE registration_no = <some_var>;
Why join on id when registration_no is better?
Stop being so technical. When we/I speak of natural keys, we are talking about the column
that would NATURALly lend itself as the primary key.
No one ever said a number is not natural. just that there is no need to duplicate uniqueness
with a separate number.
IOW: If we have an account table, then the account_id or account_no
would be the primary key. There is no need to have a separate serial id as the primary key.
Likewise, if we have a car table, then registration (or vehicle_id) is preferred.
EG: Good
CREATE TABLE car
(
registration_no varchar(30) not null,
car_make varchar(25) not null,
model varchar(15) not null;
build_year date not null;
owner varchar(50),
CONSTRAINT car_pk PRIMARY KEY (registration_no)
);
bad
CREATE TABLE car
(
id serial not null,
registration_no varchar(30) not null,
car_make varchar(25) not null,
model varchar(15) not null;
build_year date not null;
owner varchar(50),
CONSTRAINT car_pk PRIMARY KEY (id)
);
The benefit in avoiding arbitrary and simple values for the key is that it makes
the database design much more logical.
Consider:
SELECT c.registration_no,
c.car_make,
p.part_no
FROM car c
JOIN parts p ON ( p.registration_no = c.registration_no)
WHERE registration_no = <some_var>;
versus:
SELECT c.registration_no,
c.car_make,
p.part_no
FROM car c
JOIN parts p ON ( p.id = c.id)
WHERE registration_no = <some_var>;
Why join on id when registration_no is better?
On Tue, Aug 25, 2015 at 10:17 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 08/24/2015 08:44 PM, Rob Sargent wrote:On Aug 24, 2015, at 6:53 PM, Melvin Davidson <melvin6925@gmail.com> wrote:What, pray tell, is the unique natural key of person in any meaningfully large domain such as state? Certainly not name + birthdate. Current address isn’t guaranteed. Social isn’t reliable and actually not truly unique.
You are right, he was probably talking about FK's. I was just so frustrated about people insisting that using "ID" as the primary key in every table is a "good" idea,
I didn't bother to reply previously. I stand firm on my belief that the primary key should be something meaningful and NOT "id" just for the sake of having a unique numeric key.
To add:
1) Who determined that a number is not natural?
2) One of the older unique natural keys (genus, species) is not so unique. I am a fisheries biologist by training and in my time the 'unique' identifier for various fishes has changed. Now that ichthyologists have discovered DNA testing, it can be expected there will be even more changes. This is even more apparent when you go back in in history. As an example:
https://en.wikipedia.org/wiki/Rainbow_trout
Rainbow trout
Current
Oncorhynchus mykiss
Past
Salmo mykiss Walbaum, 1792
Parasalmo mykiss (Walbaum, 1792)
Salmo purpuratus Pallas, 1814
Salmo penshinensis Pallas, 1814
Parasalmo penshinensis (Pallas, 1814)
Salmo gairdnerii Richardson, 1836 <--The one I learned.
Fario gairdneri (Richardson, 1836)
Oncorhynchus gairdnerii (Richardson, 1836)
Salmo gairdnerii gairdnerii Richardson, 1836
Salmo rivularis Ayres, 1855
Salmo iridea Gibbons, 1855
Salmo gairdnerii irideus Gibbons, 1855
Salmo irideus Gibbons, 1855
Trutta iridea (Gibbons, 1855)
Salmo truncatus Suckley, 1859
Salmo masoni Suckley, 1860
Oncorhynchus kamloops Jordan, 1892
Salmo kamloops (Jordan, 1892)
Salmo rivularis kamloops (Jordan, 1892)
Salmo gairdneri shasta Jordan, 1894
Salmo gilberti Jordan, 1894
Salmo nelsoni Evermann, 1908
All the above point to the same fish and have appeared and appear in articles and reports about said fish. Lets not even get into the common name situation:).
Even given that there are models which are made of entities with legitimate attributes which per force define a unique instance, I see no benefit in avoiding the convenience of an arbitrary and simple value for the key. Is it the overhead of generating and storing one more value per tuple that you can’t abide?
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
В списке pgsql-general по дате отправления: