Solved

Database Design with Foreign Keys and Compound Keys

Posted on 2014-01-06
4
506 Views
Last Modified: 2014-05-21
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.
0
Comment
Question by:kindaprog
4 Comments
 
LVL 17

Accepted Solution

by:
sweetfa2 earned 250 total points
ID: 39761248
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.
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 250 total points
ID: 39773206
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.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now