This sounds like a bit of heavy lifting for a database server -- but not an impossibility. What I'm not getting is how often you have to regenerate this big table. Every time a web user wants to look at it? Every time something in the underlying database changes?

I would imagine you could generate and then cache the 'latest version' of the table at the end of every day, assuming new data is added throughout the day.

Hey TAlexb,

Ya, everytime a user wants to see it is the request. The idea being circulated right now is that various combinations of product_classes and industry_groups will be dreamed up for a report. There are likely to be ~6,000 of these reports. Yes, someone is going to dream up ~6,000 combinations of 350 intersections. Then a user goes to the list of reports and selects which he wants to see today.

And so, yes, I'm toying with the pre-generation option too, since once the data has been loaded, it never, ever changes. But at some point, you just know they're going to want to be able to generate every possible set and sub-set of intersections, so I think the pre-generation of static files would be a temporary delay of the inevitable pain.

So, all the info could have been put into one big 30,000 row x 12,000 
+column table ...
[download]

Your example query can be performed using a self-join like this:

SELECT a.company from CA as a, CA as b where a.company = b.company and
+ a.answer = 'group_a' and b.answer = 'product_class_c';
[download]

Note that this method allows you to add more questions without having to change the schema of the table.

I don't think you want a 12,000 column table.

On this, we are in complete agreement.

Just use an 'intersection' table which has just two columns, one for the company and the other for the question they answered in the affirmative. There would not be any rows for questions they answered 'no' to.

If I understand correctly if on average each company has answered 'yes' to ~500 questions, I'll have a two-column, 15-million row table?

And I'd still have 350 individual queries to run on this table, no?

If I understand correctly if on average each company has answered 'yes
+' to ~500 questions, I'll have a two-column, 15-million row table?
[download]

You're going to have 15 million somethings - you have a lot of data! This is one of the most efficient ways to represent a large sparse matrix. Also, having the data in this form will allow you answer so many other kinds of questions.

As I read your original problem statement, you are only are interested in the number of companies in each intersection. Moreover, it seems that you are only interested in cross-indexing company group by product class. I would create two intersection tables, one for the company group answers (call it CG), and another for the company product class answers (call it CP).

You'll still have the 15M rows, but it will be split between two tables.

Then you can do this with one query:

SELECT CG.answer as cgroup, CP.answer as pclass, count(*) FROM CG, CP
  WHERE CG.company = CP.company GROUP BY cgroup, pclass;
[download]

With the proper indexes, I think you'll be surprised at how fast this can be computed. Depending on the database you're using, you might want to do a LEFT JOIN instead of a regular join. You'll be able to tell by doing an explain on the different queries.

You can do it with the original schema, except that it would be harder to restrict the join condition to just company-group and product-class.