[thelist] normalizing an abnormal table

[Chris Blessing]

rudy you are the man. :D  And Rebecca, thanks for your suggestion also.  I
think rudy nailed it down very well.

I love the idea about the composite primary key, WHY DIDN'T I THINK OF THAT!
:)  I will be reassessing the whole db design because of that thought,
thanks.

And hats off, as usual.  Always some good insight.

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> >just add that field to Promotion_Items table as a FK
>
> i think i should clarify
>
> this has to be in conjuction with Promotion_ID
>
> in other words, Promotion_ID *and* Is_Premier *together* have to be a
> foreign key to the Promotions table
>
> that way, the database ensures that premier promotion items are
> linked only
> to premier promotions
>
> now, theory says that FKs can only reference PKs, so this would dictate
> that the pair of columns should be the PK of the Promotions table
>
> that may be problematic, in that you could then have both promotion 1234
> ordinary and promotion 1234 premier, but if the promotion id is
> an identity
> or autonumber, this should not come up, and in any case, you can
> get around
> it if you want
>
> some databases will let you declare a foreign key on any column(s) of
> another table, not just the pk, provided it/they are unique, so
> if you have
> this option, use it
>
> in any case, the bottom line is this:   let the database handle the
> structure, and don't sully your code with any of the constraint details
>
> rudy