I am working on the availability portion of my database that determines the availability of an item based on its appearance in other records and the current.
It essentially is tracking rental inventory. Each "job" has a start and end date and equipment list. This portion of the database is currently 3 tables: "Jobs", "line items", and "inventory", much like a standard invoicing layout. When I add equipment from the inventory to a job, it creates a new line item.
What I plan on doing for the next step, is have the system confirm the "inventory" being added isn't part of an overlapping "job", along with a number of other minor features, such as a QTY available for the job (based on the dates of the job), which I assume should be a field in "inventory" that isn't indexed and has an appropriate calculation.
Question: is creating an "availability" field in Inventory the correct approach? If so, what type of calculation(s) would give me the desired result?