Flags in a database rows, best practices

Generally speaking, I avoid bitmask fields. They're difficult to read in the future and they require a much more in-depth knowledge of the data to understanding.

The relational solution has been proposed previously. Given the example you outlined, I would create something like this (in SQL Server):

CREATE TABLE Users (
  UserId INT IDENTITY(1, 1) PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  EmailAddress VARCHAR(255)
);

CREATE TABLE Badges (
  BadgeId INT IDENTITY(1, 1) PRIMARY KEY,
  [Name] VARCHAR(50),
  [Description] VARCHAR(255)
);

CREATE TABLE UserBadges (
  UserId INT REFERENCES Users(UserId),
  BadgeId INT REFERENCES Badges(BadgeId)
);

If you really need an unbounded selection from a closed set of flags (e.g. stackoverflow badges), then the "relational way" would be to create a table of flags and a separate table which relates those flags to your target entities. Thus, users, flags and usersToFlags.

However, if space efficiency is a serious concern and query-ability is not, an unsigned mask would work almost as well.

A Very Relational Approach

For databases without the set type, you could open a new table to represent the set of entities for which each flag is set.

E.g. for a Table "Students" you could have tables "RegisteredStudents", "SickStudents", TroublesomeStudents etc. Each table will have only one column: the student_id. This would actually be very fast if all you want to know is which students are "Registered" or "Sick", and would work the same way in every DBMS.

For many cases, it depends on a lot of things - like your database backend. If you're using MySQL, for example, the SET datatype is exactly what you want.

Basically, it's just a bitmask, with values assigned to each bit. MySQL supports up to 64-bit values (meaning 64 different toggles). If you only need 8, then it only takes a byte per row, which is pretty awesome savings.

If you honestly have more than 64 values in a single field, your field might be getting more complicated. You may want to expand then to the BLOB datatype, which is just a raw set of bits that MySQL has no inherent understanding of. Using this, you can create an arbitrary number of bit fields that MySQL is happy to treat as binary, hex, or decimal values, however you need. If you need more than 64 options, create as many fields as is appropriate for your application. The downside is that is is difficult to make the field human readable. The BIT datatype is also limited to 64.