Re: Primary keys for companies and people

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.