This question was meant to be a follow-on to
http://forums.mysql.com/read.php?20,401800,403426#REPLY, which was closed, but perhaps the question deserves its own new topic.
Consider the table named `apples`, organized as rows of apple types, with attributes for each:
1. the
weight of the apple on earth, in ounces
2. the
luster or reflectance of the fruit, scale 0.0 to 1.0
3. the apple type's
redness, as an integer RGB value
4. the
sugar in the apple, as a percent
+--------------+--------+--------+----------+----------+
| type | weight | luster | redness | sugar %1 |
+--------------+--------+--------+----------+----------+
| GrannySmith | 4.5 | 0.1 | 0x6cfe50 | 12.25 |
| RedDelicious | 5.2 | 0.6 | 0xdd1601 | 14.94 |
| GoldenDelish | 6.5 | 0.3 | 0xfefb01 | 14.39 |
+--------------+--------+--------+----------+----------+
A site visitor can select an apple by its attributes. Some attributes are more important than others for each visitor, so he can weight any attribute according to its importance to him.
There are three visitors to my site. Each asks my app to select an apple for the use he has in mind. The app will return a list of apple types ordered by the suitability of the apple type for this visitor.
Alan,a poor, tired grad student, wants something filling and energizing.
Bruce is an interior decorator putting together a colorful Thanksgiving-dinner centerpiece; he prizes apples of luster and color.
Chaz is a hiker who wants a sweet apple that's not too heavy.
Each of these guys assigns an importance rank of 1 through 5 to each attribute:
+-------------------------------------+
| importance of apple attributes |
+-------+--------+--------+---------+---------+
| name | weight | luster | redness | sugar % |
+-------+--------+--------+---------+---------+
| Alan | 5 | 0 | 0 | 5 |
| Bruce | 1 | 5 | 3 | 0 |
| Chaz | 5 | 2 | 2 | 4 |
+-------+--------+--------+---------+---------+
The question, finally: How can I construct a query to return to each visitor the "best" apple for him?
As I see it, I have to (1) somehow normalize all the attributes across rows; (2) weight those normalized values according to the visitor's preferences; and (3) select apples ORDERed BY the sum of the weighted values.
How much of this calculation is MySQL willing to do?
Thanks!
1 Brittany B.,
Effect of Maturity level on Malic Acid, Brix,and Starch Levels in Various Apples, Science Project 2004, (
http://www.selah.k12.wa.us/soar/sciproj2004/brittanyb.html ), table (
http://www.selah.k12.wa.us/soar/sciproj2004/Graphs/BrittanyB.pdf ).
Edited 1 time(s). Last edit at 05/18/2011 03:09PM by Pete Wilson.