On the future of user preferences

[stwalkerster]

There's a few issues (#674, #670, #594) at the moment which talk about adding user preferences for various things.

Continually adding user preferences makes the user table quite wide, and relies on us changing the database schema for every single user preference we want to add. This is not something I'm strictly opposed to, but it does make the process of adding new user preferences harder.

Currently, the table looks like this:

column	type	nullable	options	comment
id	int	no	PK AI
username	varchar	no	UQ
email	varchar	no	UQ
status	varchar	no	default='New'
onwikiname	varchar	yes
welcome_sig	varchar	yes		Seemingly unused
lastactive	datetime	no	default='0000-00-00 00:00:00'
forcelogout	int	no	default=0
forceidentified	int	yes
welcome_template	int	yes	FK=welcometemplate.id	The welcome template chosen for use
abortpref	tinyint	no	default=0	"Skip double-check prompt before closing requests"
confirmationdiff	int	no	default=0
emailsig	blob	yes
updateversion	int	no	default=0
creationmode	int	no	default=0	Manual vs OAuth creation
skin	varchar	no	default='main'

My proposal is that we split out all user preferences that don't matter for indexing/ACL into another table, keyed on (user id, preference name), thus converting the problem of user preferences into a data problem, not a schema problem. The proposed table structure would look something like this:

column	type	nullable	options
id	int	no	PK AI
username	varchar	no	UQ
email	varchar	no	UQ
status	varchar	no	default='New'
onwikiname	varchar	yes
lastactive	datetime	no	default='0000-00-00 00:00:00'
forcelogout	int	no	default=0
forceidentified	int	yes
confirmationdiff	int	no	default=0
updateversion	int	no	default=0

column	type	nullable	options
id	int	no	PK AI
user	int	no	FK=user.id
preference	varchar	no
value	varchar	yes
updateversion	int	no	default=0

In theory, this makes retrieving specific preferences require another database round-trip, but also has leaner responses to preference requests, as well as the aforementioned benefit of no schema changes for preferences.

Opinions?