Re: Help finding natural keys

In message <3e27fe35$1_at_news.uia.ac.be>, Jan Hidders <hidders_at_hcoss.uia.ac.be> writes
>In article <nfMV9.89095$t06.4191316_at_news2.east.cox.net>,
>Alan Gutierrez <ajglist_at_izzy.net> wrote:
>>I've been reading this group and considering the design of my database used
>>for management of patient charts.
>>
>>I am generating keys using select max. I've considered what Mr Celko says
>>about how this is just a sequential file model in SQL, but I try as I might,
>>I can't think of good, er, natural keys for my entities.
>
>Just for the record let me state that the use of surrogate identifiers is a
>controversial issue. Even experienced database designers sometimes differ in
>their opinions over them. But what everybody does agree about is that even
>if you use them you still have have to also define natural keys. If you
>cannot find those, your data model is very probably faulty.
>
>>Have I just fallen into a bad habit? Are there candidate keys for things
>>like a person that I just cannot see? Below are some examples from my
>>database.
>>
>>create table firm (
>> firm_id integer not null,
>> firm_name varchar(32),
>> primary key (firm_id)
>>);
>>
>>The firm may change it's name, so that doesn't make for a good key.
>
>The only requirement for a key is that *at every moment* there are no
>two entities (tuples) for which these values are the same. Since the firm
>name will be unique before the change and after the change it is therefore a
>good key.

There are some pragmatic problems. I've worked with systems that had up to 6 entries for the same company at the same address. If there is a way to ensure that company names are always entered in exactly the same way each time that risk is eliminated.

In modern systems free-text fields used a foreign-keys can automatically propagate changes to key values throughout the database. Systems that can't do that should treat key-value changes with care.

-- 
Bernard Peek
bap_at_shrdlu.com
www.diversebooks.com: SF & Computing book reviews and more.....

In search of cognoscenti