SQL and flags

Of course, it’s not about the Capture The Flag game mode on the SQL server, but about the use of bit flags. Bit operations are probably familiar to everyone involved in panning, regardless of the environment and development language. However, the use of flags, in my opinion, is exotic for many than an everyday tool. On Habré, the possibility of a convenient .NET ability to work with flags through enum has been mentioned more than once, but SQL also gives us excellent opportunities for using flags!

And so, let's look at a simple example - in a certain application there should be a certain user notification system. Suppose you are building a forum and want to allow the user to receive notifications by mail: a new answer in a selected topic, a new personal message, forum news. A quick look at the task will give a trivial table design:
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 user table, the alert table and the relationship between them is implemented through an auxiliary table in order to enable each user to select more than one alert. The data will look something like this:
tblUsers
1, “Vasya”, “tricky 4p0l”

tblUserAlerts
1, 1

tblAlerts
1, “You have a new private message”

Now let's look at 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 version, we get the same functionality and do without an additional table. In the tblAlerts table, we set alertID as a flag bit using the same int (the 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, so in the tblAlerts table there will be 4 (no alerts + the mentioned 3 types) rows with flags in the alertID field. The first ID will be 0 - in bits it will look like 0000, the value, respectively - there are no notifications. Then we add our alerts by lighting 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 the selected topic”
2, “You received a new personal message"
4, “We have news!”

Now we need to subscribe the user, for example, to news and to notifications of private messages. To do this, we summarize the identifiers of these alerts and get 0110 = 6:
tblUsers
1, “Vasya”, “tricky 4p0l”, 6

Now we will build a query in the tblUsers table to find out which user has subscribed to the news: The meaning of this operation is simple: 0110 is 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) A nice aspect - you can compare the masks as well. Suppose we want to get a list of users subscribed to all types of alerts 1 + 2 + 4 = 7: Note that there is an intersection between mask 6 and 7, but the result will be different from 7
SELECT * FROM tblUsers WHERE (tblUsers.alert & 4) > 0







SELECT * FROM tblUsers WHERE (tblUsers.alert & 7) = 7


0110 - user mask
0111 - verification mask
0110 - result (intersection or minimum of 7 and 6 = 6)

With the same ease, you can make a request to the list of users subscribed to either a notification of replies or a notification of private messages 1 + 2 = 3: A positive result will be considered checking the user's mask with the values ​​of 1, 2, and 3 (that is, either one of two alerts, or both). In such a simple way, you can get rid of the middleware table and even simplify queries. However, the disadvantage may not be the ability to do some action (say JOIN) through the standard visual query editor, as the standard will compare identifiers. It will be necessary to manually replace the comparison with a bit operation:
SELECT * FROM tblUsers WHERE (tblUsers.alert & 3) > 0




SELECT tblUsers.*, tblAlerts.*
FROM tblUsers INNER JOIN tblAlerts ON (tblUsers.alerts & tblAlerts.alertID) = tblAlerts.alertID