Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Mailing Lists: postgresql.org > pgsql-novice > 07 > 110563.html (Request Expert postgresql.org Support)

[NOVICE] design of queries for sparse data

From: Andreas <maps.on(at)gmx.net>
Date: Sun Nov 11 2007 - 13:09:59 EST


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)
    ...
  2. Could I control the order in which those attends_to-columns appear by a numerical field output_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?
  4. 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
  5. b) Or it is availlable but no one has chosen it in 2007. --> attends_to.in_year

Regards
Andreas 

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Received on Sun Nov 11 13:10:32 2007
Do you need help?X

This archive was generated by hypermail 2.1.8 : Thu Jun 19 2008 - 00:03:48 EDT

Contact Us  Legal Notices  Order Services Online 
Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library