Search code examples
phpmysqldata-storage

Storing lists: in files or MySQL?

Which is the generally better way of storing lists of the following type:

array(
   'category'=>array(
      'key'=>'value',
      'key'=>'value'
    ),
   'category'=>array(
      'key'=>'value',
      'key'=>'value'
    )
);

Would a more efficient approach to storing these lists be a lists.php file or a serialized entry in MySQL?

Solution

  • Well depends on your mysql tables structures:

    • If your tables are NORMALIZED then serializing data and storing it will break normalization.

    But other than that why dont you create two tables, one storing the categories and then another storing key-value pairs referencing to that category.

    CREATE TABLE Categories (
   CID INT,
   ***
   PRIMARY KEY (CID)
)ENGINE=InnoDB;

CREATE TABLE KeyValues (
   Category INT,
   Key varchar(100),
   Value varchar(100,
   FOREIGN KEY Category REFERENCES Categories(CID)
)ENGINE=InnoDB;

    and you can further normalize the keyvalues table depending on your structuring. I didnt not choose primary key on purpose since it depends whether you are normalizing your tables or not. Adding autoincrement primary key would be fine as well.

    this is easier and faster to select data. When you have serialized data, if you need to do some kind of calculation you first need to grab on PHP side, un-serialize it and do calculations where you could do same-thing by just one query.