[ACCEPTED]-How do you structure config data in a database?-configuration

You can expand option 1 to have a 3rd column, giving 7 a data-type. Your application can than use 6 this data-type column to cast the value.

But 5 yeah, I would go with option 1, if config 4 files are not an option. Another advantage 3 of option 1 is you can read it into a Dictionary 2 object (or equivalent) for use in your application 1 really easily.

Since configuration typically can be stored 12 in a text file, the string data type should 11 be more than enough to store the configuration 10 values. If you're using a managed language, it's 9 the code that knows what the data type should 8 be, not the database.

More importantly, consider 7 these things with configuration:

• Hierarchy: Obviously, configuration will benefit from a hierarchy
• Versioning: Consider the benefit of being able to roll back to the configuration that was in effect at a certain date.
• Distribution: Some time, it might be nice to be able to cluster an application. Some properties should probably be local to each node in a cluster.
• Documentation: Depending on if you have a web tool or something, it is probably nice to store the documentation about a property close to the code that uses it. (Code annotations is very nice for this.)
• Notification: How is the code going to know that a change has been made somewhere in the configuration repository?

Personally, i 6 like an inverted way of handling configuration, where 5 the configuration properties is injected 4 into the modules which don't know where 3 the values came from. This way, the configuration 2 management system can be very complex or 1 very simple depending on your (current) needs.

I use option 1.

0

It seems overkill to use the DB for config 25 data.

EDIT (sorry too long for comment box): Of 24 course there's no strict rules on how you 23 implement any part of your program. For 22 the sake of argument, slotted screwdrivers 21 work on some philips screws! I guess I judged 20 too early before knowing what your scenario 19 is.

Relational database excels in massive 18 data store that gives you quick storing, updating, and 17 retrieval, so if your config data is updated 16 and read constantly, then by all means use 15 db.

Another scenario where db may make sense 14 is when you have a server farm where you 13 want your database to store your central 12 config, but then you can do the same with 11 a shared networked drive that point to the 10 xml config file.

XML file is better when 9 your config is hierarchically structured. You 8 can easily organize, locate, and update 7 what you need, and for bonus benefit you 6 can version control the config file along 5 with your source code!

All in all, it all 4 depends on how the config data is used.

That 3 concludes my opinion with limited knowledge 2 of your application. I am sure you can make 1 the right decision.

My project uses a database table with four 16 columns:

1. ID [pk]
2. Scope (default 'Application')
3. Setting
4. Value

Settings with a Scope of 'Application' are 15 global settings, such as Maximum number 14 of simultaneous users.

Each module has 13 its own scope based; so our ResultsLoader 12 and UserLoader have different scopes, but 11 both have a Setting named 'inputPath'.

Defaults 10 are either provided in the source code or 9 are injected via our IoC container. If 8 no value is injected or provided in the 7 database, the default from the code is used 6 (if one exists). Therefore, defaults are 5 never stored in the database.

This works 4 out quite well for us. Each time we backup 3 the database we get a copy of the Configuration 2 which is quite handy. The two are always 1 in sync.

I guess this is more of a poll, so I'll 5 say the column approach (option 2). However 4 it will depend on how often your config 3 changes, how dynamic it is, and how much 2 data there is, etc.

I'd certainly use this 1 approach for user configurations / preferences, etc.

Go with option 2. Option 1 is really a way 4 of implenting a database on top of a database, and 3 that is a well-known antipattern, which 2 is just going to give you trouble in the 1 long run.

I can think of at least two more ways:

(a) Create 4 a table with key, string-value, date-value, int-value, real-value 3 columns. Leave unused types NULL.

(b) Use 2 a serialization format like XML, YAML or 1 JSON and store it all in a blob.

Where do you you store the configuration 3 settings your app needs to connect to the 2 database?

Why not store the other config 1 info there too?

I'd go with option 1, unless the number 2 of config options were VERY small (seven 1 or less)

At my company, we're working on using option 11 one (a simple dictionary-like table) with 10 a twist. We're allowing for string substitution 9 using tokens which contain the name of the 8 config variable to be substituted.

For example, the 7 table might contain rows ('database connection 6 string', 'jdbc://%host%...') and ('host', 'foobar'). Encapsulating 5 that with a simple service or stored procedure 4 layer allows for an extremely simple, but 3 flexible, recursive configuration. It supports 2 our need to have multiple isolated environments 1 (dev, test, prod, etc).

