Relationships

[Sprocket]

Help this is doing my head in.

I have not set anything up yet as I can't work out how many tables I need and thus what the relationship should be.

Here's the problem:

I have a number of students with support needs. OK make this tblSTUDENT

I have a number of support workers. Let's make this tblSUPPORT .

Support workers are not allocated solely to one student --so:

we have TWO -- Many to Many -- relationships

Each support worker can help many students.
Each student can have many support workers.

Each day of the week has 8 possible teaching periods.

Therefore these could be M1, M2, through to ......F8

Now here comes a second set of Many - to - Many relationships

Each student will require support for many (but not all) periods.

Each support worker can cover many but not all teaching periods.
(some may only work mornings - some only one or two days per week)

I suspect I need TWO period tables - one to record which periods a support worker can cover and one to record which periods the student wants cover for.

Then I need to set up a many to many relationship bewteen the two period tables

BUT I'M NOT SURE HOW?


Am I on the right lines?

Can anyone show me a graphical picture of the required relationships as I think I would understand that best.

At the end of the day I need to be able to set up a query that can find any support worker who has availability if a student has to fit in an extra class. Or if one support worker is ill I need to find who can cover before going outside to recruit from a temp bureau.


Many thanks for you patience... in getting this far

Cheers.....Sprocket

 

[llkhoutx]

Use a junction tables to link the Man to Many:

tbStudents
StudentID
sStudentName
etc.

tbWorkers
WorkerID
sWorkerName
etc

tbSWJunction
StudentID
WorkerID

tbPeriod
PeriodID
sPeriod
etc.

tbSPJunction
StudentID
PeriodID

Querying tbSWJunction can give you all the students for a given worker or all the workers for a given student.

Similarily querying tbSPJunction can give you all the students for a given period or all the periods for a given student.

 

[jeremie_ingram]

When you encounter a situation of a many to many relationship, and association table is usually the best answer. Create a table that would contain the primary key from each of the tables, and link them accordingly. This should create two one to many relationships out of one many to many relationship.

You have the tblStudent, and tblSupport. Say they have primary keys of PKeyStudent and PKeySupport, now create a table that has two foreign key fields of PKeyStudent and PKeySupport. Include in this new table anything that would be specific to each of the support occurrences (Time, date, reason) and relate the tables through the Primary-Foreign key relationships. This should establish the one-to-many you are looking for.


Hope this is helpful