On Fri, 20 Apr 2001, Christian Marschalek wrote:
> Let's say I have 3 Tables... Teacher,Class,Pupil.
> Now if I want to have the lessions easily accessable I could make
> another Table called lessions with some attributes from
> Teacher,Class,Pupil combined.
CREATE TABLE Teach (
tid serial not null primary key,
teachname text not null,
teachsalary float not null
);
CREATE TABLE Class (
cid serial not null primary key,
classtitle text not null,
classcost float not null
);
CREATE TABLE Pupil (
pid serial not null primary key,
pupilname text not null,
);
If you want to show which pupil took which class with which instructor,
you could create a new table
CREATE TABLE Lessons (
tid int references teach,
cid int references class,
pid int references pupil
);
and insert some data
However, getting information from Lessons isn't very pretty --
you just see the id numbers for classes, pupils, etc.
A view could create a joined version of this, letting you see more
information about the relationship of these tables.
CREATE VIEW lessons_view AS
SELECT t.*,
c.*,
p.*
FROM lessons l,
teach t,
pupil p,
class c
WHERE l.cid = c.cid
AND l.pid = p.pid
AND l.tid = t.pid;
Now, you can *treat* lessons_view as a table for SELECTs -- that is, you
can just select from it and get this nicer view of your data.
For extra credit, you can set it up so that you can insert/update/delete
from this view, and have this happen to the source tables. Read the
documentation on rules for more info.
Good luck and HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington