[thelist] normalizing an abnormal table

[rudy]

>Promotion_Items
>- Promotion_Item_ID (PK, int, identity)
>- Promotion_ID (FK, int)
>- Product_ID (FK, int)
>- Price_Domestic (money) *
>- Price_Domestic_Premier (money) *
>- Price_Intl (money) *
>- Price_Intl_Premier (money) *
>- Rate_Code_Domestic (varchar 10) *
>- Rate_Code_Domestic_Premier (varchar 10) *
>- Rate_Code_Intl (varchar 10) *
>- Rate_Code_Intl_Premier (varchar 10) *
>- Promotion_Item_Insert_Date (datetime)

hi chris

this design might make some people sit up and holler "repeating group"

but not me  ;o)

> not every promotion item will fill all of these *'ed fields.

<sigh /> ...   nulls for the fields that aren't used, right?

> What I don't want to have happen is for a promotion item's
> pricing to end up in the premier fields when in fact it's not a
> premier promotion, and therefore should have the non-premier
> pricing/rate codes accordingly.

then how about not having separate fields?

> I have a "Promotions" table which has a field called "Is_Premier"

this is just what you need!

just add that field to Promotion_Items table as a FK

in fact, you don't even need to do that, but for the sake of argument,
assume there are some queries that would not otherwise need to join to the
Promotions table, so having the Is_Premier field in the Promotions_Items
table will save the join to Promotions to get it

> I want this to be a pseudo-constraint within the database as well.

as constraints go, there's nothing pseudo about foreign keys   ;o)


by the way, you do not need Promotion_Item_ID to be the PK for this table

unless you allow the same product to be in the same promotion more than
once (unlikely, i should think), or unless you have child tables that you
want to hang off the Promotion_Items table, then you could ditch
Promotion_Item_ID and make Promotion_ID and Product_ID a composite PK

rudy