Large sets of results

[Crusader]

I've got about 5,000 integers that need to be checked if a user has seen before. These integers are grouped into different categories of varying size, so one category could contain 325 of the integers while another has 50 of them.

 

They're all unique integers and won't appear in any category more than once. Meaning that if it's in one category, it's only in that category.

 

Right now I have a table setup to contain the following:

 

user_id | category_1 | category_2 | category_3 | category_n... etc

 

In category_1 there's just a comma separated list of integers that the user has seen before.

 

eg. 1,2,3,5,6,7,111,9,10

 

This seems a bit clunky so I'm thinking about storing each individual integer seen in its own row. Kind of like this.

 

user_id | int_seen | category

 

The only problem I can think of is that there could be any number of user_ids... It'd probably be capped at 10,000 but that'd still be 50,000,000 entries in the table...

 

My solution to that would be clearing the table of a specific user_id's entries when they have seen all the integers and making another table that contains a list of users that have done this.

 

That and maybe having a group_id column in the original table so people in a group can share a result set instead.

 

What do you guys think? Any ideas or alternatives?

 

edit: hope that made sense

[kickstart]

Hi

 

If the user_id | int_seen | category table was properly keyed / indexed I would suspect the overhead of a 2nd read to check the table of those who have seen all catagories wouldn't be worthwhile.

 

All the best

 

Keith