System settings stored in 1 row table

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

System settings stored in 1 row table

Post 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?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: System settings stored in 1 row table

Post by allspiritseve »

Do you mean one column?

Maybe you could give us an example of what the data looks like that you are storing.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: System settings stored in 1 row table

Post 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!
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: System settings stored in 1 row table

Post 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?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: System settings stored in 1 row table

Post 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.
Post Reply