Re: design of queries for sparse data

On Nov 11, 2007 1:09 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> Hi,
>
> I need some help to improve my design skills. :)
>
> I lately read an article about table design, that teached one shouldn't
> designe tables where it's clear that some columns aren't relevant for
> every row. It didn't span into the dirty usage details beyond the table
> design, though.
>
> E.g. a really simple example like a school that stores pupils like this:
> pupil (pupil_id, pupil_name, attends_english, attends_history,
> attends_maths, attends_football, attends_swimming)
>
> 1) Some pupils don't attend to football, swimming or both.
> 2) Occasionally there will be new classes added and others get
> dropped. Say in a year a column "attends_knitting" gets introduced. Now
> all those 50,000 existing rows get a column where the person hadn't even
> the occasion to apply.
> If for some reason the knitting class gets discontinued every row in the
> future will still get this column.
>
> So it was better to create 3 normalized tables:
> pupil (pupil_id, pupil_name, start_date, exit_date)
> classes (class_id, class_name, is_available, output_order)
> attends_to (pupil_id, class_id, in_year) as an n:m-relation
>
> Fine. Now I got rid off those empty columns in the pupil table.
>
>
> MY QUESTIONS:
>
> 1) How would I SELECT a report that looks like the first version of
> the pupil table out of the 3 table design?
> There must be a nontrivial SELECT statement that combines all 3 tables.
> E.g. I want the result:
> pupil_id, pupil_name, attends_to_english, ....., attends_to_football,
> attends_to_swimming, attends_to_knitting
> (42, Frank Miller, yes, ...., no, yes, yes)
> (43, Suzy Smith, yes, ..., yes, yes, no)

This type is called a crosstab query. There are several ways to
accomplish this, but adding a class will result in having to change
the query, generally. However, you can easily construct these results
in your client application. For flexibility reasons, it might be best
to do things this latter way.

> 2) Could I control the order in which those attends_to-columns appear
> by a numerical field output_order?

See the answer to number 1. This is most flexibly done in a client application.

> 3) Could I restrict the classes list so that only those appear when
> there are pupils actually attending them in a given time frame?

You could use SQL to get the list of relevant classes and then use
those in your client application to build the appropriate
representation of the data.

> 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
> 3) b) Or it is availlable but no one has chosen it in 2007. -->
> attends_to.in_year