Re: How would i do this? - Mailing list pgsql-general

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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+