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:

Sub-location (fk to location)
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.

  • FOREIGN KEYs are not a requirement for databases. If you need to violate them, then either do the operations in the right sequence, or don't use FKs. – Rick James Jan 8 '17 at 1:52
  • @RickJames My question is not really about whether to use foreign keys or not, it is more about whether my Task table/model is ok to have fk's to the same tables that equipment has. – the-a-train Jan 8 '17 at 1:55

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.

enter image description here

  • Are you talking about a nested data set for the make/model relationship? A task is linked to only 1 piece of equipment but the machine that the equipment is attached to may change location and I don't want any 'completed' tasks to change location if the machine moves location. What is the benefit of the new junction table you are referring to instead of my current solution? – the-a-train Jan 8 '17 at 3:25
  • The make and model can exist in the same table. The junction table allows you to have any combination of machine, equipment, and location that you like; it's basically a running log of everything that happens: * Task 123 on Equipment abc attached to Machine qrs at location 5 on 01/01/2017@00:00:00. * Task 456 on Equipment abc attached to Machine tuv at location 3 on 01/02/2016@00:01:01. You can use the table to track equip and machine movement w/o associating tasks as well, just create a dummy entry in the Task table, or make the TaskID nullable in the Junction table. – Lizzy Jan 8 '17 at 3:52
  • What structure are you proposing for the MakeModel Table? ID, Make (string), Model (string) or something else? – the-a-train Jan 8 '17 at 5:22
  • See the update to the answer - I attached an ERD. – Lizzy Jan 8 '17 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.