Primary keys for companies and people

Поиск
Список
Период
Сортировка
От Michael Glaesemann
Тема Primary keys for companies and people
Дата
Msg-id F353305E-2FB0-4612-BB82-647E85F68039@myrealbox.com
обсуждение исходный текст
Ответы Re: Primary keys for companies and people
Re: Primary keys for companies and people
Список pgsql-general
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




В списке pgsql-general по дате отправления:

Предыдущее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Creating views (Multiple Databases)
Следующее
От: "Leif B. Kristensen"
Дата:
Сообщение: Re: Primary keys for companies and people