Re: FK -> non PK - bad design?

"Mike" <Star.Point_at_mcsci.net> wrote in message news:a90c0da6.0304111119.2456eae9_at_posting.google.com...
> 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.

The original candidate key was not a name. It was an integer called 'type'. Unless users change the integers representing 'type' frequently, there is probably no reason to add a redundant integer key and thereby force users into unecessary joins. Received on Fri Apr 11 2003 - 21:46:24 CEST