Table for Storing Site Prefs

l008com

I'm making a table in my DB to store site preferences. Basic stuff like how many items to display on different pages etc. Uses that are all getting hard-coded into my site, but will be easy to change with a web form.

I can't decide what kind of table to use. I could go with the three column, ID, KEY, Value table, where each preference has it's own row in the table. Or I could go with the one row table, where each preference has it's own column.

Sure, in the screen shots, the one-row table looks "messy". But I'm not going to be displaying it's contents so that really doesn't matter. And it will take a bit less code to work with, using the one-row option. The multi-row table "looks" more "right", but is it worth it in this particular case?

Multi-Row

One-Row

dagon

i use one row then you can have multiple field types

l008com

dagon;10969064 wrote:
i use one row then you can have multiple field types

Hmm yup, that's a good point in favor of one row-many columns.

bradgrafelman

On the contrary, I'd go with the three-column approach since adding new preferences wouldn't require a DB schema change. In fact, since this data has little to no value to the DB itself, you could even serialize it and store any type of PHP variable you want (integer, string, n-dimensional array, etc.).

l008com

It really doesn't seem any easier or harder adding columns vs adding rows. But here's an important question, is there a max number of rows per columns? That could be the best reason to use a multi-row table.

dagon

4096, your doing something odd, if you hit that limit.

l008com

Hah, yes I agree, that should be WAY more than enough. If the limit was like... 16, I'd go with the multi-row table instead.

dagon

l008com;10969075 wrote:
Hah, yes I agree, that should be WAY more than enough. If the limit was like... 16, I'd go with the multi-row table instead.

16! i have 'normal' tables with over a thousand

l008com

nice

bradgrafelman

l008com;10969073 wrote:
It really doesn't seem any easier or harder adding columns vs adding rows.

Are you talking about manually or automatically, e.g. via a script?

My preference would be the rows method since you could add another field to your update form and the new row would automatically be created (e.g. via the use of the same "INSERT .. ON DUPLICATE KEY UPDATE" query you'd use for existing preferences).

While I suppose you could do some error checking and craft an ALTER TABLE statement to handle new fields, I always shudder at the thought of scripts that ALTER tables. In fact, the credentials that I configure web apps with don't even have the privileges to do so. :p

l008com

I'm talking about manually. All these prefs are going to be hard coded into various pages of my site. There will be no mechanism for easily adding new pref values via script... there's no point, since I would still have to build the system to use that pref, from scratch. You get what I'm saying?

bradgrafelman

l008com;10969080 wrote:
You get what I'm saying?

Aye; to each his own, I suppose. I'm simply lazy and like to avoid duplicate work when I can.