Re: FK -> non PK - bad design?

Originally posted by Otisusenet
> lennart_at_kommunicera.umea.se (Lennart Jonsson) wrote in message
> news:news:...
> 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.
If TYPE is a sensible business key that is not likely to get updated, then it is good as a primary key. Generated IDs are used in situations where there isn't a good business key, or there is but it is likely to get updated, or comprises several columns and is therefore unwieldy.

You seem to have the worst case - a generated ID as the PK, AND a business key TYPE that is used as the foreign key in other tables, making ID redundant.

Some people HATE generated keys and never use them, others (like your DBA) LOVE them and always use them whether they are needed or not, and the rest decide which to use on a case-by-case basis.

Whichever way you go, you should not remove the TYPE column from the SERVICE_PLAN table, otherwise you will lose information.

--
Posted via http://dbforums.com