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);
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
