Development Flexible custom user settings... DB structure

So I'm on to the user management part of my little project, and before I commit to any type of DB structure, I want to make sure I'm not doing anything too crazy - so this is really just a sanity check.

Code:

Users table:
------------
id (int, pk, ai)
username (tinytext)
password (char40) [one-way hashed, obviously]
salt (char5)
csrf (char10)

CustomUserSettingsConfig table:
-------------------------------
id (int, pk, ai)
name (VC40)

CustomUserSettings table:
-------------------------
user_id (int, fk)
custom_user_setting_config_id (int, fk)
value (tinytext)

Or something along those lines. The users table will keep a bare minimum set of information - enough to log the user in, set/check a secure cookie, and throw a hidden input field on pages for cross-site request forgery prevention (csrf field). Custom user settings would be built out separately in the config table - just a list. The values would be joined with the two (something like SELECT CustomUserSettingsConfig.name, CustomUserSettings.value FROM CustomUserSettings JOIN CustomUserSettingsConfig ON CustomUserSettings.custom_user_setting_config_id = CustomUserSettingsConfig.id, dunno) and then sit alongside the User object in a UserDetails object. Both have aggressive caching so the join performance on the DB isn't too much of an issue - they'll only ever get read when values change in the DB.

Obviously names are subject to change, but for now I'm keeping them as verbose as possible to clarify my idea.

Anyways, I think this is a bit better than trying to guess what fields I'll need at any given time and put them straight on to the user table, since it could range from common stuff like email and real name to vBulletin/phpBB userID to flickr username, whatever.

So is this approach crazy and foolish, or does it seem like a decent way to go about the task? Any feedback or alternate ideas are fully welcomed here - I've got my User object working off of sample local data right now which is going fine but have no tables commited for anything yet, and haven't even started the code for the custom user properties object.

Thanks!

 

Sounds like an idea, firehed.
I've used a similar setup for user permissions on one of our backends. It works well, and overheads aren't bad if you're running proper indexing.
The upside of the idea is that it's flexible and extendable.
The downside is that your queries can take a bit to execute if you have lots of users and settings. Hence me recommending proper indexing (in effect on the userId part and then probably on the setting ID). I'm sure you are aware of that, though.

IMHO, the idea is a lot better than adding columns to the table at a later stage, even if you default their values, have explicit inserts and all of that...

Not sure about the CSRF field though... it is still pretty easy to forge, what with people being able to see it when they "View source" the page... unless of course you were to AJAX the value in there, which would (in my experience) not show the value when the user clicks "view source", but would still work if you're requesting that on postback... provided the AJAX request finished by that time.

 

The idea behind CSRF is really just prevention for ALL users, not a specific one. Yes, if you're able to snoop network traffic then you could rig up a forged request against a specific user, but as the token is different for every user, it would be extremely difficult to forge a request from an arbitrary user. Combined with only allowing POST requests and checking the HTTP_REFERER, I can have things as safe as is reasonably possible, provided the database as a whole doesn't get leaked out (and if that happens, I've got bigger problems). My primary concern would be the <img src="http://www.example.com/victim/some_action.php?var=value" /> kind of CSRF attack, not a user trying to hack together their own sort of API. Sending the value via AJAX severely reduces fallback accessibility (which is a big no-no), and if someone's looking for it could easily view the "live" HTML in Firebug.

I'll double-check the indexing if I go this route. The two id primary keys automatically have indexing set up - would it be best to add an index on the foreign keys in the values table? My understanding of indexes is fairly weak compared to the rest of my MySQL knowledge... I've got a decent handle on it, but it's never configured in a way that seems very logical. Given the way I have caching set up it's almost certainly a non-issue, but obviously I want to do it right regardless.

Thanks for the feedback

 

your security plan sounds good, man. And you're right. The moment someone sets out to break your site with custom code, you have real problems anyway. And yes, AJAX does reduce fallback ability.

Indexing is a tricky subject. I'm not too fluent on it myself.
To the best of my understanding, i'd guess your primary (i.e. most run query) would be for retrieving user info, which would mean selecting all the properties according to the userID (talking about the mapping table CustomUserSettings), and then getting the values for each setting.
From that point of view, i'd say index the userId column (it's going to make a difference once you gain lots of users, even though the difference would not be as big as when indexing a PK, simply because a PK is unique, and a FK can be repeated).
Simply because that's what you'll be querying on.
Essentially (without the joins and from a VERY basic perspective), you'd Check the username, thereby having his/her ID. you then use that to get all the settings coupled to that ID (hence my indexing recommendation), and then you select the name for the setting for every setting retrieved from the previous query. This means you're querying three tables, each on a index, as userID is indexed on the users table, and on the CustomUserSettings table. Then you have the "where" sorted for the next query, and ID is indexed for that as well...

Please note that i'm not exactly a DBA, so my theory might be flawed. But this SHOULD make a difference once your tables gain some rows (i.e. a couple of thousand or so). NB (again, i'm sure you're aware of this), that indexes DO use more space on the server, so if DB space is a limitation, i'd try to go at it without the added index, and only activate that once it's needed. For most low user counts, you won't notice the difference anyway.