Representing Multiple Selections using a Single Value in Database Tables

Introduction

Usually, boolean values are stored as bit fields in database tables. If we require only a few boolean values to be stored in the table, it would be good to have them as separate bit fields. But consider a situation where we want to store some series of boolean values in the database; I have a good idea to avoid creating fields for each boolean value in the table. For example, choosing weekdays, months in scheduling kind of problems, attendance for 8 hours of a day in school/college management applications, list of colours, card-suit values such as clubs, diamonds, hearts, spades, or any kind of finite or non-finite enumerations can be represented in the database with this encoding technique.

Background

The idea is very simple, and needs bitwise interpretation logic for extracting each of the required bit fields out from the encoded value and conflating from all the bit values back to the encoded value.

Before deciding the size of the field for storing the encoded value, identify whether the list of selections is finite or non-finite. If the list of selections is finite, then the implementation is pretty simple; the size of the encoded value will straightaway be the number of items in the list. In weekdays-selection, for 7 days, it is enough to have a byte type data field in the table to hold the whole week. For 12 months, we need a 16 bit data field.

But, if the list is non-finite, then we must do it with some predictions. For example, assume that our company presently releases 5 newsletters and users are provided with the choice of subscribing these newsletters. We may need a byte to hold the encoded value for each user's subscriptions. It can hold up to 8 newsletter subscriptions. But, in future, if the company comes out with more than 8 newsletters, our encoded value shall require more than 8 bits. Naturally, somehow we can predict the maximum possible count of newsletters. These can be a maximum of 16 or even 32. It's better if we start allotting the least significant bits (LSB) to the existing/identified newsletters in the encoded value.

Implementation of Weekdays Selection in C#

Let us implement the selection of weekdays using this technique. For alert/alarm kind of applications, this Weekdays class can be used. This class contains 7 boolean type properties for 7 days in a week to represent whether the corresponding day is required to be included in the schedule or not. In the code below, the AllDays property sets the value of each weekday by interpreting the encoded value read from the database table which shall be used to show in the checkboxes; similarly, it gets the independently selected weekdays in a single encoded value, which can be stored in the table in a single field. Thanks to Paulo Zemek for his advice to change the values used for bitwise operations to enumerated data with a flags attribute.

public class Weekdays
{
    // Enumerated data with Flags for setting 
    // each corresponding bit among 7 bits
    [Flags]
    enum weekday
    { 
        Sun = 1,
        Mon = 2,
        Tue = 4,
        Wed = 8,
        Thu = 16,
        Fri = 32,
        Sat = 64
    };
    
    // Store boolean values for seven days
    private bool sunday;
    private bool monday;
    private bool tuesday;
    private bool wednesday;
    private bool thursday;
    private bool friday;
    private bool saturday;
    
    // Expose the properties outside
    public bool Sunday
    {
        get { return sunday; }
        set { sunday = value; }
    }
    public bool Monday
    {
        get { return monday; }
        set { monday = value; }
    }
    public bool Tuesday
    {
        get { return tuesday; }
        set { tuesday = value; }
    }
    public bool Wednesday
    {
        get { return wednesday; }
        set { wednesday = value; }
    }
    public bool Thursday
    {
        get { return thursday; }
        set { thursday = value; }
    }
    public bool Friday
    {
        get { return friday; }
        set { friday = value; }
    }
    public bool Saturday
    {
        get { return saturday; }
        set { saturday = value; }
    }
    
    // Making all the days to be jointly interpreted as a single byte
    public byte AllDays
    {
        get
        {
            byte Value = 0;
            // from LSB to MSB - Sunday, Monday, Tuesday, 
            // Wednesday, Thursday, Friday, Saturday
            if (sunday) Value |= (byte)weekday.Sun ;
            if (monday) Value |= (byte)weekday.Mon ;
            if (tuesday) Value |= (byte)weekday.Tue;
            if (wednesday) Value |= (byte)weekday.Wed;
            if (thursday) Value |= (byte)weekday.Thu;
            if (friday) Value |= (byte)weekday.Fri;
            if (saturday) Value |= (byte)weekday.Sat;
            return Value;
        }
        set
        {
            // Extract the corresponding bits for each weekday
            // into bool properties of the class
            sunday = ((value & (byte)weekday.Sun ) != 0 );
            monday = ((value & (byte)weekday.Mon) != 0);
            tuesday = ((value & (byte)weekday.Tue) != 0);
            wednesday = ((value & (byte)weekday.Wed) != 0);
            thursday = ((value & (byte)weekday.Thu) != 0);
            friday = ((value & (byte)weekday.Fri) != 0);
            saturday = ((value & (byte)weekday.Sat) != 0);
        }
    }
}

