Re: FK -> non PK - bad design?

"Bob Badour" <bbadour_at_golden.net> wrote in message news:<qyFla.330$hD7.33053736_at_mantis.golden.net>...
> "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.

Bob, your right, there is no "service plan name" just a "type" that is an integer. I see no reason for the service_plan table at all!

It is likely that OtisUsenet "has this feeling" for an ID/"name" because his very next step will be to add a column to table service_plan like "Name" and thus there will be a "My First Service Plan" with a type code of 1...

Mike Received on Sat Apr 12 2003 - 09:02:43 CEST