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

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


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.


Saving them individually will make searching etc possible on each field. If you serialize them, then searching will become difficult/impossible.
You may not need that now, but it may be required further down the line.