Date range as relationship key?
I'm creating a DB to track a manufacturing process. I've got crocks (for sauer kraut) that are used over and over again. They'll be in use for certain date ranges. When planning future production batches I want to be able to see which crocks are available for the date range I'm about to be planning on.
Pretend crock A is scheduled for use during 10/1-10/8 and 10/20-10/30. If I want to schedule aonother batch for 10/9-10/19 that'd be fine, but if it were for 10/8-10/20 that wouldn't be.
I've been going over the different ways to do this in my head and haven't figured anything that feels clean yet. I'd like to be able to use a relationship to dynamically determine crock availability but if needed I can just script something to manually determine this stuff.