Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm planning on adding storage for configuration options to the website I'm working on (php + MySQL).

The problem is that I cannot decide between having a 2-column table (with key and value fields) that contains multiple rows, one for each option; or an n-column table with one row.

It seems like using the single row format is superior, because each field can have the proper data type (INT, VARCHAR, etc.) and would make fetching all configuration values easier (single-row), but adding a new option would mean a new field every time.

At the same time, though, it seems like having a key-value table would possibly be a performance issue since you'd have to fetch all rows to get the full configuration, as well as updating having to find and seek the proper row to update; you also don't get actual typing (and would have to rely on VARCHAR or TEXT only).

I'm not planning on having a lot of configuration options (maybe 20 at the most, more likely 5-10), so I'm not sure if key-value or multi-field is the way to go, here.

3 Answers
3

I did this with a project recently, only I had even fewer settings to store. I opted for using a two column table, with each setting being it's own row.

I think there are pluses and minuses to each, but if you're dealing with such a small set of configuration options, there is not going to be a huge performance hit. To put into perspective, phpBB's config table has almost 270 rows of config options.

Yeah. It wouldn't even really be a choice between them if there were a lot of potential options. The distinct lack of configuration settings is what makes me wonder... I guess I should just go with the key-value store, even if it would be less complex than a single row.
–
XkeeperNov 17 '11 at 17:19

A single row many column table to hold the majority of the "common to all records" type of data.

And a second table which has a foreign key field to the first table, and then two additional fields as key-value pairs.

I've used this before, and it combines the best of both worlds, allowing for the flexible storage of the key-value, and the performance of the single row.

You can even promote one of the key-value pairs into an additional field within the common table, and then populate it from the data in the key-value table before removing the set of key-value data once an option becomes so common amongst your user base that it makes more sense to have it within the single row table.

This really seems like overkill for a small amount of options, and handling types in PHP is only useful if you have a specific thing that writes to a config value and cross-checks the proper typing and everything.
–
XkeeperNov 17 '11 at 17:22

Also, is there any reason for using VARCHAR instead of TEXT?
–
XkeeperNov 17 '11 at 17:24

@Xkeeper : TEXT requires much more disk space I think, and I'm not sure you'll need more that 255 bits for your use. This is not an overkill, it provides you with enough flexibility to enhance your system later if need. It is a good pattern for scalability and proved useful for me.
–
SRKXNov 17 '11 at 18:42