This is an interesting question. I think its related to a bunch of other problems like the recipe problem, and the scrabble/crossword problem. You're probably right that this isnt suited to InDB work, except for one aspect of what you said.

The data is compiled off-line. Once compiled, it never changes, so the speed of inserts, updates, deletions, etc are of absolutely no concern.

Going from this to assuming that H/W assets like disk size arent a big problem for you, then I would find out the maximum number of indexes for the tables you are using and then create enough tables and enough indexes that every single column is indexed. Then you would need to have a "precompile" stage before actually running your reports which would convert the actual requirements into the correct SQL select statement to find the data. (Ie, if they want responseX you need to know that its field N of table T). Since each of the columns would be indexed and you would only be joining the columns you need it would be like: 

select count(resp_id) from 
table1,table5,table32,table63
where
table1.resp_id=table5.resp_id
and table5.resp_id=table32.resp_id
and table32.resp_id=table63.resp_id
and table1.field3=1 and table1.field8=1
and table5.field16=1 and table32.field7=1
and table63.resp_id=1

[download]

Since it would stay totally in index I should think the performance would be quite good for the tradeoff of using vast disk space. :-)

Anyway, no matter how you actually build your reports I would probably be looking at some kind of in DB representation as well. Having both can be really useful, especially if you can go from one to the other. :-)

BTW a last thought is try not to think MySQL = RDMBS or DBMS. Its a common mistake in these parts that isnt always helpful.

Oh, um, also why do you reply to yourself and not to the replies? It seems like some of your replies are incorrectly parented, and I cant think why. You should make sure you are replying to the correct thing so people can follow the conversation.


---
demerphq

    First they ignore you, then they laugh at you, then they fight you, then you win.
    -- Gandhi

    Flux8


In reply to Re: Basic Perl trumps DBI? Or my poor DB design? by demerphq
in thread Basic Perl trumps DBI? Or my poor DB design? by punch_card_don

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.