How can I find one of many possible patterns among a single mySQL entry? More inside.

Question

I had a hard time summing up my question. Basically:

There's a table called "files". Files holds an entry called "grades". It is used to identify the particular grade level a file might be useful for. Because a file can be useful for > 1 grade level, I store things like this

If it's only good for 3rd grade grades: 3

If it's good for 3rd, 4th and 5th: grades: 3,4,5

etc etc.

When putting together a SQL query to retrieve these files, I ran into a weird issue- Basically a user can say "I only want things that are good for 2nd and 3rd grade". So I should look for files that have "2,3" in the Grades area. Easy! BUT!

It could also have "1,2,3" or "2,3,4" or "2,4".

I;m getting a headache just thinking about it. It's easy enough to parse those entries via the commas to get "1" and "2", but what's the most efficient way to match a SQL record to the query? It seems like a waste to get EVERY RECORD in the DB, parse them down and then match them up again.

Is it better to go back to square one and create a DB called "files" and individual tables for each grade? That also seems like a waste- Writing multiple records for one file.

What's the solution here? I'm a little flummoxed.

Answer 1

several options here...

1) store the grades as an integer where each grade corresponds to a bit. grade 1 = bit 0, grade 2 = bit 1, grade 3 = bit 2, and so on. then grades 1,2,3 would correspond to 0x00000111 (8) and grades 2,4 would be 0x00001010 (10) etc; then querying becomes a simple matter of doing an AND comparison... if you want all rows where grades 2 and 4 are selected (and possibly others) then select * from files where (grades & 10) == true

2) if there are only a relatively few grades you could store each as a boolean column.

3) store the grades in a separate table and then the relationship between grades and files n a 3rd join table (since it is a many to many relationship).

Answer 2

To elaborate on what @emh said. Best option IMHO, would be having a grades table that connects to the files table on the file id (#3). You can then store the connection between grade and file in a new row each time (if the connection doesn't already exist)

tbl_file_grades
-----------
file_id
grade

When you're doing the search, you can join the two tables and filter the search by the grade column.

SELECT files.file_info FROM files
INNER JOIN tbl_file_grades ON files.file_id = tbl_file_grades.file_id
WHERE tbl_file_grades.grade = 1 AND tbl_file_grades.grade = 2 ...

I'm not sure whether the extra table for grades is necessary. That would depend on your needs. It seems like if you're happy without it now, then it isn't all that important to have.

And also, most important, welcome to SO.