Saving data as array or individual fields (php/MySQL)

Question

I am designing a new system for users/clients and I have in my system user preferences. Before I start to create code and the database I want to make sure I do the right thing.

I have these preferences:

• font size
• font face
• font color
• theme
• home page
• dashboard options
• few true/false options like enable sharing etc...

and more.

My idea was to create each field for each preferences but I thouhgt maybe I can save an object or array instead in a blob.

Is it a good idea?

Answer 1

You could make an auxiliary table to keep references of properties names. Then you can link that table with a merge table — between a user id and the id of one of the properties. Doing so, you can always change the properties.

Using Foreign keys you can also “cascade delete” user details that have a property that does not exist anymore. Furthermore, you can ensure that you add only references to valid property names, and you optimize the search by using indexes.

Let's say that:

CREATE TABLE user_preferences_headers
(
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL
);

CREATE TABLE `users`
(
   `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHAR(255) NOT NULL,
   `pass` VARCHAR(255) NOT NULL
);

CREATE TABLE `user_preferences`
(
  `id_user` INT NOT NULL,
  `id_preference_entity` INT NOT NULL,
  `value` VARCHAR(255)
);

ALTER TABLE `user_preferences` ADD INDEX ( `id_user` );
ALTER TABLE `user_preferences` ADD INDEX ( `id_preference_entity` );

ALTER TABLE `user_preferences` ADD FOREIGN KEY ( `id_user` ) REFERENCES `users` (
`id`
) ON DELETE CASCADE ON UPDATE CASCADE ; 
ALTER TABLE `user_preferences` ADD FOREIGN KEY ( `id_preference_entity` ) REFERENCES `user_preferences_headers` (
`id`
) ON DELETE CASCADE ON UPDATE CASCADE ; 

Now you first select all headers from user_preferences_headers by name, or not, and you use the id to select the desired preference value of a user (identified also by id) from user_preferences. Notice that when you delete an entry in user_preferences_headers, all entries that link to the id of the deleted row will also be deleted.