用户偏好数据库表设计

Question

我希望创建一个用户偏好表格，但无法确定最佳方法。ASP.NET默认的方式似乎非常笨拙，我想避免使用它。目前，我为每个用户使用一行，其中为每个用户偏好使用不同的列（未标准化，我知道）。因此，我想到的另一个想法是将偏好本身拆分成自己的表，并在用户偏好表中为每个用户的每个偏好使用一行；但是，这意味着每个偏好需要具有完全相同的数据类型，这对我来说也不太理想。因此，我的问题是：设计用于保存用户偏好值的数据库的最佳/最合适的方法是什么？

- wanderingwebmaster

Answer 1

有些我在数据库工作中尽力避免的想法是数据重复和不必要的复杂性。您还要避免“插入、更新和删除异常”。话虽如此，将用户偏好存储在一个表中，每行=一个用户，列为可用的不同偏好，是有意义的。

现在，如果您可以看到这些偏好在数据库中以任何其他形式或方式使用，例如多个对象（不仅仅是用户）使用相同的偏好，则需要使用FK / PK对引用偏好。

至于您所描述的内容，我认为第一种方法可行。

- GregD

Answer 2

我通常会这样做：

Users table (user_id, .... etc.)
.
Options table (option_id, data_type, ... etc.)
(list of things that can be set by user)
.
Preferences table (user_id, option_id, setting)

我使用了新的SQLVARIANT数据类型来设置字段，因此它可以是不同的数据类型，并将选项的数据类型作为选项定义的一部分记录在Options表中，在查询时将其转换回正确的类型。

- Ron Savage

---

那么，在这种情况下，data_type会是什么？您是否有另一个包含所有数据类型的表格，以便将其作为外键？ - NSX

数据类型可以是像“decimal(4,2)”这样的字符串，也可以是您在应用程序中定义的枚举中的数字...这样您的应用程序就可以确定如何将结果转换为通用数据类型。 - Ron Savage

好的，它不需要是字符串。您可以定义一组有效的数据类型并创建一个Type表。这与我的解决方案类似。 - BobbyShaftoe

一个有效的数据类型表的问题在于许多数据类型都有宽度和精度参数（如decimal(4,2)或decimal(12,4)），因此你可能会得到很多微不足道的不同条目。 - Ron Savage

如果你使用sql_variant类型（实际上我对此一无所知），你可以像这样查询类型：SELECT *,
SQL_VARIANT_PROPERTY(val,'BaseType') AS basetype, SQL_VARIANT_PROPERTY(val,'Precision') AS PRECISION, SQL_VARIANT_PROPERTY(val,'Scale') AS scale, SQL_VARIANT_PROPERTY(val,'BaseType') AS basetype, SQL_VARIANT_PROPERTY(val,'TotalBytes') AS totalbytes, SQL_VARIANT_PROPERTY(val,'Collation') AS collation, SQL_VARIANT_PROPERTY(val,'MaxLength') AS maxlength - John Gibb

Answer 3

如果您将所有用户偏好存储在一个用户表的单个行中，那么维护将成为一场噩梦！

每个用户的每个偏好使用一个行，并将偏好值存储为 varchar（长度为255或某个足够满足您要求的值）。您需要显式地转换该列中的值。

唯一不易处理的情况是，如果您想将某些大型二进制数据作为用户偏好存储，则此方法不适用，但我发现这并不是常见需求。

- Mitch Wheat

---

然而，按每个用户每个偏好设置一行的方式进行操作似乎会导致EAV类型的模型，我一直认为应该尽量避免这种情况。 - NSX

Answer 4

很快，一个方法：

User(UserID, UserName, ...)

PreferenceDataType(PreferenceDataTypeID, PreferenceDataTypeName)

PreferenceDataValue(PreferenceDataValueID, PreferenceDataTypeID, IntValue, VarcharValue, BitValue, ...)

Preference(PreferenceID, PreferenceDataTypeID, PreferenceName, ...)

UserHasPreference(UserID, PreferenceID, PreferenceDataValueID)

- BobbyShaftoe