I'm designing an equipment reservation database and am having trouble with the relationships used to check for schedule conflicts. The issue is that I need a join table between the equipment table and the reservation table, and I need to check the schedule conflicts from the join table. Since I'm using relationships to check for conflicts (as suggested in this topic: Detecting scheduling conflicts ) I need fields for the start and end date in my join table, but this data is stored in the reservations table. If I use unstored calculation fields, the relationship doesn't work. The only other way I can think of do this is to have script triggers when the user edits the time info on the reservation to filter down to all of the items in the join table, but this would cause duplicated data in my database and I'm worried it could be a performance problem.
Is these any other way to do this that I'm missing?