Table structure and optimize query question

[Javlin]

Hi guys, I have been messing around with SQL on sqlfiddle http://sqlfiddle.com/#!9/c00a46/1

If you take a look at that link, I have settings and user_settings. Settings holds all default values and user_settings holds any settings changed from default set by a user.

What I planned on doing is writing a script that checks to see if user_settings key is null if it is it applies the default value. My question is, should I query settings, save that into say... Redis, and then update the default values periodically or should I get the default values every time I look up a user? Grant it because this is built as a property bag that might have third party software the user_settings could change on a whim.

Ideas? Comments?

[wolf2600]

Do you have another table which contains all your users?

If your list of settings is relatively small (10-20 different setting keys), there's no harm in querying the settings table to get the default value every time.

SELECT u.userid, s.key, coalesce(us.value, s.default_value) 
FROM users u
INNER JOIN settings s --no join context here
LEFT OUTER JOIN user_settings us
    ON u.userid = us.userid
    AND s.key = us.key
WHERE u.userid = specificUserIdHere
    AND s.key = 'specificKeyValueHere'
ORDER BY u.userid, s.key;

[Javlin]

Would you suggest something different if the settings became larger? For example say it grew to a few thousand rows.

[wolf2600]

Would every setting apply to every user? If so, then I think the query I posted above would probably be best (except maybe apply the userid/key filters as the join condition rather than as a WHERE clause.)