Link to home
Start Free TrialLog in
Avatar of kindaprog
kindaprog

asked on

Database Design with Foreign Keys and Compound Keys

I'm having trouble coming up with database model for this data-set... What would the normalization of it be?

E.g.

Table Name:
MARKETING_OFFERS
    OFFER_ID               PK
    PRICING_ID    FK
    ... other columns...

Table Name: PRICING_GROUPS
    PRICING_ID                  PK
    PRICING_SEGMENT       PK
    PERCENT_OFF
    ... other columns...

Example Data:

OFFER_ID        PRICING_ID
1                      1
2                      1
3                      2


PRICING_ID    PRICING_SEGMENT    PERCENT_OFF
1                     A                                10
1                     B                                30
1                     C                                50
2                     A                                25
2                     B                                35


The idea would be that we can have a marketing offer that is assigned a pricing group. Then when we collect information from the customer that allows us to use logic to determine what pricing segment we want to give them within that pricing group.

However, I cannot setup a foreign key without including the segment in the marketing offer table.

I'm hoping this can be solved a way I have not thought of yet...

My idea thus far:

Put default segment in the offer table. Always must have an A at least in pricing groups to create a new offer.

Any help to make a DB design that works with this concept is greatly appreciated!

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of sweetfa2
sweetfa2
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Franck Pachot
Franck Pachot
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial