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.
kindaprogAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sweetfa2Commented:
In your pricing groups table create a separate unique identifier field using a GUID.

Use that as the foreign key on your marketing offers table.

Add an additional unique key on the pricing id,/pricing segment fields of the pricing group table.

There is nothing to stop you just adding the pricing id and pricing segment to the marketing table using a composite foreign key, but generally it is more efficient to use a single field as a foreign key reference.

If your intent is to always use the segment field in queries then it would be better to move it into the header table and suffer the additional space wastage, but normalisation rules would say leave it where it is.

Your default segment would more logically sit in the marketing offer tables as it would seem to more logically relate to a default offer whereas putting it in the pricing segment table restricts the use of defaults to one behaviour.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Franck PachotOracle DBACommented:
Hi,

Yes, you can't have a compound foreign key where one column is null and the other not. You can but you're going into trouble...

I think you need another table 'pricings' with primary key PRICING_ID only.
PRICING_GROUPS will have a foreign key to that 'pricings' table.
MARKETING_OFFERS will have two foreign keys:
 PRICING_ID referencing that 'pricings' table
 and  (PRICING_ID,PRICING_SEGMENT) referencing MARKETING_OFFERS - that one being nullable if you want to be able to set a pricing

Regards,
Franck.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.