Bundling of related boolean values for an SQLite database or keeping them separate?

Question

I'm writing a reminder application where reminders can be shown or not for each day of the week (similar to Google Calendar). So I'd like to store the following in my SQLite database:

• Monday: True or False
• Tuesday: True or False
• etc. for all seven days of the week

I could simply store one True/False value for each weekday. SQLite doesn't have a boolean datatype, so I'd have to store it as integers in this case.

An alternative would be to store a single integer between 0 and 127 (000 0000 and 111 1111 in binary).

Normally I'd prefer the first solution since it's more direct, but, when there are so many boolean values, wouldn't it be better to bundle them together?

What should I consider when choosing between these approaches? Is there a better alternative?

Answer 1

There are a couple benefits to storing the day of the week as a bitarray.

• Fewer resources - as you've said, there would be 7 integer columns to support a separated day of the week schema. This probably won't be a big issue in a small database like the one you're describing. I wouldn't base a decision on this advantage alone.
• Easy querying - I think this is the most compelling reason to use the bit array. With the bit array you can use bitwise operators to quickly and easily identify reminders for the current date using the number of the day of the week. If you store each day separately it will become much more difficult to query reminders by day of the week. You would either need a dynamic query that is built differently depending on the current date or you would need a somewhat complex query that can handle any day of the week.

There is an alternative approach where instead of having a column for each day you can normalize the days with another table:

Where a row in reminder_day signifies the reminder is active for that day.

You can query like so:

select
  a.*
from
  reminder a inner join
  reminder_day b on a.id = b.reminder_id
where
  day_of_week = 1 // find reminders for Sunday

This isn't necessarily better performing or saving space, but it is easy to query and it may be easier for others to understand than using a bit array.

Answer 2

@Samuel's answer is fine, nevertheless I like to play the devil's advocate here and argue for a slightly different point of view. IMHO

It is - more or less - a matter of taste.

• If you store one or seven integers per record in a database will be most likely negligible for any kind of real world application, as long as you do not expect to manage several billions of reminders, so "Resources" is not really relevant here (note, for this specific use case, it is most likely the number of boolean values will stay 7, over the whole life time of your application).

• Queries for a dynamically given weekday when using one column per day get a little bit longish, but not really more complex than when using a bit array. A query like

  SELECT * FROM reminders WHERE Monday=:x1 AND Tuesday=:x2 AND Wednesday=:x3 AND ... Sunday =:x7

is obviously longer than SELECT * FROM reminders WHERE DaysArray=:x, and you need to initialize the boolean/integer arguments x1 to x7 in code to the correct values for a given day of the week, however, I do not call this "more complex". In fact, a bitarray column is not 100% self-documenting, since it is not obvious if the days are ordered from left to right or right to left, or if the first but refers to sunday or monday.

• using an additional table `reminder_days' allows shorter queries than a solution with seven individual columns, but these are definitely more complex than for the other two approaches. It would be clearly preferable if there is a chance the records in "reminder_day" will ever become subject to change, but for this case, I think this will also be just a matter of taste.

So if I have three almost equivalent solutions, I would probably prefer to use the one where I have the fewest amount of code to type, since I am lazy guy and I am used to bitarrays. But other people might think differently, and prefer a solution where the names of the days of the week occur explicitly as column names or records in the database.

So there is no obviously "best" solution, any of the three will work, and any of them has some pros and cons. Just pick your choice.

Answer 3

Another possibility is to take a hybrid approach by using a view to supplement your table:

CREATE TABLE Reminders (
    ReminderID   INTEGER PRIMARY KEY,
    Description  VARCHAR,
    WeekdayFlags INTEGER
);

CREATE VIEW RemindersByWeekday AS
SELECT
    ReminderID,
    Description,
    (WeekdayFlags & 1) != 0 AS Sunday,
    (WeekdayFlags & 2) != 0 AS Monday,
    (WeekdayFlags & 4) != 0 AS Tuesday,
    (WeekdayFlags & 8) != 0 AS Wednesday,
    (WeekdayFlags & 16) != 0 AS Thursday,
    (WeekdayFlags & 32) != 0 AS Friday,
    (WeekdayFlags & 64) != 0 AS Saturday
FROM Reminders;

This way, you get the space efficiency of having all your flags packed into a single column, but can easily write queries that operate on individual days of the week.