Horton
Menu

Thread

  1. Re: How would i do this?

    wsheldah@lexmark.com — 2001-12-11T20:52:48Z

    
That works fine right up until the student wants to sign up for her second
class. There needs to be an intersection table that just stores class-student
combinations:

create table class-student (id serial primary key, student_id integer, class_id
integer);

with appropriate indexes. Then each student can take as many classes as they
like, a class can have as many students enrolled as the school allows, but you
don't waste space if they take fewer classes or have a low enrollment. And it
stays normalized. Etc. But like the others said, there's lots of literature
about this, most of it starting with the teacher-class-student situation, so
you're in luck.  :-)

Wes Sheldahl




Vince Vielhaber <vev%michvhf.com@interlock.lexmark.com> on 12/11/2001 03:26:09
PM

To:   John Hughes <johughes%shaw.ca@interlock.lexmark.com>
cc:   pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
      Sheldahl/Lex/Lexmark)
Subject:  Re: [GENERAL] How would i do this?


On Tue, 11 Dec 2001, John Hughes wrote:

> I am new to DB programming, so im not too sure about how to implement my DB.
>
> Here is what i need: a DB with tables of:
>
> 1. Students.
> 2. Classes
> 3. Teachers
> 4. Assignments
>
> Each teacher can be assigned a class, which is compromised of a list of
> students. Each class can be given assignments.
>
> Coming from a programming background, I could do this in c++ very easily. I
> actually started inmplementing it in a similar fasion: each student class
> teacher ect would have a uniqe id in the database, and, for example, a
> class would include an array of integer student id's.
>
> This seems really error prone, and not very efficient.
>
> what is a better implementation?

Don't think array.  Look at it from the other direction.

Each class has a name (prog101) a room#, teacherid, etc.  and a class id.
Each student has a name, etc. and the id of the class they're taking.

Then:  select name from student where classid = 4;

Where 4 could be the id for prog101.

That's the REALLY simplified version, but you get the idea.


Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

  2. Re: How would i do this?

    Vince Vielhaber <vev@michvhf.com> — 2001-12-11T21:20:35Z

    On Tue, 11 Dec 2001 wsheldah@lexmark.com wrote:

>
>
> That works fine right up until the student wants to sign up for her second
> class. There needs to be an intersection table that just stores class-student
> combinations:

I'm guessing you missed this part?

"That's the REALLY simplified version, but you get the idea."

I wasn't about to design the whole thing for him, nor did he
want or ask for that.


Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

  3. Re: How would i do this?

    Chris Albertson <chrisalbertson90278@yahoo.com> — 2001-12-11T21:45:35Z

    
I'd call this student-cross-class table "enrollments" as it
records when a student enrolls in a class.  You may want a 
trigger on insert on it.  For example if the sudent is already
enrolled in the same class or another class the meets at an over
lapping time.  One thing I whished for when going to school
at a large university was a check that back to back classes
where within close enough distance so you would not always be
late to the second class.  I think I had 10 minutes to walk
over a mile once.


--- wsheldah@lexmark.com wrote:
> 
> 
> That works fine right up until the student wants to sign up for her
> second
> class. There needs to be an intersection table that just stores
> class-student
> combinations:
> 
> create table class-student (id serial primary key, student_id
> integer, class_id
> integer);
> 
> with appropriate indexes. Then each student can take as many classes
> as they
> like, a class can have as many students enrolled as the school
> allows, but you
> don't waste space if they take fewer classes or have a low
> enrollment. And it
> stays normalized. Etc. But like the others said, there's lots of
> literature
> about this, most of it starting with the teacher-class-student
> situation, so
> you're in luck.  :-)
> 
> Wes Sheldahl
> 
> 
> 
> 
> Vince Vielhaber <vev%michvhf.com@interlock.lexmark.com> on 12/11/2001
> 03:26:09
> PM
> 
> To:   John Hughes <johughes%shaw.ca@interlock.lexmark.com>
> cc:   pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
>       Sheldahl/Lex/Lexmark)
> Subject:  Re: [GENERAL] How would i do this?
> 
> 
> On Tue, 11 Dec 2001, John Hughes wrote:
> 
> > I am new to DB programming, so im not too sure about how to
> implement my DB.
> >
> > Here is what i need: a DB with tables of:
> >
> > 1. Students.
> > 2. Classes
> > 3. Teachers
> > 4. Assignments
> >
> > Each teacher can be assigned a class, which is compromised of a
> list of
> > students. Each class can be given assignments.
> >
> > Coming from a programming background, I could do this in c++ very
> easily. I
> > actually started inmplementing it in a similar fasion: each student
> class
> > teacher ect would have a uniqe id in the database, and, for
> example, a
> > class would include an array of integer student id's.
> >
> > This seems really error prone, and not very efficient.
> >
> > what is a better implementation?
> 
> Don't think array.  Look at it from the other direction.
> 
> Each class has a name (prog101) a room#, teacherid, etc.  and a class
> id.
> Each student has a name, etc. and the id of the class they're taking.
> 
> Then:  select name from student where classid = 4;
> 
> Where 4 could be the id for prog101.
> 
> That's the REALLY simplified version, but you get the idea.
> 
> 
> Vince.
> --
>
==========================================================================
> Vince Vielhaber -- KA8CSH    email: vev@michvhf.com   
> http://www.pop4.net
>          56K Nationwide Dialup from $16.00/mo at Pop4 Networking
>         Online Campground Directory    http://www.camping-usa.com
>        Online Giftshop Superstore    http://www.cloudninegifts.com
>
==========================================================================
> 
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 
> 
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html


=====
Chris Albertson 
  Home:   310-376-1029  chrisalbertson90278@yahoo.com
  Cell:   310-990-7550
  Office: 310-336-5189  Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com