From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: 'Andreas' <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: design of tables for sparse data
Date: Mon, 12 Nov 2007 18:48:19 -0300
Message-ID: <0c8501c82575$bd3cf830$8f01010a@iptel.com.ar> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar, maps.on@gmx.net
Subject: Re: design of tables for sparse data
In-Reply-To: <0c8501c82575$bd3cf830$8f01010a@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox