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?
分享 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.
分享
