3

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.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
1
  • 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. Commented Nov 17, 2011 at 17:19
2

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...

Improve this answer
0

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.

Improve this answer
3
  • 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. Commented Nov 17, 2011 at 17:22
  • Also, is there any reason for using VARCHAR instead of TEXT? Commented Nov 17, 2011 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. Commented Nov 17, 2011 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.