Re: Primary keys for companies and people - Mailing list pgsql-general

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.