Database structure for service equipment

paul.wilson · 2025-02-13T12:30:40.232Z

Hi,
Could anybody advise how best to structure my database for parts needed for a particular service?

The links are pretty complex. I have a customer, they have sites, they have equipment, the equipment has parts. When a certain service on an equipment is needed, only certain parts are needed. It’s this link I’m not sure about.

Let’s say a machine has parts A, B, C, and D. On Service type 1, only A and B needs to be replaced, on Service type 2, A, B, C, and D need to be replaced.

How am I best structuring this?

mati · 2025-02-13T13:58:49.314Z

Hi Paul

Speaking quickly and without knowing 100% the product you want to build I can tell you that you can use a satellite data type, which we will call “service_part”, where you make the 1 to 1 relationship between the service and the part. Then in your data type “service” you can have the listing of this satellite data.

This allows you to have a clear relationship between the parts and the service and keep your data types as light as possible. Also have a clear control in case you have to edit the services or delete one or more pieces of it.

In the same way, a recommendation, it is very useful for me to “draw” the tables that I am going to have and build their relationships, to have a clearer picture. Maybe it would be better for you to start with this and then understand if my example is according to the product you want to create.

Good luck

paul.wilson · 2025-02-13T14:07:23.801Z

I appreciate your suggestion and think I understand what you mean. I’ll play around with that and let you know how I get on.

Thanks for the suggested of drawing the tables, I did try this, but then confused myself even more. I think it could be the case of stepping away for a few hours and coming back to it with a fresh set of eyes.

hi.luisacosta · 2025-02-13T16:15:31.776Z

Example Setup:
Service_Template acts as a blueprint, saying “this type of service requires these types of parts,” while the Service_Instance records what actually happened, with the specific parts used.

Structure:

• Service_Template (predefined service plan)

• List of Part_Types (e.g., “Tires,” “Brake Pads”)

• Any other service details

• Service_Instance (actual service performed)

• Linked to a Service_Template

• List of specific Parts used (e.g., “Michelin X123 tires from Van A”)

This way, the template defines what should be serviced, while the instance records what was serviced, customized to the actual equipment.

If you need help with the rest of the details, happy to help, I like designing these

paul.wilson · 2025-02-14T07:52:59.759Z

Excellent, I think that’s the one. My job today is to try and get this functional, so I’ll your suggestion.

At the moment, I have the database with field for master equipment (this is where the user can create a new piece of equipment using an already created grandfather type piece of equipment), equipment, parts. I think it needs a complete restructure.

image1135×727 36.9 KB

image1067×653 28.4 KB

image837×796 32.4 KB

image1263×807 67.2 KB

paul.wilson · 2025-02-14T10:08:26.366Z

When you say ‘linked’ to Service_template, how would I achieve that link?

hi.luisacosta · 2025-02-14T14:30:00.729Z

What I meant was a reference field. A field which is of type “Service_Template”.

So when the user clicks “Oil Change” on the screen, Oil Change is a Service_Template; so now you create a “Service_Instance” and save the “Service_Template” to that new record.

paul.wilson · 2025-02-17T06:20:38.125Z

Thanks @hi.luisacosta, that seems to be functioning how I wanted it too.