I've used both 1 and 2 in the past, and 19 I think they're both terrible solutions. I 18 think Option 2 is better because it allows 17 typing, but it's a lot more ugly than option 16 1. The biggest problem I have with either 15 is versioning the config file. You can 14 version SQL reasonably well using standard 13 version control systems, but merging changes 12 is usually problematic. Given an opportunity 11 to do this "right", I'd probably create 10 a bunch of tables, one for each type of 9 configuration parameter (not necessarily 8 for each parameter itself), thus getting 7 the benefit of typing and the benefit of 6 the key/value paradigm where appropriate. You 5 can also implement more advanced structures 4 this way, such as lists and hierarchies, which 3 will then be directly queryable by the app 2 instead of having to load the config and 1 then transform it somehow in memory.

I vote for option 2. Easy to understand 1 and maintain.

Option 1 is good for an easily expandable, central 12 storage location. In addition to some of 11 the great column suggestions by folks like 10 RB, Hugo, and elliott, you might also consider:

Include 9 a Global/User setting flag with a user field 8 or even a user/machine field (for machine-specific 7 UI type settings).

Those can, of course, be 6 stored in a local file, but since you are 5 using the database anyway, that makes these 4 available for aliasing a user when debugging 3 - which can be important if the bug is setting 2 related. It also allows an admin to manage 1 setings when necessary.

I use a mix of option 2 and XML columns 3 in SQL server. You may also wan't to add 2 a check constraint to keep the table at 1 one row.

CREATE TABLE [dbo].[MyOption] (
  [GUID] uniqueidentifier CONSTRAINT [dfMyOptions_GUID] DEFAULT newsequentialid()  ROWGUIDCOL NOT NULL,
  [Logo] varbinary(max) NULL,
  [X] char(1)  CONSTRAINT [dfMyOptions_X] DEFAULT 'X' NOT NULL,
  CONSTRAINT [MyOptions_pk] PRIMARY KEY CLUSTERED ([GUID]),
  CONSTRAINT [MyOptions_ck] CHECK ([X]='X')
)

for settings that have no relation to any 11 db tables, i'd probably go for the EAV approach 10 if you need the db to work with the values. otherwise 9 a serialized field value is good if it's 8 really just a store for app code.

but what 7 about a format for a single field to store 6 multiple config settings to be used by the 5 db?

like one field per user that contains 4 all their settings related to their messageboard 3 view (like default sort order, blocked topics, etc.), and 2 maybe another with all their settings for 1 their theme (like text color, bg color, etc.)

Storing hierarchy and documents in a relational 11 DB is madness. Firstly you either have to 10 shred them, only to recombine them at some 9 later stage. Or there bunged inside a BLOB, even 8 more stupid.

Don't use use a relational db 7 for non-relational data, the tool does not 6 fit. Consider something like MongoDB or 5 CouchDB for this. Schema-less no-relational 4 data stores. Store it as JSON if it's coming 3 down the wire in any way to a client, use 2 XML for serverside.

CouchDB gives you versioning 1 out of the box.

Don't store configuration data in a database 33 unless you have a very good reason to. If 32 you do have a very good reason, and are 31 absolutely certain you are going to do it, you 30 should probably store it in a data serialization 29 format like JSON or YAML (not XML, unless 28 you actually need a markup language to configure 27 your app -- trust me, you don't) as a string. Then 26 you can just read the string, and use tools 25 in whatever language you work in to read 24 and modify it. Store the strings with timestamps, and 23 you have a simple versioning scheme with 22 the ability to store hierarchical data in 21 a very simple system. Even if you don't 20 need hierarchical config data, at least 19 now if you need it in the future you won't 18 have to change your config interface to 17 get it. Of course you lose the ability to 16 do relational queries on your config data, but 15 if you're storing that much config data, then 14 you're probably doing something very wrong 13 anyway.

Companies tend to store lots configuration 12 data for their systems in a database, I'm 11 not sure why, I don't think much thought 10 goes into these decisions. I don't see this 9 kind of thing done too often in the OSS 8 world. Even large OSS programs that need 7 lots of configuration like Apache don't 6 need a connection to a database containing 5 an apache_config table to work. Having a 4 huge amount of configuration to deal with 3 in your apps is a bad code smell, storing 2 that data in a database just causes more 1 problems (as this thread illustrates).