Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Help with relational design

    Hi - New to database design here, and I've got a project that is stumping me. The real life situation is too complicated to get in to here, but I have a simple anology that fits very well indeed. Here is that analogy:

    I need a way of storing data relating to teachers, students, and the subjects that they teach and study. Sounds simple enough.

    Here are a few facts about the relationships between these parties:

    * Students learn multiple subjects.
    * Students have multiple teachers.
    * Subjects have multiple teachers.
    * Teachers have multiple subjects.
    * Teachers have multiple students.

    I would sum that up as a many to many to many relationship. Does that make sense?

    If so, how would I go about storing that data in a simple, scalable way. ie. the best way.

    So far I have the following tables:

    Students
    -----------
    student_id
    student_firstname
    student_lastname
    student_dateofbirth
    etc...

    Teachers
    -----------
    teacher_id
    teacher_firstname
    teacher_lastname

    Subjects
    ----------
    subject_id
    subject_name

    Can anyone give any suggestions about how to go about storing the information about which subjects teachers teach, and which subjects students study? Do I need link tables? What would they be called, and what data would they store?

    Feel free to ask any questions to clarify - I'll be listening. Thanks in advance.

    Andy.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    When a teacher engages as a subject, it is a class. Therefore you need to track classes:

    tblClass:
    class_id
    teacher_id
    subject_id

    When a student takes a subject, they are effectivelyt aking a class, so you need to track that in a seperate table:

    tblClassStudents
    class_student_id
    class_id
    student_id


    That should take you most of the way...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    2
    Ahh... something's just fallen into place. Thank you very much for that. I will go away and get my head around it properly with those new concepts to consider.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •