[thelist] normalizing an abnormal table

[Chris Blessing]

I have an interesting normalization issue, but I'm thinking it's probably
been seen before.

Basically the problem table looks like this:

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)

The fields marked with a * are the fields in question here.  The problem is
that each row in the Promotion_Items table represents a product in a
promotion we'd be running.  So for example, I might have Product X and
Product Y under promotion "Sale Items".  Now here's where it gets tricky:
not every promotion item will fill all of these *'ed fields.  An item may
not be premier, so in that case it wouldn't have a premier domestic and
international price, and it wouldn't have any premier rate codes as well.

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.  I
have a "Promotions" table which has a field called "Is_Premier" which is a
bit for whether or not to retreive premier or non-premier pricing/rate codes
from this Promotion_Items table.  Of course it's not a problem to
retreive/enter/maintain the proper data per that bit flag, but being the
ever-striving-for-perfection person that I am, I want this to be a
pseudo-constraint within the database as well.

So this leads me to the heart of the matter: how can I normalize such a
table so that the appropriate rate codes/prices get
inserted/updated/selected properly based on this higher-level "Is_Premier"
flag?  Is it even possible?  Is it worth the work?

I realize this is kinda wordy, my apologies.  It's a lot to try to explain.
:)  Thanks in advance all,

-Chris

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