I am building an inventory management application for equipment attached to machines. The app is to allow scheduling and reporting on maintenance and service of the equipment. The machines have make/model information and also site location lookups. There is also daily task information that is attached to the equipment which gets marked as completed and this is used to produce reports. My app entities look like the following:
Location Sub-location (fk to location) Make Model (fk to make) Machine (fk to model, fk to sub location) Equipment (fk to machine) Task (fk to equipment, read below for more info on fk's)
I need to produce reports and calendars based on task information and would like to see tasks by make/model and also site location in separate reports.
The problem is that a task may be completed while the equipment is at Location A and then the equipment moves to Location B - the equipment might also be removed from the current machine and attached to a new machine. I need to allow my app to support this reality and give the user good historical reporting and future scheduling.
My current solution is as follows:
On the Task model, I have a fk to both site location and machine which is copied from the equipment and machine when the task is created. When the equipment location or machine is changed, I update all incomplete ie. completed tasks have their data frozen.
The benefit I see to this is that I can keep my queries simple without having to always join tasks to equipment and then machine to get location and machine/model/make info and I can also support equipment moving without having to introduce some history type table and a complicated join based on todays date etc.
Can anyone advise on pros/cons to this approach or a new approach?
I'll also add that this is a MySQL/PHP application where I am using laravel and eloquent ORM.