Storing user settings in table - how?

Question

I have settings for the user about 200 settings, these include notice settings and tracking settings from user activities on objects. The problem is how to store it in the DB? Should each setting be a row or a column? If colunm then table will have 200 colunms. If row then about 3 colunms but 200 rows per user x even 10 million users = not good.

So how else can i store all these settings? NOTE: these settings are a mix of text entry and FK lookups to other tables.

Thanks.

Answer 1

Serializing the data almost always turns out to be a bad idea, because in doing so you cripple the dbms. All of the man years that went into producing an efficient dbms will be wasted on a serialized bucket of bits.

If you have application logic hooked up against each setting, I think you should implement it as either:

1 column per setting in the settings table. This makes it easier to leverage the power of your dbms, with constraint checking, referential integrity, correct data type for your values, plenty of information to the optimizer. The downside is that row size grows.

or

1 table per setting (or group of related settings). This has all of the benefits of the above, but trades rowsize for a performance penalty when you need to fetch most or all of the settings at once. When settings are optional, this alternative will be significantly smaller if the actual data is sparse.

Also, lots of columns is often a "smell", that suggests you haven't normalized your data correctly, but it doesn't have to be that way. Only you know your data.