Click here to Skip to main content
12,623,850 members (27,659 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

28.8K views
24 bookmarked
Posted

Representing Multiple Selections using a Single Value in Database Tables

, 13 Nov 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
An alternative technique to store multiple bit (boolean) fields 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.

bitfields.png

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

  • 9th November, 2009: Initial post.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

P. Ganesh Kumar
Software Developer (Senior) Infence Technologies
India India
Ganesh Kumar has done his Bachelor's degree in Computer Science at Bharathiar University, Coimbatore, Master's degree in Computer Applications at IGNOU and M.Tech in Computer Science & Engineering at Dr. MGR University, Chennai, India.

He is having hands-on experience in Algorithm implementation, Application design/development for Servers, Desktop and Single Board Computer devices in .Net C#, C, C++.

Interested in developing & customizing algorithms.

His hobbies include Blogging, Listening to Music and Singing.

 Home:        http://www.infence.com/

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralBits! Pin
Bit-Smacker17-Nov-09 7:44
memberBit-Smacker17-Nov-09 7:44 
GeneralRe: Bits! Pin
P. Ganesh Kumar25-Nov-09 2:25
memberP. Ganesh Kumar25-Nov-09 2:25 
GeneralIts called Bit field Pin
sahej17-Nov-09 7:27
membersahej17-Nov-09 7:27 
QuestionWhat about "And" operator in weedays class? Pin
kltong16-Nov-09 16:32
memberkltong16-Nov-09 16:32 
GeneralNot suitable for indexes Pin
warny13-Nov-09 6:51
memberwarny13-Nov-09 6:51 
GeneralRe: Not suitable for indexes Pin
P. Ganesh Kumar14-Nov-09 23:38
memberP. Ganesh Kumar14-Nov-09 23:38 
GeneralRe: Not suitable for indexes Pin
voloda218-Nov-09 3:38
membervoloda218-Nov-09 3:38 
GeneralRe: Not suitable for indexes Pin
P. Ganesh Kumar25-Nov-09 2:17
memberP. Ganesh Kumar25-Nov-09 2:17 
GeneralMy vote of 1 Pin
voloda212-Nov-09 9:21
membervoloda212-Nov-09 9:21 
GeneralRe: My vote of 1 Pin
P. Ganesh Kumar14-Nov-09 23:43
memberP. Ganesh Kumar14-Nov-09 23:43 
Questionwhat would a query look like? Pin
bquick10-Nov-09 1:11
memberbquick10-Nov-09 1:11 
AnswerRe: what would a query look like? Pin
P. Ganesh Kumar10-Nov-09 3:06
memberP. Ganesh Kumar10-Nov-09 3:06 
AnswerRe: what would a query look like? Pin
P. Ganesh Kumar11-Nov-09 6:40
memberP. Ganesh Kumar11-Nov-09 6:40 
GeneralPretty much just a blog entry Pin
PIEBALDconsult9-Nov-09 7:09
memberPIEBALDconsult9-Nov-09 7:09 
GeneralRe: Pretty much just a blog entry Pin
P. Ganesh Kumar10-Nov-09 3:03
memberP. Ganesh Kumar10-Nov-09 3:03 
GeneralMy vote of 2 Pin
Daanvis9-Nov-09 2:48
memberDaanvis9-Nov-09 2:48 
GeneralRe: My vote of 2 Pin
P. Ganesh Kumar9-Nov-09 5:11
memberP. Ganesh Kumar9-Nov-09 5:11 
GeneralMy vote of 2 Pin
Sebastien Ros9-Nov-09 2:10
memberSebastien Ros9-Nov-09 2:10 
GeneralRe: My vote of 2 Pin
P. Ganesh Kumar9-Nov-09 5:10
memberP. Ganesh Kumar9-Nov-09 5:10 
QuestionConsidered using enum? Pin
Paulo Zemek9-Nov-09 1:16
memberPaulo Zemek9-Nov-09 1:16 
AnswerRe: Considered using enum? Pin
P. Ganesh Kumar9-Nov-09 1:30
memberP. Ganesh Kumar9-Nov-09 1:30 
AnswerRe: Considered using enum? Pin
P. Ganesh Kumar9-Nov-09 5:08
memberP. Ganesh Kumar9-Nov-09 5:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.161128.1 | Last Updated 13 Nov 2009
Article Copyright 2009 by P. Ganesh Kumar
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid