I’m adding user settings in a web application. It seems obvious not to add a column in a table for each those parameters.

It looks like EAV (or property bag) was the best approach in 2012, and it makes sense to me. See this popular question.

For performance reasons (and more), it looks like more and more people go for the JSONB approach allowed by PostgreSQL. What troubles me is that you’ll end up with a lot of redundancy in your database (i.e. parameters name). Changing parameters name or adding metadata looks tough. Overall, it seems messy.

Visually:

Option 1 – Columns (nope)

+--------+-------------------+-----------------------+------------+----------+
| UserID | UserName           | Email                 | ThemeColor | FontSize |
+--------+-------------------+-----------------------+------------+----------+
| 1      | JohnDoe            | [email protected]      | #0000FF    | 14       |
| 2      | JaneSmith          | [email protected]      | #FFFF00    | 12       |
+--------+-------------------+-----------------------+------------+----------+

Option 2 – EAV / property bag

Users:
+--------+-------------------+-----------------------+
| UserID | UserName           | Email                 |
+--------+-------------------+-----------------------+
| 1      | JohnDoe            | [email protected]      |
| 2      | JaneSmith          | [email protected]      |
+--------+-------------------+-----------------------+

Attributes:
+-------------+--------------------+-----------+
| AttributeID | AttributeName       | DataType  |
+-------------+--------------------+-----------+
| 10          | ThemeColor          | string    |
| 11          | FontSize            | int       |
+-------------+--------------------+-----------+

UserSettings:
+--------------+--------+-------------+---------+
| UserSettingID | UserID | AttributeID | Value   |
+--------------+--------+-------------+---------+
| 1            | 1      | 10          | #0000FF |
| 2            | 1      | 11          | 14      |
| 3            | 2      | 10          | #FFFF00 |
+--------------+--------+-------------+---------+

Option 3 – JSONB

+--------+-------------------+-----------------------+---------------------------------------+
| UserID | UserName           | Email                 | Settings                              |
+--------+-------------------+-----------------------+---------------------------------------+
| 1      | JohnDoe            | [email protected]      | {"ThemeColor": "#0000FF", "FontSize": 14} |
| 2      | JaneSmith          | [email protected]      | {"ThemeColor": "#FFFF00", "FontSize": 12} |
+--------+-------------------+-----------------------+---------------------------------------+

What’s your take, if there were many settings, but similar accross users?