pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow would i do this?
6+ messages / 6 participants
[nested] [flat]
* How would i do this?
@ 2001-12-11 19:21 John Hughes <johughes@shaw.ca>
2001-12-11 20:09 ` Re: How would i do this? Andrew Snow <andrew@modulus.org>
2001-12-11 20:16 ` Re: How would i do this? David A Dickson <davidd@saraswati.wcg.mcgill.ca>
2001-12-11 20:26 ` Re: How would i do this? Vince Vielhaber <vev@michvhf.com>
2001-12-11 21:47 ` Re: How would i do this? Shaun Thomas <sthomas@townnews.com>
2001-12-12 07:23 ` Re: How would i do this? Arian Prins <prinsarian@zonnet.nl>
0 siblings, 5 replies; 6+ messages in thread
From: John Hughes @ 2001-12-11 19:21 UTC (permalink / raw)
To: pgsql-general@postgresql.org.pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How would i do this?
2001-12-11 19:21 How would i do this? John Hughes <johughes@shaw.ca>
@ 2001-12-11 20:09 ` Andrew Snow <andrew@modulus.org>
4 siblings, 0 replies; 6+ messages in thread
From: Andrew Snow @ 2001-12-11 20:09 UTC (permalink / raw)
To: 'John Hughes' <johughes@shaw.ca>; +Cc: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How would i do this?
2001-12-11 19:21 How would i do this? John Hughes <johughes@shaw.ca>
@ 2001-12-11 20:16 ` David A Dickson <davidd@saraswati.wcg.mcgill.ca>
4 siblings, 0 replies; 6+ messages in thread
From: David A Dickson @ 2001-12-11 20:16 UTC (permalink / raw)
To: John Hughes <johughes@shaw.ca>; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How would i do this?
2001-12-11 19:21 How would i do this? John Hughes <johughes@shaw.ca>
@ 2001-12-11 20:26 ` Vince Vielhaber <vev@michvhf.com>
4 siblings, 0 replies; 6+ messages in thread
From: Vince Vielhaber @ 2001-12-11 20:26 UTC (permalink / raw)
To: John Hughes <johughes@shaw.ca>; +Cc: pgsql-general@postgresql.org
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
==========================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How would i do this?
2001-12-11 19:21 How would i do this? John Hughes <johughes@shaw.ca>
@ 2001-12-11 21:47 ` Shaun Thomas <sthomas@townnews.com>
4 siblings, 0 replies; 6+ messages in thread
From: Shaun Thomas @ 2001-12-11 21:47 UTC (permalink / raw)
To: John Hughes <johughes@shaw.ca>; +Cc: PostgreSQL general list <pgsql-general@postgresql.org>
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-15...
http://www.amazon.com/exec/obidos/ASIN/1558605002/qid=1008106745/br=1-1/ref=br_lf_b_1/104-0323785-15...
http://www.amazon.com/exec/obidos/ASIN/0123264251/qid=1008106745/br=1-6/ref=br_lf_b_6/104-0323785-15...
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| 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 |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How would i do this?
2001-12-11 19:21 How would i do this? John Hughes <johughes@shaw.ca>
@ 2001-12-12 07:23 ` Arian Prins <prinsarian@zonnet.nl>
4 siblings, 0 replies; 6+ messages in thread
From: Arian Prins @ 2001-12-12 07:23 UTC (permalink / raw)
To: pgsql-sql
Hello John,
You would really have to read-up on relational theory. There is a lot to be
found on the web. And if a take a peek at our books I see: Date, C.J., An
Introduction to DATABASE SYSTEMS.
But for a quick explenation: think in relations. Think in on of these posible
relations:
x has ZERO or ONE y (example in a hospital setting: a patient x has zero or one
cause of death y)
x has ZERO to MORE y (example: a student x has followed zero to more classes y)
x has ONE to MORE y
x has ZERO to MORE y, AND, y has ZERO to MORE x (example teachers x that teach
to students y.... a teacher can have multiple students and a student can have
multiple teachers).
One piece of your example can be modeled like this:
A Class is comprised of students:
- you'd have a table that identifies classes:
class (class_id, class_name, ..other items that you'd like to record....)
- and a table that identifies students:
student (student_id, student_name, student_adres.............)
- and a table that lists which student attends which classes:
students_attending_classes (students_attending_classes_id, class_id,
student_id,......... )
Because the last table has multiple rows with one student in it, a student can
attend multiple classes, and a class can be attended by multiple students. If a
student can attend _only_ one class then you'd put the class_id in the
student-table.
But... there is A LOT to know... You can normalise into the 3rd, 4th or 5th
form, etc. etc. etc so I'd suggest you'd find some sites / literature.
Succes,
Arian.
John Hughes schreef:
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-12-12 07:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-11 19:21 How would i do this? John Hughes <johughes@shaw.ca>
2001-12-11 20:09 ` Andrew Snow <andrew@modulus.org>
2001-12-11 20:16 ` David A Dickson <davidd@saraswati.wcg.mcgill.ca>
2001-12-11 20:26 ` Vince Vielhaber <vev@michvhf.com>
2001-12-11 21:47 ` Shaun Thomas <sthomas@townnews.com>
2001-12-12 07:23 ` Arian Prins <prinsarian@zonnet.nl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox