[SQL] design of tables for sparse data
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Temporal databases
Hi Philippe, Have a look at the post I made to the pgsql-sql list I made on 6th November 2007 (day before you posted this). It contains alot of stuff for temporal tables within Postgres. However temporal tables (when done properly) are a very complex subject at the best of times. It depends how much you want to integrate them. A good place to start is Joe Celko's book "SQL For Smarties" and Richard Snodgrass' book "Developing Time-Oriented Database Applications in SQL" - this book is no longer in print, but is downloadable (for FREE!) from: http://www.cs.arizona.edu/~rts/tdbbook.pdf Richard Snodgrass is one of the leading experts in the field. I warn you - the book is heavy going - but so worth it!! Keith On Wednesday 07 November 2007 13:22, Philippe Lang wrote: > Hi, > > Does anyone have experience, tips, links, regarding how to build > temporal databases with Postgresql? > > Thanks > > Philippe > > ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Select in From clause
I have never seen this done before, but it seems like it is supposed to work from reading the manual. I want to be able to get a table name from another table and use it in the from clause of a select. Something like SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.condition = xxx; which translates to something like SELECT * FROM Dealer AS b WHERE b.zipcode = 12345; The translated version works but the SELECT in FROM version reports that b.condition does not exist. ---(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
Re: [SQL] Select in From clause
Ray Madigan wrote: > I have never seen this done before, but it seems like it is supposed to work > from reading the manual. > > I want to be able to get a table name from another table and use it in the > from clause of a select. > > Something like > > SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.condition > = xxx; > > which translates to something like > > SELECT * FROM Dealer AS b WHERE b.zipcode = 12345; No, that's not how it works. The stuff returned by the inner select is a set of rows which can be further operated upon by the outer select. It is not expanded into a table name. One way to construct queries is to build plpgsql functions and use EXECUTE. However, the approach you are using looks like bad practice (read: bad database design). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] functions are returns columns
Hi all. I want that a function return a table rows (like the doc says at 33.4.4. SQL Functions as Table Sources), but I want the a function return only a few cols, so the same that I select into the func. Modifying the doc example: CREATE TABLE foo (fooid int, foosubid int, fooname text); INSERT INTO foo VALUES (1, 1, 'Joe'); INSERT INTO foo VALUES (1, 2, 'Ed'); INSERT INTO foo VALUES (2, 1, 'Mary'); CREATE FUNCTION getfoo(int) RETURNS foo AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; This give me an error: ERROR: return type mismatch in function declared to return foo DETAIL: Final SELECT returns too few columns. CONTEXT: SQL function "getfoo" So, how do it? Thanks, Michele ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] functions are returns columns
On 09/11/2007, Michele Petrazzo - Unipex srl <[EMAIL PROTECTED]> wrote: > Hi all. > I want that a function return a table rows (like the doc says at 33.4.4. > SQL Functions as Table Sources), but I want the a function return only a > few cols, so the same that I select into the func. > Modifying the doc example: > > CREATE TABLE foo (fooid int, foosubid int, fooname text); > INSERT INTO foo VALUES (1, 1, 'Joe'); > INSERT INTO foo VALUES (1, 2, 'Ed'); > INSERT INTO foo VALUES (2, 1, 'Mary'); > > CREATE FUNCTION getfoo(int) RETURNS foo AS $$ > SELECT fooid, foosubid FROM foo WHERE fooid = $1; > $$ LANGUAGE SQL; > > This give me an error: > > ERROR: return type mismatch in function declared to return foo > DETAIL: Final SELECT returns too few columns. > CONTEXT: SQL function "getfoo" > > CREATE FUNCTION getfoo(int) RETURNS foo AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1 LIMIT 1; $$ LANGUAGE SQL; or CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; try: SELECT * FROM getfoo(1); Regards Pavel Stehule > So, how do it? > > Thanks, > Michele > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
