For simplicity's sake, I have a mySQL table like this:
Right now, if I run a select statement, my only options for "order by" are by flavor or color. What I really need to be able to do is order by favorite (nothing to do with alphabetical order or insertion order).
So, I will add a "favorite" column whose values will be 1, 2, 3 based on my favorite flavor. However, depending on the day and my mood, my favorite can change. So if yesterday my favorite was Vanilla and today my favorite is chocolate, I'd like to be able to update the favorite column for chocolate to 1 and then set all other favorite values = favorite + 1.
Is the best way to accomplish this to create a trigger on insert or update to the favorite column?
Should the favorite column be auto increment, or is that not necessary since I will have the trigger update the rest of the values as favorite + 1?
Thanks for your help.