Michael Glaesemann wrote:
Hello, all!
Recently there was quite a bit of discussion regarding surrogate keys
and natural keys. I'm not interested in discussing the pros and cons of
surrogate keys. What I'd like to find out are the different methods
people actually use to uniquely identify companies and people *besides*
surrogate keys.
I'm currently working on an application that will include contact
information, so being able to uniquely identify these two entities is
of interest to me. Right now I'm thinking of uniquely identifying
companies by telephone number. For example:
create table companies (
company_id integer primary key -- telephone number, not serial
, company_name text not null
);
Of course, the company may have more than one telephone number
associated with it, so there will also be a table associating telephone
numbers and companies.
create table companies__telephone_numbers (
company_id integer not null
references companies (company_id)
on update cascade on delete cascade
, telephone_number integer not null
, unique (company_id, telephone_number)
);
There should also be a trigger that will check that the company_id
matches an existing telephone number associated with the company,
something like:
create function assert_company_id_telephone_number_exists
returns trigger
language plpgsql as $$
begin
if exists (
select company_id
from companies
except
select company_id
from companies
join companies__telephone_numbers on (company_id = telephone_number)
)
then raise exception 'company_id must match existing company telephone
number';
end if;
return null;
end;
$$;
For people I'm more or less stumped. I can't think of a combination of
things that I know I'll be able to get from people that I'll want to be
able to add to the database. Starting off we'll have at least 7,000
individuals in the database, and I don't think that just family and
given names are going to be enough. I don't think we'll be able to get
telephone numbers for all of them, and definitely aren't going to be
getting birthdays for all.
I'm very interested to hear what other use in their applications for
holding people and companies.
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Telephone numbers make bad primary keys because they get recycled. A phone
number that belongs to me this year may belong to somebody else next year.
