0

Our application has many configuration directives. Things like "are multiple languages enabled?" or "which CSS template should be used?" or "how many items should be shown per page?".

We need to store the configuration directives in DB. Currently we have table for each "area" of configuration and the table has only one row containing the values in columns. This approach allows us to put constraints on the values - let it be ENUM, INT, VARCHAR, etc. But it's huge PITA when we need to add new config directive -- we need to update and redeploy DB schema. It also pollutes the table space with many setup_something tables.

My idea was to replace the tables with MySQL based key-value store (we use shared hosting with little configuration options, so we cannot use NoSQL). Some of the values MAY depend on locale or other variables, but I think that it can be solved by something like fallback (find key begining with "lang:fr" and if not found try one begining with "lang:default" (which will be always ready).

The constraints would be checked only in PHP using concrete setup classes, but there will be no DB checks.

  1. Are there any ready-made solutions for this (prefferably working well with Zend Framework).
  2. What are the pitfals of this solution (except the obvious - that I can't fetch the whole "row" at once or many rows at once - which is not a problem)
  3. [Have you used|Would you use] similar solution or something completely different?
Improve this question
4
  • Why not use one table with key/value pairs? If it's per-user configurations, you can always use user/key/value as the table data. Commented Aug 1, 2012 at 14:39
  • 1
    If you don't need to write/change the parameters from the web app (e.g. the admin interface), I would go with something in the text files. Like yaml or ini style configuration. No problem with existing solutions/php libs to handle this. Commented Aug 1, 2012 at 14:40
  • That's my first idea. But I want to crowdsource the experience first :) There may be some unforeseen pitfalls ;) Commented Aug 1, 2012 at 14:41
  • bcelary: We DO need :( Some of the items can be updated from admin interface. Commented Aug 1, 2012 at 14:42

3 Answers 3

Reset to default
3

You could simply have one MySQL table with all your config settings. Perhaps something like this:

config_id (INT autoincrement)
config_group (VARCHAR) this gives you something to group your configuration setting with
config_key (VARCHAR) this would be the key name by which you would reference the value
data_type (VARCHAR) you can use this to tell your application how you want to treat this data within the application (i.e. treat it as integer, float, string, etc.)
value (VARCHAR) the value

Of course you would then need to take the string value and manipulates it in your code to treat it like an integer, float, string, etc.

Maybe note the cleanest way to do things, but have seen this approach taken a number of times.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

The Wordpress system (which uses MySQL, of course) has a wp_options table. It contains these columns:

 option_id    (autoincrement pk)
 blog_id      (parameter grouping number)
 option_name  (text of option name)
 option_value (text of option value)
 autoload     ('yes' or 'no')

The option_value can either be a simple number (0, 1, 42) or a text string as needed. Some option_value items are parseable JSON-like serialized parameter sets.

The autoload column allows your system to skip little-used settings if need be.

This works quite well and offers a lot of flexibility.

Improve this answer

Comments

0

Store your key/values in a php array, serialize it then store the resulting string in a text column in a database table. Lots of flexibility and easy to implement. Only real down side is that you can't query on individual attributes.

Improve this answer

1 Comment

This is not an option. The options need to be readable from SQL.

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.