Views...

Поиск
Список
Период
Сортировка
Искать

Views...

От:
"Christian Marschalek" <cm@chello.at>
Дата:
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

RE: Views...

От:
"Christian Marschalek" <cm@chello.at>
Дата:
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   
> Director of Information Systems, Support Center of Washington
>

Re: Views...

От:
Joel Burton <jburton@scw.org>
Дата:
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   
Director of Information Systems, Support Center of Washington
FAQ