Hi Guys, I am here to bother you with another question.
Not being able to come up with a better title, this is my situation:
I am working on a project that has to version data but not only with a "deleted at" field, but with dates from when to when it was valid (i called them "valid_start" and "valid_end"). This is crucial since calculations are performed on old data as well.
An example would be a service catalog and who uses which services in which quantity. This means i have to be able to tell how much department x paid for our services 2 years ago (when their usage was different from today and our prices and maybe even services were as well).
The idea we came up with: Each element has a mandatory valid_start field The valid_end field is either null (=valid forever until it is changed) or a fixed date. Deleting an element is setting it to valid_end = now (i wont go into how we deal with references, but we do have a solution for that)
This means that for example a couple of price items refer to the same Catalog item but only one can be active at a certain time (meaning valid_start < that time < valid_end (or null).
And there lies the problem: How can i ensure that there always is only one "active" record at a time? How do i handle those dates, which the user puts in (also regarding datetime so that i have neither gaps nor overlaps)
I hope you understand my problem and i am very grateful for helpful input :-)