在数据库中存储多个标志的最佳方法 [英] Best Way To Store Multiple Flags In Database

问题描述

我有一个基于Web的应用程序，该应用程序通过电子邮件通知用户网站上的活动。用户可以选择要接收的通知类型。到目前为止，大约有10种不同的选项（每个选项都是对/错）。

I have a web-based application that notifies users of activity on the site via email. Users can choose which kinds of notifcations they want to receive. So far there are about 10 different options (each one is a true/false).

我目前将其存储在一个varchar字段中，以0或1分隔，逗号。例如：
1,0,0,0,1,1,1,1,0,0

I'm currently storing this in one varchar field as a 0 or 1 separated by commas. For example: 1,0,0,0,1,1,1,1,0,0

此方法有效，但是很难添加新通知标志并跟踪哪个标志属于哪个通知。这样做是否有公认的标准？我正在考虑为每个通知类型添加另一个带有列的表。然后，我可以根据需要添加新列，但是我不确定这样做的效率如何。

This works but it's difficult to add new notification flags and keep track of which flag belongs to which notification. Is there an accepted standard for doing this? I was thinking of adding another table with a column for each notification type. Then I can add new columns if I need, but I'm not sure how efficient this is.

预先感谢！

推荐答案

我将使用两个表。一个表将存储用户数据，另一个表将存储他们订阅的通知。第二个表如下所示：

I would use two tables. One table would store the user data and the other the notifications that they subscribe to. The second table would look something like this:

create table notifications (
   user_id int,
   notification_type int
);

我要在user_id和用户表中的用户ID之间建立FK关系，并启用层叠删除。使用user_id和notification_type作为主键。要检查用户是否想要特定的通知，只需在两个表之间进行联接，然后选择notification_type与所讨论的匹配的行。如果结果集为非空，则用户需要通知。

I'd make a FK relationship between user_id and the user's id in the users table with a cascade on delete. Use both the user_id and notification_type as the primary key. To check if a user wants a particular notification simply do a join between the two tables and select rows where the notification_type matches the one in question. If the result set is non-empty the user wants the notification.

添加新通知变得很简单（删除操作也是如此）。只需添加（删除）新的类型值，然后让用户选择是否接受即可。如果您想将通知类型保留在一个表中，以通过应用程序进行管理，那也可以，但是会稍微复杂一些。

Adding new notifications becomes trivial (as does deleting). Simply add (delete) a new type value and let users choose to accept it or not. If you wanted to keep the notification types in a table to manage via the application that would work, too, but it would be a little more complex.

这篇关于在数据库中存储多个标志的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！