Relationship design (1 Viewer)

[optidisk]

I am designing a db from an excel file that a volunteer org uses, i am the treasurer and I believe what they do in the spreadsheet would be easier in Access. I am familiar with tables, queries etc.
Where I am getting stuck is the relationships and getting it to work properly.
The database would need these tables based on the attached spreadsheet;
Students, Tutors, Workshops, Events, Student Payments, Tutor Payments.
NOTE: check the comments in each sheet in the spreadsheet for how the sheets work, but it is self explanatory.
I think the db would need to be based on the students and from that the workshops and events that they attend, the workshops need to be linked to the tutors, where it gets tricky is that tutors can also attend events.
Basically it would be a registration database, I've looked at the template from Microsoft.com called event management and it is sort of what needs to be done but not quite.
Any suggestions would be greatly appreciated.

Cheers
Optidisk

 

[Pat Hartman]

Students and tutors are both people so they should be in a single table. Workshops are events so they should be in the event table. And finally since students and tutors are both now in a single table, there is no need for separate payment tables. To connect all this together, you will need some relation tables. You need a table that relates students to tutors.

tblStudentTutor:
StudentTutorID (autonumber primary key)
StudentID (foreign key to tblPerson.PersonID)
TutorID (foreign key to tblPerson.PersonID)
StartDate
EndDate
...etc.

tblPersonEvent:
PersonEventID (autonumber primary key)
PersonID (foreign key to tblPerson.PersonID)
EventID (foreign key to tblEvent.EventID)
Role (Attendee or Leader, etc.)

tblPersonPayment:
PersonPaymentID (autonumber primary key)
FromPersonID (foreign key to tblPerson.PersonID)
ToPersonID (foreign key to tblPerson.PersonID)

That should get you thinking.

 

[optidisk]

Thanks Pat, that will certainly give me something to think about over the weekend.
I'll post an example if I get it working.

Cheers
optidisk