From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: "'Andreas'" <maps.on@gmx.net>,
	<pgsql-sql@postgresql.org>
Subject: Re: design of tables for sparse data
Date: Mon, 12 Nov 2007 18:48:19 -0300
Organization: IP-TEL
Message-ID: <0c8501c82575$bd3cf830$8f01010a@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: Acgi1hYmMV60V+XcTzm/HggxPSdwDgCkRnvw
In-Reply-To: <4734632D.2010400@gmx.net>


> --- Andreas Wrote: ---
> ...
>
> MY QUESTIONS:

Your questions have a strong "home-work" look. 

> 
> 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)
> ...

You should check out the JOIN clause in select statements. 
Simple example:

Select t1.col1, t2.col1, t2.col2
from t1 inner join t2 b on (t1.col1 = t2.col1)

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

You specify the order of output columns in the select statement. If you want
to do this dynamically (say each user wants to configure its own order) you
are really better of programming in your front-end application. No trivial
solution in a pure SQL solution. It would probably require some dynamic sql
and another table which holds de column printout order.

> 3)   Could I restrict the classes list so that only those appear when
> there are pupils actually attending them in a given time frame?
> 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
> 

Yes, you could. Read about different JOINS and WHERE clauses. ;)


Regards,
Fernando.