PHP/MySQL - Storing array in database

Question

Hi, I'm working on a PHP app which requires various settings to be stored in a database. The client often asks if certain things can be added or changed/removed, which has been causing problems with the table design. Basically, I had a lot of boolean fields which simply indicated if various settings were enabled for a particular record.

In order to avoid messing around with the table any more, I'm considering storing the data as a serialized array. I have read that this is considered bad practice, but I think this is a justified case for using such an approach.

Is there any real reason to avoid doing this?

Any advice appreciated.

Thanks.

Answer 1

One thing is that extensibility in limited. Database should not be mixed with programming environment. Also changing the values in database and debugging is much easier. The database and cgi can be interchanged to another database or cgi like perl.

Answer 2

One of the reasons to use a relational database is to help maintain data integrity. If you just have a serialized array dumped into a blob in a table there is no way for the database to do any checking that what you have in that blob makes any sense.

Answer 3

It sounds like your data needs to be normalized a bit. Can you post table structures?

Answer 4

The real reason is normalisation, and you will break the first normalform by doing it.

However, there are many cases in which a breach of the normal forms could be considered. How many fields are you dealing with and are they all booleans?

Storing an array serialized as a string in your database will have the following disadvantages (among others):

• When you need to update your settings you must first extract the current settings from the database, unserialize the array, change the array, serialize the array and update the data in the table.
• When searching, you will not be able to just ask the database whether a given user (or a set of users) has a given setting disabled or enabled, thus you won't have any chances of searching.

Instead, you should really consider the option of creating another table with the records you need as a one-to-many relation from your other table. Thus you won't have 30 empty fields, but instead you can just have a row for each option that deviates from the default (note that this option has some disadvantages aswell, for example if you change the default).

In sum: I think you should avoid serializing arrays and putting them into the databases, at least if you care just a tiny bit about the aforementioned disadvantages.

Answer 5

The proper way (which isn't always the best way)

CREATE TABLE mytable (
    myid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    mytitle VARCHAR(100) NOT NULL
);
CREATE TABLE myarrayelements (
    myarrayid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    myid INT UNSIGNED NOT NULL,
    mykey VARCHAR(100) NOT NULL,
    myval VARCHAR(100) NOT NULL,
    INDEX(myid)
);

$myarray = array();
$res = mysql_query("SELECT mykey, myval FROM myarrayelements WHERE myid='$myid'");
while(list($k, $v) = mysql_fetch_array($res)) $myarray[$k] = $v;

Although sometimes it's more convenient to store a comma separated list.

Answer 6

Any reason you can't store your settings in a configuration file on the server? For example, I save website or application settings in a config.php rather than a database.