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

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