Thread: Views...

Hi!

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.

I could also just make a view, couldn't I?

Where's the difference?

Thanks for your time in advance!

Yours, Chris

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

It was just an example...

My point was, that I would like to know which one is better? We've done
some examples of data modeling in school and when we had to combine some
tables we did that by creating a new one... For example lessions in a
school or sellings at a car seller or what ever

Now that I heard of views and understood them as so to say "saved"
queries I wonder which aproach is better? The new table one or a view.
(Also I'm right now designing a data model in Visio2000 and there I have
the choice of using a table or a view... So I wanted to know which is
better, before I continue working on the model) Of course, there many
situations where you can't use a view and have to use a table but if I
can choose which one shall I take? :o)

thanks for your help

Chris

> -----Original Message-----
> From: Joel Burton [mailto:jburton@scw.org]
> Sent: Friday, April 20, 2001 11:28 PM
> To: Christian Marschalek
> Cc: [GENERAL] PostgreSQL
> Subject: Re: Views...
>
>
> 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
>