Primary keys for companies and people

Поиск
Список
Период
Сортировка
Искать
От
Michael Glaesemann
Тема
Primary keys for companies and people
Дата
Msg-id
F353305E-2FB0-4612-BB82-647E85F68039@myrealbox.com
Список
Дерево обсуждения
Primary keys for companies and people Michael Glaesemann <grzm@myrealbox.com>
Re: Primary keys for companies and people "Leif B. Kristensen" <leif@solumslekt.org>
Re: Primary keys for companies and people David Goodenough <david.goodenough@btconnect.com>
Re: Primary keys for companies and people Martijn van Oosterhout <kleptog@svana.org>
Re: Primary keys for companies and people "Leif B. Kristensen" <leif@solumslekt.org>
Re: Primary keys for companies and people Merlin Moncure <mmoncure@gmail.com>
Re: Primary keys for companies and people Michael Glaesemann <grzm@myrealbox.com>
Re: Primary keys for companies and people Merlin Moncure <mmoncure@gmail.com>
Re: Primary keys for companies and people Michael Glaesemann <grzm@myrealbox.com>
Re: Primary keys for companies and people "John D. Burger" <john@mitre.org>
Re: Primary keys for companies and people Mark Dilger <pgsql@markdilger.com>
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
Дата:
От: Leif B. Kristensen
Дата:
FAQ