Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    18

    Help with DB Design Required Please :)

    Hi All,

    I am creating my ERM for the initial design of my RELATIONAL database but I'm running into problems regarding my table relationships:

    The tour table will have the following entries:

    TourID (PK)
    StartDate
    EndDate
    PickUpPoint
    DropOffPoint
    StudentsTakingPart (List of Student IDs - Multiple Attributes so need to deal with this)

    The student table will have the following entries:

    StudentID (PK)
    SchoolID (FK)
    Forename
    Surname
    DOB
    AddressLine1
    AddressLine2
    AddressLine3
    Postcode

    Now my problem lies with the many to many relationship of many tours having many students and vice versa so I thought Id introduce a StudentGroup table with the following attributes:

    StudentGroupID (PK)
    StudentID (FK)

    hence changing the Tour table to:

    TourID (PK)
    StartDate
    EndDate
    PickUpPoint
    DropOffPoint
    StudentGroupID (FK)

    So now a Tour only has one Studentgroup but I'm still stuck with a M:M relationship between StudentGroup and Students as:

    - Many Student Groups may contain many students
    - Many Students may be in many Student Groups

    Please help me overcome this problem. Maybe I am misinterpreting my relationships ?

    Thanks in advance

    Greg

  2. #2
    Join Date
    Sep 2005
    Posts
    22

    Many to Many

    You need to uniquely identify student with tour
    eg:

    Tour
    ---
    TourID PK
    starts
    pickup
    ends
    drop_off

    Tour_Student
    ------------
    TourID
    StudentID
    PK(TourID,StudentID)

    Student
    -------
    StudentID PK
    ....

    Tour:Tour_Student 1:M
    Student:Tour_Student 1:M
    Tour_Student is unique for StudentY,TourZ

Posting Permissions

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