I have a large database of components that are grouped by series.

examples are the AB series, the R2H series…
Within each series, some components can be altered to become other components. This is governed by the part number.

example: There are part numbers in the AB series
AB12.01-4HU AB22.01-4HU AB08.01-4HU
AB12.01-2HF AB22.01-6TR AB08.01-4HL

for a given part, as long as this prefix is the same AB__.__, the letters on the end can transform.
U can become F, R and L, but not the other way around. R can become L and L can become R
I have this mapped in an array indexed on the position of the part number after the prefix:

    'AB__.__-' = [
        0 = [
        2 = [2, 4],
        4 = [4],
        6 = [6]
      ],
      1 = [
      'H' = ['H']
      ],
      2 = [
        'U' = ['U', 'F', 'R', 'L'],
        'F' = ['F'],
        'R' = ['R', 'L'],
        'L' = ['L', 'R']
      ]
    ]

The assembler I built takes components we have, grouped by prefix, and then iterates through each letter position and adds possible components to a buildable table.

Every day I run this assembler on the components present in the database, to build a list of the components currently buildable. This is computationally expensive and I wonder if there is a better way of doing things. Also, there are some configurations which do not neatly fit into this system and would benefit from being able to manually add some configurations. Additionally, there are some components which require the presence of TWO or MORE base components, and this current setup doesn’t allow for that. I have code written that does this but it’s even worse.

I know that I could run all of these calculations just one time and store the possible combinations so that given a component I could retrieve all components buildable by that component, but I am unsure of the best table structure. any insight or advice would be helpful.

Thank you

A table structure I am thinking of could be:
components table: id, part_number, series

buildable_components =
base_component_id, buildable_component_id, build_type {‘manual’ || ‘autobuilt’}

and then if I make changes to the configuration I could run the builder one time to rebuild the database and leave the manual entries alone.

This doesn’t solve the multiple base models needed issue though