I have a database where I am trying to link building asset information. I have created an 'asset' table in which I add individual assets, which can range from simple items or products comprising a number of separate sub-assets. As an example, I have a 'master asset' such as an air handling unit and then related 'sub-assets' such as fans, heating coils, etc, which are all parts of the air handling unit. I have created link tables to make sure the sub-assets link to the master asset and can be displayed in a portal (in the record for the air handling unit I have a portal showing all of the component parts). For various technical reasons I want to keep a single table for all assets, whether they are master assets or sub assets. This is working fine to an extent.
I then have a table of 'Systems' that the air handling unit is connected to. These systems are listed in the 'Systems' Table and comprise items such as electrical supply, water supply, ventilation system. Again, I have created link tables to make sure the various systems can be displayed in a portal for the master asset to which they are connected.
Here is the challenge. I also want each of the connected systems to be displayed in a portal for each of the sub-assets. So if I go to the record for the fan within the air handling unit, I want to see which electrical system it is connected to. Likewise, if I look at an electrical system, I need to see in a portal all the devices connected to it (both the sub-assets such as the fan but also the master asset). I can achieve the result I want if I script the creation of a new entry in the link tables for each sub-element but this doesn't seem very efficient and could be prone to error.
Is there a way to create a relationship that automatically links all of the assets and sub-assets to the relevant systems by way of a single link table (or other technique)?
I hope that the above makes sense!
I look forward to your suggestions and advice.