PHP / MySQL - storing an array in a database

Question

I am working on a PHP application that requires storing various parameters in a database. The client often asks if some things that cause problems with the table design can be added or changed / deleted. Basically, I had many boolean fields that simply indicated whether various settings were enabled for a particular record.

To avoid confusion with the table, I am considering storing data as a serialized array. I read that this is considered bad practice, but I think it is a justifiable case for using this approach.

Is there a real reason to avoid this?

Any advice is appreciated.

Thanks.

Answer 1

The real reason is normalization, and you break the first normal form by doing it.

However, there are many cases in which a violation of normal forms could be considered. How many fields do you mean, and are they all Boolean?

Saving 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, non-serialize the array, modify the array, serialize the array and update the data in the table.
• When searching, you won’t be able to simply ask the database if this parameter is disabled or enabled (or a set of users), so you will not have any chance of a search.

" " . , 30 , , ( , , , ).

: , , , .

Answer 2

( )

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;

, .

Answer 3

, . . . cgi cgi perl.

Answer 4

One reason for using a relational database is to help maintain data integrity. If you just have a serialized array flushed to the blob in the table, there is no way for the database to make any checks that what you have in this blob makes sense.

Answer 5

For what reason can you not save your settings in the configuration file on the server? For example, I save the settings of the website or application in the config.php file, and not in the database.