Meagan,
It is a common problem to have to report out on business objects that are not really related to each other except by time period. Quickbase is not very good at that problem. Here is a similar architecture to what you are proposing but based around the concept of Month End Close. I have clients where there are a many table related to the Month End.
The difference with your solution is that there are two dates in the MEC Table. As Mark mentioned your Months need to be built out well into the future and have no overlap of dates so your Parent record is always unique.
I do not like to change Key Fields so I will use a Pipeline as you described. There is a Search step that will have a Advanced Query that looks like this
Assume that [First Day of Month] is Field ID 5
[Last Day of Month] is Field ID 6
{5.OBF.'Date'}AND{6.OAF.'Date'}
The Search step of the Pipeline is looking for the record where your Date field sits inside that month. Then the next step in the Pipeline will update the record to relate them.
Reach out if this does not make sense.
