How best would you store configuration values in a database table?

Question

How best would you store configuration values in a database table?

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Key/Value is how I've done that.

Using the SQLVariant datatype might help, or might not, but is worth looking at. But really, converting a few values from varchar to int or numeric or whatever is no big deal. It only really matters if you have to do it a LOT, and then having it stored in separate columns for data types works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jason Norsworthy Say Hey Kid Points: 666 More actions · Answer 2

I think that option one is by far your best choice.

For option 2, I don't think you'd want to change the schema every time you want to add a configuration value.

For option 3, I don't see the benefit of maintaining several columns to hold just one value. A more normalized way to do it would probably be to store the value, as well as the data type. That said, I don't see much benefit of even storing the datatype unless you envision using configuration values without even knowing what they were being used for (perhaps through generated code?)

In addition to name/value pairs, consider adding a field for ApplicationName or some other identifier/namespacer if you foresee the need to have similar/same named values for different applications. Also consider the standard DeltaUser, DeltaTS, CreatedUser, CreatedTs if it may be important to see when configuration values changed, and who changed them.