So I ve got a users table; storing things like username, email, etc. I then have a user_settings table. Instead of standard columns like timestamp_logged_in, profile_views, etc., I have it organized as key/value combo. For example, the columns are:
user_settings_ID user_ID name value
A sample record would be: 82 2 timestamp_logged_in 2009-10-10 02:23:53
This makes the settings a little more permeable. I don t need to have a column exist to store new user setting types. I m concerned that doing this (which will store every value as a string, and I ll cast it to the appropriate type in the middle ware) will make it difficult to sort. So for example if I wanted to get a list of the users in order of last logged in, sorting would require an sql call such as:
SELECT user_ID FROM user.settings WHERE status= open AND name= timestamp_logged_in ORDER BY name
This would be sorting the found records as strings, however. Is there a way to make sure the sorting algorithm mysql is implemented based on a certain value type (eg. forcing it to treat the sort column as a string, integer, datetime, timestamp, etc.)?
Any thoughts would really be appreciated. Thanks. Oliver Nassar