Sql – Flags in a database rows, best practices

databaseflagssql

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?

Best Solution

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

Related Solutions

C# – What does the [Flags] Enum Attribute mean in C#

The [Flags] attribute should be used whenever the enumerable represents a collection of possible values, rather than a single value. Such collections are often used with bitwise operators, for example:

var allowedColors = MyColor.Red | MyColor.Green | MyColor.Blue;

Note that the [Flags] attribute doesn't enable this by itself - all it does is allow a nice representation by the .ToString() method:

enum Suits { Spades = 1, Clubs = 2, Diamonds = 4, Hearts = 8 }
[Flags] enum SuitsFlags { Spades = 1, Clubs = 2, Diamonds = 4, Hearts = 8 }

...

var str1 = (Suits.Spades | Suits.Diamonds).ToString();
           // "5"
var str2 = (SuitsFlags.Spades | SuitsFlags.Diamonds).ToString();
           // "Spades, Diamonds"

It is also important to note that [Flags] does not automatically make the enum values powers of two. If you omit the numeric values, the enum will not work as one might expect in bitwise operations, because by default the values start with 0 and increment.

Incorrect declaration:

[Flags]
public enum MyColors
{
    Yellow,  // 0
    Green,   // 1
    Red,     // 2
    Blue     // 3
}

The values, if declared this way, will be Yellow = 0, Green = 1, Red = 2, Blue = 3. This will render it useless as flags.

Here's an example of a correct declaration:

[Flags]
public enum MyColors
{
    Yellow = 1,
    Green = 2,
    Red = 4,
    Blue = 8
}

To retrieve the distinct values in your property, one can do this:

if (myProperties.AllowedColors.HasFlag(MyColor.Yellow))
{
    // Yellow is allowed...
}

or prior to .NET 4:

if((myProperties.AllowedColors & MyColor.Yellow) == MyColor.Yellow)
{
    // Yellow is allowed...
}

if((myProperties.AllowedColors & MyColor.Green) == MyColor.Green)
{
    // Green is allowed...
}

Under the covers

This works because you used powers of two in your enumeration. Under the covers, your enumeration values look like this in binary ones and zeros:

 Yellow: 00000001
 Green:  00000010
 Red:    00000100
 Blue:   00001000

Similarly, after you've set your property AllowedColors to Red, Green and Blue using the binary bitwise OR | operator, AllowedColors looks like this:

myProperties.AllowedColors: 00001110

So when you retrieve the value you are actually performing bitwise AND & on the values:

myProperties.AllowedColors: 00001110
             MyColor.Green: 00000010
             -----------------------
                            00000010 // Hey, this is the same as MyColor.Green!

The None = 0 value

And regarding the use of 0 in your enumeration, quoting from MSDN:

[Flags]
public enum MyColors
{
    None = 0,
    ....
}

Use None as the name of the flag enumerated constant whose value is zero. You cannot use the None enumerated constant in a bitwise AND operation to test for a flag because the result is always zero. However, you can perform a logical, not a bitwise, comparison between the numeric value and the None enumerated constant to determine whether any bits in the numeric value are set.

You can find more info about the flags attribute and its usage at msdn and designing flags at msdn

SQL Server – Best way to get identity of inserted row

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

Best Solution

Related Solutions

C# – What does the [Flags] Enum Attribute mean in C#

SQL Server – Best way to get identity of inserted row

Related Question