MySQL Forums :: Database Design & Data Modelling :: Using null to mean all or any


Advanced Search

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


Subject Written By Posted
Using null to mean all or any Jim Haberlin 09/30/2009 04:33PM
Re: Using null to mean all or any Rick James 10/01/2009 07:30PM
Re: Using null to mean all or any Jim Haberlin 10/02/2009 07:35AM
Re: Using null to mean all or any Rick James 10/02/2009 09:43AM
Re: Using null to mean all or any Jim Haberlin 10/02/2009 11:28AM
Re: Using null to mean all or any Al Lombardo 10/02/2009 01:57PM
Re: Using null to mean all or any Jim Haberlin 10/02/2009 02:31PM
Re: Using null to mean all or any Al Lombardo 10/02/2009 02:48PM


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.