How to store configs?

Question

I am creating a modular system where each module is separated from others. I want to store the module configuration data in database, but I don't know best structure for this.

I have some variants:

1st:

key_____|______value (primary key is `key`)
option1 | 1
option2 | abcd
option3 | bla-bla-bla

I can easy get settings, but if I want to update some settings I'll need to build hard (?) SQL query with CASE:

UPDATE settings SET value = CASE
WHEN key = option1 THEN 1;
WHEN key = option2 THEN abcd;
…
END
WHERE key IN (option1, option2, …)

If there is a better variant for update many rows in one query (I'll use MySQLi and InnoDB only) this will be great.

2nd:

module_____|_____settings
news       | a:3:{s:7:"option1";i:1;s:7:"option2";s:4:"abcd";s:7:"option3";s:11:"bla-bla-bla";}

This way, I'll serialize an array with settings and put it in database. I want to cache unserialized data in .php files. But if I'll have large settings array, my serialized string will be very very big.

3rd:

option1 | option2 | option3
   1    |   abcd  |bla-bla-bla

I'll have one column for one option key, but I think that create one table to store one row is unnecessarily.

---

Which variant is the best? Can you suggest your own methods to store configs? Thank you!

Answer 1

If you're set on storing configuration data in the database, I would always go with option number one.

It allows you to easily add and update configuration values without affecting too many other pieces of the system. You're just slightly over thinking the complexity of updating values. Rather than try to wrap everything in a single update, write separate updates for each key:

update settings set value = 1 where key = option1;
update settings set value = 'abcd' where key = option2;

Option two might be a close second, but I really hate storing one large string of serialized data in the database. If that's the way you're going to go, considering just storing it in a configuration file instead.

I've worked on projects that have used the third option as well and it is by far my least favorite. Having to add/remove columns any time you add/remove a configuration value is a royal pain.