Config Table

Databases
1

This is going to sound like a really daft question, I’m sure, but I’m interested in thoughts on how people do this themselves and why they do it in that particular fashion:

Basically, as with most web-apps (though I know not all), we have a config table that holds a load of settings for various uses. When I’ve done it in the past, the table has been as simple as possible: two columns typically, with a config name, and the value. There may be other columns occasionally, like whether this is mandatory or not, read only (enforced in application), which site it pertains to if the DB supports multiple sites, etc. The value column however has to accept any possible type, so is usually a large varchar(). Types are all enforced in the application.

In this method, I would “SELECT conf_val FROM conf_table WHERE conf_key = ‘siteadmin’;” for example

However, at the company I work for now, they rotate everything around 90° and they have one single row of data, and each config key is a column name. This gives them the added advantage that the types are correctly maintained (the tax value in eCommerce for example cannot be text, as the column type is decimal). The problem I have though is that it’s a pain to find the data, and if I want to add an extra value for any reason then I need to alter the table before I can add it, instead of just being able to add another row. There’s also no indexing and worst of all, no PK, so it’s possible to have multiple rows with conflicting values.

In this method, I would “SELECT siteadmin FROM conf_table;” for example, assuming that an extra row has never crept in anywhere and any how. Then again, updates are usually to set a column value without any conditions, so all rows take the same value.

So, I’m curious about how people do it themselves, why, and what pros and cons you see to both. I’m wondering about whether it’s worth updating ours to my preferred method, or whether I’m better to just put up with it how it is

Thanks guys

2

i kinda like the many-columns approach at your company :slight_smile:

first of all, it’s not actually a pain to find the data, because either there’s a column for it (in which case you can 100% find it easily) or else there isn’t (in which case your search will be 0% successful)

indexing should not be needed if there’s only one row, in fact indexing would likely be ignored

enforcing only one row can be handled by the application a lot more easily than enforcing the actual datatypes for the various config values in your model (which, by the way, is called EAV, entiti-attribute-value, and is notoriously difficult to work with, although not, prehaps, in a simple config table)

i would definitely just put up with what you have and go find some other more worthy battle to fight

:slight_smile:

3

:slight_smile: Yeah, it’s not the worst idea, it’s just that I’ve never seen it done anywhere before so it’s… weird… you know? Maybe just because I’m not used to it, I find it hard to eyeball for the data that I’m looking for. Scrolling horizontally in my GUI instead of scrolling down feels wrong :smiley: A huge schema for a single row seems ridiculous too. Again, it feels wrong. What I do find annoying about it though, is that we have hundreds of websites out there and although they start from a common code-base, they develop over time… If we add new config values to newer versions then we have to explicitly alter the table with this method though, which is inconvenient. Whereas I could just insert a new row using the method that I’ve always used before, which is much more straightforward. Being able to store additional information against each config value has often been beneficial in the past too, so I’ve had extra columns (as in my original post).

It’s certainly far from the most pressing problem that we have with the software and the databases in general (in the past they’ve run ecommerce software with MyISAM tables which I’ve changed as soon as I could) and having someone say “it’s not actually a bad idea” does make me feel a bit better about it… but why don’t I see it anywhere else? Shining beacons they may not be, but PHPBB uses the type I’m more used to. Wordpress too. Bespoke systems that I’ve worked on in the past that are not my creation… this is the first time I’ve ever come across such a system. How come? Don’t tell me that my predecessors are geniuses, because I’ve got example code that proves otherwise :smiley:

4

I think enforcing data type is a great idea. You can go overboard with it, but I agree with r937. Maybe for a config table it has less usage. Though traditional EAV (meta relational structures) can benefit in terms of query optimizations and not needing cast values to the proper type on the fly. In fact that is something I did because I ran into that problem building a CMS that is based on a meta relational structure. Having separate columsn for short strings, integers, dates, etc makes it possible to index those columns and use them in joins without type casting resulting in better query performance. That is when dealing with meta relational schemas.

Every programmer thinks the guy before them was complete moron. Someone will come along after your gone, disagree with some decisions and call you a moron. That is the way it works. There are many times where my utter disgust of past decisions go a long way in getting me through the day. I’m sure the guy who has my post position thinks the same…

5

Ah yes, but we’ve had this chat about my predecessors and everyone agrees that they’re not too sharp. Unforgivably so, unfortunately :frowning: