Re: FK -> non PK - bad design?

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, and because an Oracle DBA once told me it is always good to have a db-generated id/PK in each table.
The unique and not-null 'type' column I wanted because its value would be a human-friendy, human-assigned meaningful name. 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'.

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
2. remove 'type' column, an point FKs in other tables to 'id' instead of 'type'

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

Thank you. Received on Wed Apr 09 2003 - 09:41:08 CDT