How can I improve this data model so that I can use one filter for 2 different fields (that contain the same data)
Hello everyone,
I have two separate tables (Bookings and YL) containing data. The YL table holds the accommodation capacities for each school/gender, grouped by week/year. The Bookings table is much larger and contains all the student bookings. We have 3 common fields across the tables - date, school and gender.
The first issue was that the bookings data is interval based - [date from] / [date to] however we needed to be able to click on a date and see how many students we have on that particular date. To solve this, I've created a link table (Bookings_x_Dates) with one record for each possible date / enrolment ID and joined this to the master calendar. The YL/accommodation capacity data is also then linked to the master calendar and this seems to be working well for date selections.
My issue now though is that I also need to filter charts based on school and gender so I somehow need to link these fields together too if I want to avoid complicated set analysis / buttons with select in field actions etc. The fields to be linked are Bookings.Gender / YL.Gender and Bookings.School / YL.School. They both contain the same data values.
The ultimate goal is to be able to calculate how many beds we have remaining for each school / gender / weekyear by subtracting the total number of bookings for that week from the total capacity.
Hopefully that makes sense! What is the best way to approach this problem? I've attached my current data model as an image for reference.
