SQL and flags

Of course, this is not about the mode of the game Capture The Flag on the SQL server, but about the use of bit flags. Bit operations are familiar, probably, to all those involved in panning, regardless of the development environment and language. However, the use of flags, in my opinion, for many is an exotic rather than a daily tool. On Habré, we have repeatedly mentioned the possibility of a convenient .NET ability to work with flags through enum, but after all, SQL gives us excellent opportunities to use flags!

And so, we will consider a simple example - in some application there should be a certain system for notifying users. Suppose you are building a forum and want to enable the user to receive alerts by mail: a new answer in the chosen topic, a new personal message, forum news. A quick look at the task will be given by the trivial design of the tables:
tblUsers {userID (PK) as int, name as nvarchar (50), password as nvarchar (50)}
tblUserAlerts {userID (FK) as int, alertID (FK) as int}
tblAlerts {alertID (PK) as int, message as nvarchar (50)}


That is, there is a table of users, a table of alerts and the connection between them is implemented through an auxiliary table in order to allow each user to select more than one alert. The data will look something like this:
tblUsers
1, "Vasya", "tricky" 4 "
')
tblUserAlerts
eleven

tblAlerts
1, "You received a new personal message"

Now consider an example using the flag:
tblUsers {userID (PK) as int, name as nvarchar (50), password as nvarchar (50), alerts as int},
tblAlerts {alertID (PK) as int, message as nvarchar (50)}

In this embodiment, we get the same functionality and at the same time do without an additional table. In the tblAlerts table, we set the alertID as a flag bit using the same int (size depends on the number of alert options), and in the tblUsers table, the alerts field displays the alert mask bit. Suppose we create 3 types of alerts, then in the tblAlerts table there will be 4 (no alerts + 3 types mentioned) rows in the alertID field. The first ID will be 0 - in bits it will look like 0000, the value, respectively - no alerts. Then we add our alerts, igniting a different bit each time (each identifier will be a power of two): 0001 = 1, 0010 = 2, 0100 = 4:
tblAlerts
0, NULL
1, “New Answer in Favorite Topic”
2, "You received a new personal message"
4, "We have news!"

Now we need to subscribe the user, say, to the news and to alerts about personal messages. To do this, we summarize the identifiers of these alerts and get 0110 = 6:
tblUsers
1, “Vasya”, “slyp4r0l”, 6

Now we will build a query in the tblUsers table to find out which users are subscribed to the news:
SELECT * FROM tblUsers WHERE (tblUsers.alert & 4) > 0

The meaning of this operation is simple:
0110 - our mask
0100 - news flag
0100 - the result (that is, there is an intersection of the mask and the flag, the result will be greater than zero)

Pleasant aspect - you can compare and mask. Suppose we want to get a list of users subscribed to all types of alerts 1 + 2 + 4 = 7:
SELECT * FROM tblUsers WHERE (tblUsers.alert & 7) = 7

Note that there is an intersection between mask 6 and 7, but the result will be different from 7
0110 - user mask
0111 - check mask
0110 - result (intersection or minimum of 7 and 6 = 6)

With the same ease, you can make a request for a list of users who have signed up, either to notify you about replies or to be notified about personal messages 1 + 2 = 3:
SELECT * FROM tblUsers WHERE (tblUsers.alert & 3) > 0

A positive result will be a check of the user's mask with values ​​of 1, and 2, and 3 (that is, either one of the two alerts, or both).

In this simple way, you can get rid of the link table and even simplify queries. However, the downside may not be the ability to do some actions (say JOIN) through the standard visual query editor, since the standard will compare identifiers. It will be necessary to manually replace the comparison with a bit operation:
SELECT tblUsers.*, tblAlerts.*
FROM tblUsers INNER JOIN tblAlerts ON (tblUsers.alerts & tblAlerts.alertID) = tblAlerts.alertID