CODEWITHSUNDEEP
phpmysqldatabaseserialization
Erin Tucker
Erin Tucker

Reputation: 3334

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

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:

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?

Upvotes: 10

Views: 255

Answers (2)

khael
khael

Reputation: 2610

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.

Upvotes: 12

vascowhite
vascowhite

Reputation: 18430

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.

Upvotes: 3

Related Questions