Re: Primary keys for companies and people
От | Mark Dilger |
---|---|
Тема | Re: Primary keys for companies and people |
Дата | |
Msg-id | 43E2CD80.9050909@markdilger.com обсуждение исходный текст |
Ответ на | Primary keys for companies and people (Michael Glaesemann <grzm@myrealbox.com>) |
Список | 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.
В списке pgsql-general по дате отправления: