Data Modelling & Joins
I'm coming back to FM after a few years away. It's moved on a lot since the last time I used it.
Can anyone help with my modelling, relationships and join tables as I'm struggling to figure out the best way to achieve what I want to achieve.
My tables are
- Clients - corporate entities
- Attendees - who are staff/employees of Clients and who attend Sessions
- Programmes - educational and training programmes that comprise different
- Sessions - generally two day events within a programme
- Staff - my staff who teach at the sessions - there are multiple staff at each session
- Clients have multiple Attendees
- Clients subscribe to multiple Programmes
- Programmes have multiple Sessions
- Sessions have multiple Attendees
- Sessions have multiple Staff members
At a later date we will also be adding surveys and questionnaires that Attendees will be asked to complete pre and post Session. But we'll ignore that for now.
I need to be able to report on
- which Attendees (and display related Client data) attended which Sessions (and therefore which Programmes)
- which Staff members lead which Sessions
I've tried creating join tables but am unsure where the chain terminates. I keep getting the error message "there cannot be more that one relational path between any two tables on the graph etc"
Can anyone suggest the best way to achieve what I want to do?
Screen grabs attached