Using null to mean all or any
Posted by: Jim Haberlin
Date: September 30, 2009 04:33PM

I am new to MySQL and I am trying to determine how to handle a situation where I have a foreign key but I want to have a hierarchy and have a meaning of ALL or ANY.

These easiest way to describe it is with an example.

I have a customer table with many customers.
I have a rate table with many rates.
I want to set up a rate of $100.00 that applies to all of my customers.
I then need a rate of $90.00 that applies to only one customer, Customer A.
If I am searching for the correct rate for Customer A I should find the row with the $90.00 rate.
If I search for a rate for any other customer I should find the rate of $100.00.

My instinct is to have a customer foreign key on the rate table that would have Customer A's key on the $90.00 rate and a null value on the $100.00 rate.
The query would be: Select amount from Rate where rate.customer is "Customer A" or rate.customer is null.

This works but I am concerned with efficiency and with having correct database design. I am not arguing in favor of this solution I am merely trying to find out if it is a valid design or if there are other solutions for this scenario.

I would appreciate any suggestions.

Options: ReplyQuote

Written By
Using null to mean all or any
September 30, 2009 04:33PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.