Page 1 of 1

System settings stored in 1 row table

Posted: Mon Jul 28, 2008 12:41 pm
by jayshields
I'm just looking for a bit of advice here, mainly to do with performance and "bad practices".

I'm currently programming a system which will consist of both Java desktop applications and PHP powered web applications, and they all share one MySQL database.

The whole system has some universal settings, and as of now, I'm storing them in MySQL table with one row - it works fine, but having a table with only ever one row screams bad practice, but I don't know why.

If the system wasn't spread across multiple platforms then the obvious choice would be to put them in a local flat file. The closest I can get to that is putting a flat file on the web server and have the Java application read from that, but it seems overkill when both parties are already interacting with the MySQL database.

What would you do in this situation? Stick with a one-row table? Are there any performance issues or pit-falls that I'm missing here?

Re: System settings stored in 1 row table

Posted: Mon Jul 28, 2008 2:17 pm
by allspiritseve
Do you mean one column?

Maybe you could give us an example of what the data looks like that you are storing.

Re: System settings stored in 1 row table

Posted: Mon Jul 28, 2008 6:59 pm
by jayshields
No, just one row. With different fields for each setting (property) name, and then one single row in the table with the value for each field. No point in posting a query result cos it's just too stupid!

Re: System settings stored in 1 row table

Posted: Mon Jul 28, 2008 8:36 pm
by allspiritseve
Why not have columns like this:

Code: Select all

config
------
id
key
value
default_value
And then you won't have to change your database schema if you add or delete settings?

Re: System settings stored in 1 row table

Posted: Tue Jul 29, 2008 1:52 am
by Mordred
One row is fine, you won't have your sql license revoked for going below the mandatory number of rows as per union regulations or something.

What allspiritseve proposes is not wise - it's implementing a "database on top of a database". Well, we already have a database, which is more efficient at handling which value goes in which column than the proposed scheme.
Changing the database schema is part of SQL as well, so I can't see the problem he is trying to solve with that.