views:

46

answers:

2

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?

Sample Filter

+2  A: 

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);
Andy
I only have 1 column for level with an INTEGER. Using an enum or integer is an interesting idea. I certainly don't want to deal with a never ending growing column situation. -- So would a column hold a comma separated list of enum values?
Awaken
Does that handle the situation where 1 question (row) could have multiple numbers for the Category column (for instance contain "1,3"? Or would that require me to create a table for each category and then link them with keys?
Awaken
`SELECT * FROM questions WHERE category IN (1,3);` returns any rows that have category = 1 OR category = 3. The single column only allows 1 category per question. If you want to allow a question to belong to multiple categories, you will need a join table (3 tables total -- questions, categories, and questions_categories). Then, you would have a record in questions ("1", "What kind of bank is this?") and two category records ("1", "Misogyny"); ("2", "Idiocy"), and then two records in the join table, linking question 1 to category 1, and question 1 to category 2.
Andy
+1  A: 

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.

Abe Miessler
That is a solid idea. We do that sort of thing in embedded programming all the time. Obviously, there would be a limit of 32 or 64 categories depending on long or int, but there will have to be a limit anyway for GUI sake, so that would allow it to stay to a one table solution.
Awaken
I thought about this more. The case that is more important is when the user wants Cat 1 or Cat 3. In that case, you would just Bitwise AND with options and check for > 1 right? Can all this been done inside a SQL statement? Or will I have to return all the records and then step through them and apply the Bit Mask?
Awaken
I just checked it it doesn't look like Derby supports bitwise operations unfortunately. I have only used this method in SQL Server which does support bitwise ops, so you'll have to see if it still makes sense for your situation.
Abe Miessler
Abe Miessler