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
