Design Question
Hello,
I have a table design question relating to best practice and performance.
Monthy is somewhat of a SQL guru and so may be many others on this list,
this may actually help a lot of folks out there:
Example:
I want to get a listing of all applicable rates for a delivery:
a rate can be defined in three ways:
a. the entire state
b. for a certain area code
c. for a specific zip
to make things worth ideally I want to be able to say
carrier1:
all of state="CA" is $2,
but areacode="714" is $1
and zip="92649" is $.50
carrier2:
but areacode="310" is $1
and zip="92647" is $.50
and zip="92648" is $.60
and zip="92649" is $.70
the tables look like this right now
city(zip varchar(10),areacode varchar(10),state varchar(2),name varchar(30))
rate(zip varchar(10),areacode varchar(10),state varchar(2),carrier
varchar(5),decimal(10,2))
Data
rate('92647','','','c1',.5)
rate('','714','','c1',1)
rate('','','CA','c1',2)
rate('92647','','','c2',.5)
rate('92648','','','c2',.5)
rate('92649','','','c2',.5)
rate('','310','','c2',1)
if I now query for all rates in CA it would select * from city where
city.state="CA"
but what's the most elegant way to do the join to get it back as one
resultSet ?
I was thinking of maybe using the same logic as for the permission system
inside of MySql with % or something as wildcards but left it empty sting for
now. Which also leads me to another question as I would like to setup
permissions for various users to be able to maintain only certain rates.
This can be done on the application level. I am wondering if I need to OR
should create a query in the application doing the various or conditions or
if there is a more elegant solution.
Note: I left out the NOT NULL and the index definition.
Thanks SO MUCH
Martin Hubert
Freightgate - New Dimensions in e-Logistics (sm)
Visit us at http://www.freightgate.com
Email: [EMAIL PROTECTED]
Phone: +714.799.2833 Fax: +714.799.0100
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
