Re: FK -> non PK - bad design?

In message <a90c0da6.0304111119.2456eae9_at_posting.google.com>, Mike <Star.Point_at_mcsci.net> writes
>Bernard Peek <bap_at_shrdlu.com> wrote in message
>news:<Usc2Kzu5aIl+EwoR_at_diamond9.demon.co.uk>...
>> In message <5606b639.0304090641.2282467f_at_posting.google.com>, OtisUsenet
>> <otis_usenet_at_yahoo.com> writes
>> >
>> >I wanted to have a database-generated ID for each row just for sanity,
>> >and because an Oracle DBA once told me it is always good to have a
>> >db-generated id/PK in each table.
>>
>> This issue crops up here about once a month but I don't recall anyone
>> saying that they did it for reasons of sanity.
>>
>> The brief version of the argument against using a system generated
>> numeric and a separate text field is that you need to have a system to
>> keep the two synchronised. That's more work and more error-prone than
>> just using your Type field as a PK.
>>
>> Sometimes there are good reasons to choose a system generated ID but I'd
>> recommend that you avoid them if you have a real key available in the
>> data.
>
>Bernard, seems to me that just the fact that users may want to change
>the name of a service plan is reason enough for a system generated
>ID/PK.

That's perfectly true. But the risk is that you lose the ability to tell what the plan was called at the time the record was created. If you only store the ID then all you know is the current name of the plan with that ID.

This is the sort of issue you need to consider when you decide whether to use a system generated ID. Using a system generated ID is often the best alternative, but there are risks when you do it.

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

In search of cognoscenti