Many to one relationship

Swillsy

Registered User.
Local time
Today, 10:10
Joined
Jun 10, 2008
Messages
68
Hi Guys & Gals

Someone has just pointed out a fatal flaw with one of my table designs involving incident reporting.
Current Structure:

tblStudents
StudentID
StudentName
etc

tblIncidents
StudentID
IncidentID
IncidentType
etc

At present the StudentID is linked on tables which means that the users can report an incident involving a person. However if more than one person is involved then they would have to repeat the process.

My question is how would I go about having a single incidentID and multiple StudentIDs.

I am guessing I would need a one to many relationship in my design- but how would I go about setting this out on the form i.e. studentID2, studentID3???

Thanks

Swillsy
 
Because 1 student can be involved in many incidents and more than 1 student can be involved in a single incident there is a many to many relationship. You need to model this using a junction table so your data structure would look like this

tblStudents
StudentID
StudentName
etc

tblIncidents
IncidentID
IncidentType
etc

TblStudentsIncidents
StudentId
IncidentID
etc
 
Thanks for your reply

I understand how the db table setup would look like
I am struggling to see how this could be setup on a form - I'm not sure if its possible tbh.
For example if 2 students were having an arguement - at present I would input 2 records with 2 ids via the form
Ideally I would like to setup a text box so I could enter the 2 ids on the record and they would be linked to the Students IDs.

I'm gonna go think about this over more coffee
 

Users who are viewing this thread

Back
Top Bottom