Flags in a database rows, best practices

Question

I am asking this out of a curiosity. Basically my question is when you have a database which needs a row entry to have things which act like flags, what is the best practice? A good example of this would be the badges on stack overflow, or the operating system field in bugzilla. Any subset of the flags may be set for a given entry.

Usually, I do c and c++ work, so my gut reaction is to use an unsigned integer field as a set of bits which can be flipped... But i know that isn't a good solution for several reasons. The most obvious of which is scale-ability, there will be a hard upper limit on how many flags I can have.

I can also think of a couple of other solutions which scale better but would have performance issues because they would require multiple selects to get all the information.

So, what is the "right" way to do this?

Answer 1

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) );