Using the Code

I have developed a demo application for using this. The screenshot shows the form and its controls in its running mode.

The code below is to exemplify the usage of the Weekdays class:

public partial class frmDemo : Form
{
    // Weekdays object
    Weekdays weekdays = null;
    public frmDemo()
    {
        InitializeComponent();
    }
    
    private void frmDemo_Load(object sender, EventArgs e)
    {
        // Create a Weekdays object
        weekdays = new Weekdays();
    }
    
    // Get the current byte value for the checkbox selections and
    // and update the checkbox values
    private void btnSetByteValue_Click(object sender, EventArgs e)
    {
        weekdays.AllDays = Convert.ToByte(txtByteIn.Text);
        PopulateCheckBoxes();
    }
    
    // Populate checkbox values from the weekdays object
    private void PopulateCheckBoxes()
    {
        chkSunday.Checked = weekdays.Sunday;
        chkMonday.Checked = weekdays.Monday;
        chkTuesday.Checked = weekdays.Tuesday;
        chkWednesday.Checked = weekdays.Wednesday;
        chkThursday.Checked = weekdays.Thursday;
        chkFriday.Checked = weekdays.Friday;
        chkSaturday.Checked = weekdays.Saturday;
    }
    
    // Get the checked/unchecked values
    // from checkboxes into the Weekdays object
    private void GetCheckBoxesSelections()
    {
        weekdays.Sunday = chkSunday.Checked;
        weekdays.Monday = chkMonday.Checked;
        weekdays.Tuesday = chkTuesday.Checked;
        weekdays.Wednesday = chkWednesday.Checked;
        weekdays.Thursday = chkThursday.Checked;
        weekdays.Friday = chkFriday.Checked;
        weekdays.Saturday = chkSaturday.Checked;
    }
    
    // Checkbox values are collected in the weekdays object and
    // displays the resultant byte in the output textbox
    private void btnGetByteValue_Click(object sender, EventArgs e)
    {
        GetCheckBoxesSelections();
        txtByteOut.Text = weekdays.AllDays.ToString();
    }
}

Manipulating selections directly from the database

SQL Server supports the following bitwise operations:

• & (Bitwise AND)
• | (Bitwise OR)
• ^ (Bitwise Exclusive OR)

The operands can be int, smallint, or tinyint, except image data types. See the list of supporting datatypes here. So, we can select the records with particular selections directly from the query. For instance, in our weekdays selection example program:

DECLARE @Sun as smallint;
DECLARE @Mon as smallint; 
DECLARE @Tue as smallint; 
DECLARE @Wed as smallint;
DECLARE @Thu as smallint;
DECLARE @Fri as smallint;
DECLARE @Sat as smallint;
SET @Sun = 1;
SET @Mon = 2;
SET @Tue = 4;
SET @Wed = 8;
SET @Thu = 16;
SET @Fri = 32;
SET @Sat = 64;
SELECT * FROM ScheduleForYou WHERE (WeekdaySelection & @Sun)<>0

The above query selects the records only when Sunday is selected in it.

One important and interesting feature of this technique is you can select the records with only Sunday selected. Wow! this avoids multiple conditions applied in a query; i.e., when you have separate fields in the table for each day, you have to apply the conditions like this:

WHERE Sunday=true AND Monday=false AND Tuesday=false AND Wednesday=false 
                  AND Thursday=false AND Friday=false AND Saturday=false

But, the following query achieves this in our table:

SELECT * FROM ScheduleForYou WHERE (WeekdaySelection ^ @Sun)=0

The bitwise XOR does this work, and thanks to CodeProject member bquick for making me explore this via his question.

Advantages and disadvantages of this technique

The advantages include:

1. Avoids independent, clumsy bit fields in the table which will be difficult to manage while passing them from application via Stored Procedures and queries.
2. To some extent, the addition of new selection items doesn't require altering tables or related Stored Procedures.
3. Makes the coding easy for developing user interfaces.

Of course, there are a few disadvantages too:

1. Interpreting the values directly in the database table is not easy.
2. Enforcing field level access rights is missing.
3. Most significant unused bits are wasted (if any; and the right choice of data type for the field is a must).

History

• 9^th November, 2009: Initial post.