Re: 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.

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