Best Design for Passing Filter Set into Database Class for SQL use

Question

I'm developing a FlashCard App. Part of this is giving the user the option to filter results on difficulty level and category (more options in the future probably). See the image for a quick mockup of the user screen.

The data for the flash cards are stored in a Derby DB in a single table. One column for Level. A column for each category with a "Y" or "N" holding whether the card falls into that category.

Currently, I have a Filter class that just has a variable for each checkbox. I then pass this to the Database class. But from there it is a complex set of if/else statements to build the correct SELECT statement query.

1) Is there a better way to design this?
2) Is my 1 table approach doomed or is it fine in this case? What pitfalls am I walking into?

Answer 1

The old adage is that in computer science there are three numbers -- zero, one, or infinity. Instead of making a boolean column per level, I would suggest an enum or integer column for level, and either a category table and a JOIN between the two using foreign keys to allow questions to be linked to one or more category, or a "category" column which also uses enums or integers. This way, when you add/remove/rename a new category or level, you are not modifying your database schema. It is independent from the data it contains.

---

Also, this will greatly simplify your SQL queries. Compare:

SELECT * FROM questions WHERE category1 = false AND category2 = false AND category3 = false ... and (level = 1 OR level = 2);

with

SELECT * FROM questions WHERE category = 1 AND level = 2;

or

SELECT * FROM questions WHERE category IN (1,3,6) AND level in (1,2);

Answer 2

In situations like this in the past I have created an integer column that bitwise operations can be performed against. Explanation below:

Begin by assigning a single binary digit to each value-

cat1  cat2  cat3 cat4
----  ----  ---- ----
1     2     4    8

Next you will add an integer column to your main table, we will call it options. When the number is converted to binary each digit will represent weather categories 1, 2, 3 or 4 are set. Example:

5 = 0101 in binary = cat1 is set, cat2 is not set, cat3 is set, cat4 is not set

id | name         | options
---------------------------
1  | name1        | 5
2  | name2        | 2
3  | name3        | 7
4  | name4        | 6

We can now use bitwise operations against the options column to determine what options are allowed. Examples:

To get all records that have category 2 set when we don't care about the other categories, perform the following operation:

2 & options = 2

This would return records 2,3 and 4.

---

To get all records that have cat2 and cat3 set we would perform the following bitwise operation:

6 & options = 6

This would return records 3 and 4

---

To get all records that have category 1 and 3 set we would perform the following bitwise operation:

5 & options = 5

This would return records 1 and 3.

---

ONLY category 3 set:

4 | Options = 4

---

Category 3 NOT set:

4 & options = 0

---

This is probably a hard concept to grasp so please let me know if you have any questions. It seems to me that it might be the simplest way to accomplish what you are trying to do once you can grasp the concept.