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 when we want to store some series of boolean values in the database; I have good idea to avoid creating fields for each of such boolean values in the table. For example, choosing weekdays, months in a 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 shall be represented in the database with this encoding technique.

The 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 are finite or non-finite. If the list of selections is finite, then the implementation is pretty simple; size of the encoded value shall be straightaway 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 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 that 8 bits. Naturally, somehow we can predict the maximum possible count of newsletters. These can be maximum of 16 or even 32. Its better if we start allotting least significant bits (LSB) to the existing/identified newsletters in the encoded value.

Implementation of Weekdays Selections 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 below code, the AllDays property sets the values 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.

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

Sample application using Weekdays Class

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

Demo form in 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 database

SQL Server supports the following bitwise operations

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

The operands can be int, smallint, tinyint except image data types See supporting datatypes here. So, we can select the records with particular selections directly from 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 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

really the bitwise XOR does this work.

Advantages and Disadvantages

The advantages of this 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 extend, the addition of new selection items doesn’t require altering tables or related stored procedures.
3. Makes the coding easy for developing user interfaces.

Ofcourse there are few disadvantages too,

1. Interpreting the values directly in the database table is no 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)