Re: FK -> non PK - bad design?

"OtisUsenet" <otis_usenet_at_yahoo.com> wrote in message news:5606b639.0304090641.2282467f_at_posting.google.com...
> lennart_at_kommunicera.umea.se (Lennart Jonsson) wrote in message
news:<6dae7e65.0304081929.34e4f8b2_at_posting.google.com>...
> > otis_usenet_at_yahoo.com (OtisUsenet) wrote in message
news:<5606b639.0304072055.29001bfe_at_posting.google.com>...
> >
> > [...]
> >
> > >
> > > CREATE TABLE service_plan
> > > (
> > > id SERIAL
> > > CONSTRAINT pk_service_plan_id PRIMARY KEY ,
> > > type INTEGER NOT NULL UNIQUE
> > > ...
> > > );
> > >
> > >
> >
> > since type is unique, what is the purpose of id?
>
> I wanted to have a database-generated ID for each row just for sanity,

With all due respect, that's not sane.

> and because an Oracle DBA once told me it is always good to have a
> db-generated id/PK in each table.

The DBA did not do you any favours.

> The unique and not-null 'type' column I wanted because its value would
> be a human-friendy, human-assigned meaningful name.

In other words, the 'type' candidate key is exactly how users will identify unique rows in the service_plan table, and identity is the function of a candidate key. If 'type' appears in referencing tables, users will know what to do with it. Perhaps you can make the 'type' column a serial column if you really want something system-assigned, or perhaps you have reasons not to. Regardless, when considering complexity and familiarity, 'type' is a simple, familiar candidate key, which makes it a better key than 'id', which will at best be a simple, unfamiliar candidate key. The only question remaining is: Is 'type' a stable key?

If the 'type' column is stable, the 'id' column is unecessarily redundant and superfluous, if you get what I mean.

> Seeing that a row in service_plan has id=36 doesn't tell me much about
> the service_plan, but if I see a row with type='2 year plan', that
> tells me something about that service_plan.
> That is really why I wanted to have that 'type' in addition to the
> database-geenrated 'id'.

The question is: Why do you want the 'id' key in addition to the simple, familiar 'type' key that the users will recognize and use? The introduction of 'id' simply means users will always have to join in the service_plan table whenever they want to restrict a referencing table by 'type'.

> This this is really a bad design, which of these options would you
> recommend?
>
> 1. remove db-generated 'id', and make 'type' the PK instead

Yes, this might make more sense.

> 2. remove 'type' column, an point FKs in other tables to 'id' instead
> of 'type'

No, this won't make sense in any imaginable scenario.

> Which design is considered better, and are there more options?

If 'type' is unstable, the design you have might make sense. Does the 'type' of a service_plan change frequently? Does it change ever? If 'type' never changes or very seldom changes, 'type' makes an excellent primary key. If users frequently change the 'type' of a service_plan, introducing an 'id' column will introduce a stable key, and then the decision whether to introduce 'id' becomes a design tradeoff between familiarity and stability. Received on Wed Apr 09 2003 - 18:37:55 CEST