RE: Views...

From: "Christian Marschalek" <cm(at)chello(dot)at>
To: "'Joel Burton'" <jburton(at)scw(dot)org>
Cc: "[GENERAL] PostgreSQL" <pgsql-general(at)postgresql(dot)org>
Subject: RE: Views...
Date: 2001-04-21 02:38:28
Message-ID: 000001c0ca0c$261e1d60$0200a8c0@server
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)scw(dot)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(at)scw(dot)org>
> Director of Information Systems, Support Center of Washington
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Rob Yampolsky 2001-04-21 02:40:35 Re: How to configure iodbc access to local postgres db?
Previous Message Thomas Lockhart 2001-04-21 01:43:24 Re: Hardcopy docs available