Thread: How would i do this?
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? John Hughes
You'll probably end up forgetting what you know about arrays: I recommend a read through this to get a quick understanding of how things are done in the RDBMS world: http://www.google.com/search?q=cache:-Y8EjJKZgKk:w3.one.net/~jhoffman/sq ltut.html&hl=en#Joins (original site seems to have vanished) Once you have a handle on that, read the PostgreSQL docs for primary/foreign keys and referential integrity. Hope that helps - Andrew > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John Hughes > Sent: Wednesday, 12 December 2001 6:22 AM > To: pgsql-general@postgresql.org.pgsql-sql@postgresql.org > Subject: [GENERAL] How would i do this? > > > I am new to DB programming, so im not too sure about how to > implement my DB. > > 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.
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 ==========================================================================
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. I would create a each of the 4 tables as listed above with a unique id for each student/class/teacher/assignment. I would then make a table teacher_join_class which with two fields: teacher_id and class_id. Whenever you want to see which classes a teacher is in you would do SELECT class_id FROM class WHERE teacher_id = X; where X is your teachers id. The same can be done for your other tables. You just need to be sure that when you delete a teacher_id from teachers it gets deleted from the teacher_join_class. This can be done using triggers. -- David A Dickson david.dickson@mail.mcgill.ca
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
As I'm sure you've seen there are about a million ways to do this :) Personally, I'd start by looking at the relationships between the different objects here. Ask some questions, such as "is ther more than one teacher per class?" and "do the students get assignments, or do the classes get assignments?" Figure out the different relationships of your "things," in terms of many-to-one, one-to-one, and many-to-many. In this case I'm making these assumptions: one teacher per class many students per class, but students can have multiple classes many assignments per class, and they "belong" to the classes and it would seem like class is really the central thing here. Your class table would have a reference to a teacher id in this case, assignments would have a reference to a class id, and students would be tied to classes by an 5th associational table which would simply have student and class ids on it. If you want to track student performance on the assignments, you would need still another table, which would associate student ids, assignment ids (assuming they are unique), and grades (or whatever your performance indicator is). Databases are all about relationships. If you want to learn this quickly I highly recommend bruce's postgresql book. He is a very talented teacher, and gets you up and running without wasting words. Regards, Mike >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? _________________________________________________________________ Chat with friends online, try MSN Messenger: http://messenger.msn.com
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 ==========================================================================
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.
So, you're either trying to make us do your homework from a class, or
don't really understand object hierarchies. You also seem to have no
comprehension that your question is DB agnostic, and therefore not
really related to postgresql. Not to mention the fact that if you
don't know how to do something this simple, you should really grab
a book on basic data structures and database design before you even
think you have any business designing a database for any professional
application.
But I'm bored, so I'll be nice... Let's see...
> 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.
You don't say what assignments are associated with, so I'll just
assume they're associated with students.
Here's a basic diagram giving the bare minimum of what you want:
------------- ------------- -------------
| Student | | Class | | Teacher |
|-----------|n 1|-----------|1 1|-----------|
| studentid |----------| classid |----------| teacherid |
| classid | | ... | | classid |
| ... | ------------- | ... |
------------- -------------
1|
|
n|
----------------
| Assignment | Obviously, the ...'s mean you can add other
|--------------| descriptive columns. The "n" and "1"'s describe
| assignmentid | the number of relations. So there is an n to 1
| studentid | relationship from student to class, meaning
| grade | multiple students per class.
| ... |
----------------
To tell the truth, though... this is far too elementary. A class is not
really an inherent attribute of teacher or student - and notice too, that
students can't be in multiple classes without replicating student data.
you'll need more tables, really. So, let's rewrite this in a more
professional manner taking scalability and inherent attributes into
consideration.
------------------- ------------- -------------------
| student_class | | Class | | teacher_class |
|-----------------|n 1|-----------|1 n|-----------------|
| studentid |----------| classid |----------| teacherid |
| classid | | studentid | | classid |
------------------- | ... | ------------------|
n| ------------- n|
| 1| |
1| | 1|
------------- n| -------------
| Student | ---------------- | Teacher |
|-----------| | Assignment | |-----------|
| studentid | |--------------|n 1| teacherid |
| ... | | assignmentid |------------| ... |
------------- | classid | -------------
1| | teacherid |
| | ... |
n| ----------------
---------------- 1|
| Homework | |
|--------------|n |
| homeworkid |-----------------|
| assignmentid |
| studentid |
| ... |
----------------
Now, teachers can teach multiple classes, or classes can have multiple
teachers (think semesters and such). Teachers can create assignments
that are associated with a particular class, and instances of that
assignment can be made specific to each student without duplication
(homework). Students can take multiple classes at once without
duplicating their data either. This system also lets you use the
swing tables to store grades for archive purposes, and introduce other
ways of relating this data - say a teacher can also be related to a
student as an advisor, so you'd want an advisor table that did this.
You'll find that the above relation does not really translate to OOP
very well due to the swing tables and the very nature of relational
databases as opposed to object oriented databases. Postgres is a
relational database, hence your idea of treating this like a C++
problem is on the wrong track. Remember, just because the only tool
you know how to use is a hammer, doesn't mean everything is a nail.
If you barely understand the above, stop reading right now and go
to a bookstore and grab something that mentions "database design".
Pay special attention to how keys and indexes work, and any given
examples of relations. It's not uncommon to start with something
simple like an address-book. Otherwise you're just making your life
a lot harder than it needs to be. Always research before starting
a project, and this is the research you'll need to do.
Here are a few helpful links...
http://www.citilink.com/~jgarrick/vbasic/database/fundamentals.html
http://www.amazon.com/exec/obidos/ASIN/0201694719/qid=1008106676/br=1-8/ref=br_lf_b_8/104-0323785-1539101
http://www.amazon.com/exec/obidos/ASIN/1558605002/qid=1008106745/br=1-1/ref=br_lf_b_1/104-0323785-1539101
http://www.amazon.com/exec/obidos/ASIN/0123264251/qid=1008106745/br=1-6/ref=br_lf_b_6/104-0323785-1539101
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas@townnews.com AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
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