我只是想知道为我的Web应用程序存储用户特定设置的最佳方法是什么?用户可能仅有的偏好.我想到了两个选择:
I was just wondering what would be the best way of storing user-specific settings for my web applications? Just preferences users may have. I've thought of two options:
用户表-我将为用户提供一个表.创建一个称为首选项"的列,并将序列化的数据存储在键=>值对中
Users table - I'll have a table for my users. Creating a column called "preferences" and storing serialized data there in key => value pairs
设置表-使用名为user_id列的单独表称为设置.以相同的方式保存设置
Settings table - Have a separate table called settings with a user_id column. Save the settings in the same way
任何输入将不胜感激.谢谢:)!
Any input would be appreciated. Thanks :)!
-
只需添加一下,如果我不进行序列化/json或放入数据中的任何数据,则每个设置都必须有一个列.
Just to add, if I didn't serialize/json or whatever the data to put in the data, I'd have to have a column for each setting.
推荐答案对于始终为每个用户设置的 设置的任何内容,您都应将其保留在表,按通常的归一化.至于可选配置,我倾向于喜欢以下表结构:
For anything that is always set for every user you should tend to keep that in the Users table, per usual normalization. As for optional config I tend to like the following table structure:
TABLE Users: id INT AI name VARCHAR ... TABLE User_Settings user_id INT PK,FK name VARCHAR PK type BOOL value_int INT NULL value_str VARCHAR NULLUser_Settings.type其中指定应引用整数还是字符串字段.
Where User_Settings.type specifies whether the integer or string field should be referenced.
即:
INSERT INTO Users (id, name) VALUES (1, 'Sammitch'); INSERT INTO User_Settings (user_id, name, type, value_int) VALUES (1, 'level', 1, 75); INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'en');对于INSERT/UPDATE问题:
And for the INSERT/UPDATE issue:
INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'fr') ON DUPLICATE KEY UPDATE value_str='fr';而且,正如大多数其他人所说的那样,序列化和存储首选项并不是一个特别好的主意,因为:
Also, as most other people are saying, serializing and storing the preferences is not a particularly good idea because:
2016年9月的回顾性
在这段时间里,我和人们讨论了如何最好地存储可选设置以及上面定义的常规表结构的一些争论.
Sept 2016 Retrospective
In the intervening time I've had a few arguments with people about how best to store optional settings, as well as the general table structure defined above.
虽然表结构不是完全错误的坏,但它也不完全是好.它试图充分利用糟糕的情况.只要您可以接受以下设置,就可以对可选设置进行序列化:
While that table structure isn't outright bad, it's not exactly good either. It's trying to make the best of a bad situation. Serialization of optional settings can work so long as you can accommodate for these settings:
然后,您可以考虑在Users表中添加类似optional_settings的字段,其中包含设置的序列化[eg:JSON]形式.您确实需要权衡上述各项,但这是一种更直接的方法,可以存储更复杂的设置.
Then you might consider adding a field like optional_settings in the Users table containing a serialized [eg: JSON] form of the settings. You do trade off the above, but it's a more straightforward approach and you can store more complex settings.
此外,如果您使用像TEXT这样的LOB类型进行存储,则数据不一定至少在MySQL中存储在行中".
Also, if you use a LOB type like TEXT for storage the data is not necessarily stored "in the row" at least in MySQL.
无论如何,您要确定您的应用程序的要求和约束,并根据这些信息做出最佳选择.
Anyhow, it's up to you to determine what your application's requirements and constraints are, and make the best choice based on that information.
更多推荐
在MySQL中存储用户设置的最佳方法?
发布评论