Solved

Database Design with Foreign Keys and Compound Keys

Posted on 2014-01-06
4
491 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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 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

10 Experts available now in Live!

Get 1:1 Help Now