How to store site wide settings in a database?

Question

I'm debating three different approaches to to storing sitewide settings for a web application.

A key/value pair lookup table, each key represents a setting.

• Pros Simple to implement
• Cons No Constraints on the individual settings

A single row settings table.

• Pros Per setting defaults and constraints
• Cons - Lots of settings would mean lots of columns. Not sure if Postgres would have an issue with that

Just hard code it since the settings won't change that often.

• Pros Easy to setup and add more settings.
• Cons Much harder to change

Thoughts on which way to go?

Answer 1

Go with #1. If you want constraints based on simple types, then rather than having a simple string as a value, add a date and number field as well. The individual properties will "know" what value they want. No reason to get all meta about it.

Answer 2

If I had to choose, I'd go with the first option. It is easy to add/remove rows as you need. Whereas the single row could end up being a nightmare, and is probably a lot less scalable. And for option 3: It's possible you will regret hard coding your settings in the future, so you definitely don't want to box yourself in.

Although you didn't list is as an option, is XML available? It is easy to set up, and gives you slightly more options, as you can nest settings within settings.

Answer 3

I've used a key/value pair lookup table much in the way you describe with good results.

---

As an added bonus the table had a "configuration name" column which provided a simple way to choose/activate a specific set of configuration settings. That meant that prod, dev, and test could all live in the same table, though it was up to the application to choose which set to use. In our case a JVM argument made sense. It might make sense to store different "sets" of config settings in the same DB table; then again, it might not.

---

If you are thinking about file-based configuration, I like INI or YAML. You could still store it in a database, though you probably won't find an INI or YAML column type (as you might for XML).