User settings, use EAV pattern or just add columns

I am using a MySQL database.

I have a user that can have many attributes, which I call settings. Each user has the exact same 50 settings. There will probably never be more than 2000 users in the system.

I can handle this in two approaches: add a column for each option, or use the EAV (entity attribute value) model. User should be searchable by any of its settings.

---

Adding columns only would look like this:

id | name | pass   | color | beta  | .. 
----------------------------------------
1  | bob  | 94jf8d | red   | true  | ..
3  | sue  | fja928 | pink  | false | ..

---

Using an EAV model would look like this:

user

id | name | pass
------------------
1  | bob  | 94jf8d
3  | sue  | fja928

setting

id | key   | default_value
--------------------------
1  | color | red
2  | beta  | false
.. | ..    | ..

user_setting

id | user_id | setting_id | value
---------------------------------
1  | 1       | 1          | pink
2  | 1       | 2          | true
3  | 2       | 1          | green
4  | 2       | 2          | false
.. | ..      | ..         | ..

---

I realize that EAV adds a whole layer of complexity. This includes having to deal with multiple joins to filter. It just seems dirty to add 50 columns to the user table.

In the end, do you think I am better off adding columns, or moving to an EAV model?

Thanks!

链接地址: http://www.djcxy.com/p/68638.html

上一篇: 使用GROUP BY获取最新条目

下一篇: 用户设置，使用EAV模式或仅添加列