From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: design of tables for sparse data
Date: Fri, 09 Nov 2007 14:39:57 +0100
Message-ID: <4734632D.2010400@gmx.net> (raw)
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.
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?
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
Regards
Andreas
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: maps.on@gmx.net
Subject: Re: design of tables for sparse data
In-Reply-To: <4734632D.2010400@gmx.net>
* 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