database normalisation on object that is attached to another object that can change

Question

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.

Answer 1

Since Make and Model is a hierarchical relationship they can exist in the same table.

You'll need a junction table to handle the many to many relationship between machine, equipment, and sub-location.

Your junction table will need to contain four columns; [SLID], [MID], [EID], [datetimestamp].

You can handle tasks in several ways - if one task always follows one piece of equipment then the link between Task and Equipment is fine, however, if the same task can use multiple pieces of equipment on multiple machines then you would want include a task id [TID] field in your junction table.

The junction table will grow quite large so you might want to partition it by month and year, and also experiment with the indexes to make sure that they cover the reporting requirements.

Also, since you're using an ORM I'm going to make an assumption that the idea of using GUIDs as IDs has come up, or will at some point - don't create a clustered index (primary key) on a guid column, if you must use a guid to make it easier for the application to do its thing, then do create an integer id column as your primary keys.

Example ERD - This is what I might start with given the requirements, and modify it from there as needed.