Solved

Database Design with Foreign Keys and Compound Keys

Posted on 2014-01-06
4
527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
subtr returning incorrect value 8 71
MySQL Finding Duplicates in a Normalized Database 6 31
Why is my $_POST not going to results page 10 42
Append Query Access 2010 4 28
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

696 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