I want to build a mysql table to store admin settings for my website. Initially I want to store a setting for:
Website down for maintenance
Switch debug mode on
switch google analystics on
set server timezone
Basically key settings which will have a Y/N setting. As this table will be called once every time a page is built I need to structure it in the most sensible way. I'd call the table and intend to do one read on the entire table (4 rows) and then instantiate an object with 4 properties to handle the settings. Any suggestions - is this the most efficient way to do it?
CREATE TABLE IF NOT EXISTS `my_options` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', `value` varchar(65535) NOT NULL, PRIMARY KEY (`id`,`name`) ) ENGINE=InnoDB
No sense in trying to mash everything into one row if you're going to use a database to begin with. Then you have to jump through hoops after selecting the data just to make it usable.
If you're using MySQL < 5.0.3 you would have to change the longer varchar to a text or longtext - this is of course assuming you want to eventually store long options values or arrays in that column. If all your options are now and forever only going to be true|false (on/off), then you could use the following instead;