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 all of state="CA" is
$2, but areacode="714" is $1
and zip="92649" is $.50
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),decimal(10,2))
Data
rate('92647','','',.5)
rate('','714','',1)
rate('','','CA',2)
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.
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
