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);
