Take the 2-minute tour ×
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.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  Xkeeper Nov 17 '11 at 17:19
add comment

If I were you I would do a table Options with 3 columns:

  • Key
  • KeyTypeId
  • Value VARCHAR(MAX)

You then create a table KeyTypes with

  • KeyTypeId
  • Name

(Your types could be "number", "string", ...)

Then, in PHP, you know how to parse your data.

This allows you to easily have the possiblity to add and a new parameter, or even a parameter type.

If you want to use per-user option, add a UserId column in the Options table.

Frankly for 20 options, I don't think performance will be too much of an issue.

share|improve this answer
    
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. –  Xkeeper Nov 17 '11 at 17:22
    
Also, is there any reason for using VARCHAR instead of TEXT? –  Xkeeper Nov 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. –  SRKX Nov 17 '11 at 18:42
add comment

Personally I would go with BOTH options.

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.

I know this has already been answered, but hope that helps...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.