Re: design of queries for sparse data

Hi!

Andreas wrote:
> 1) How would I SELECT a report that looks like the first version of
> the pupil table out of the 3 table design?
I agree with Sean that such a crosstab would best be created in the
application. What you get from the db would be something like:

42, Frank Miller, Knitting
42, Frank Miller, Basketball
43, Suzy Smith, Wrestling
43, Suzy Smith, House Cleaning

(and that's keeping it to one query... Would probably be more efficient
to split)
Like that you simply loop over the rows concerning the "same" student
and add classes to your structure.
> 2) Could I control the order in which those attends_to-columns appear
> by a numerical field output_order?
As soon as you have "out-normalized" classes, that table may have an
output_order, of course.
> 3) Could I restrict the classes list so that only those appear when
> there are pupils actually attending them in a given time frame?
Using an MN relation between students and classes, of course
> 3) a) Like "competitve knitting" was only available from 2000-2005.
> Now I'd produce a list of 2007 so there shouldn't appear an empty
> knitting-column. --> classes.is_availlable
is_available seems not a wise choice. You would probably prefer a
temporal structure (Ask yourself in 2007: Did Suzy attend House Cleaning
in 2004? Was Knitting available back then?). Why don't you split
"classes" even further: a) an abstract definition of the contents/title
and b) the concrete class by year, referring the abstract definition
(and the professor of that year).

HPO