Solved

Database Design with Foreign Keys and Compound Keys

Posted on 2014-01-06
4
515 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

808 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