query or schema question

From: Scott Frankel <leknarf(at)pacbell(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: query or schema question
Date: 2004-12-15 03:23:31
Message-ID: B1D2D3B0-4E48-11D9-A37F-000A95A7B782@pacbell.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


My first schema design has passed all the tests I've thrown it so far,
'cept one -- and a
simple one at that. I wonder if the following boils down to a question
of query construction
or if I need to redesign my schema.

Consider the (contrived) example of 3 universities, where each hosts a
*unique* list of
departments (very contrived). Now populate the universities with
students.

#
# Query: list all the students attending university XXX.
#

Schema:
CREATE TABLE universities (uni_pkey SERIAL PRIMARY KEY, uni_name text);
CREATE TABLE departments (dpt_pkey SERIAL PRIMARY KEY, dpt_name text,
uni_pkey int REFERENCES universities);
CREATE TABLE students (stu_pkey SERIAL PRIMARY KEY, stu_name text,
dpt_pkey int REFERENCES departments);

Note that since I created the connection from
university-->departments-->students, I thought I
could design a query that would return the info requested above without
spiking-off a reference
from the students table directly back to the universities table. Well,
it seems *I* can't ;)

So, which is better -- or possible? A quick fix to the schema,
referencing uni_pkey in the
students table? Or is there a reasonable way to traverse the
dependencies from the students table
back to the universities table?

Thanks heartily in advance!
Scott

[ here's my sql, pre-baked; note that each university hosts a *unique*
set of departments
in this most-contrived example ]

CREATE TABLE universities (uni_pkey SERIAL PRIMARY KEY, uni_name text);
CREATE TABLE departments (dpt_pkey SERIAL PRIMARY KEY, dpt_name text,
uni_pkey int REFERENCES universities);
CREATE TABLE students (stu_pkey SERIAL PRIMARY KEY, stu_name text,
dpt_pkey int REFERENCES departments);

INSERT INTO universities (uni_name) VALUES ('cal');
INSERT INTO universities (uni_name) VALUES ('stanford');
INSERT INTO universities (uni_name) VALUES ('ucla');

INSERT INTO departments (dpt_name, uni_pkey) VALUES ('art', 1);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('physics', 1);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('oceanography',
1);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('math', 2);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('chemistry', 2);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('geography', 2);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('design', 3);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('geology', 3);
INSERT INTO departments (dpt_name, uni_pkey) VALUES ('archeology', 3);

INSERT INTO students (stu_name, dpt_pkey) VALUES ('maria', 1);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('ed', 1);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('brian', 2);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('claire', 2);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('samantha', 2);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('siobhan', 2);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('pilar', 3);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('george', 3);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('nick', 3);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('bruce', 4);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('estelle', 5);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('harry', 6);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('rocio', 6);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('jose', 7);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('steve', 8);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('henry', 8);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('chris', 9);
INSERT INTO students (stu_name, dpt_pkey) VALUES ('john', 9);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Aaron 2004-12-15 03:23:39 Unable to read data from the transport connection.
Previous Message Tim Vadnais 2004-12-15 01:15:08 VACUUM FULL [ANALYZE] problem