Search Postgresql Archives

Primary keys for companies and people

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux