I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on my self-taught database design skills, but it lacks features that I'd like to have. I'm wondering if there would be a way to more effectively model the actual implementation in a database than I have created.
The design consists of a collection of varied implementations of similar component parts. There are 7 types of components, A through G. As you can see in the image below, there are two complexities that make modelling it difficult. The image does not show every possible connection, but essentially any component D through F can connect to any other component D through F.
I've substituted some clumsy made-up example components for clarity.
First, Component B may be connected to component C directly, or it may be connected through any number of components D through F. Components D through F may also be connected to other instances of components D through F. For example, it may simply be B->C, or it could be B->D->F->F->D->E->C or any other configuration. Finally, each instance of components D through F might be connected to more than one other component D through F, creating branching paths.
Second, instances of component C and component G are inherently related, usually (but not necessarily) existing one for one in a pair. However, instances of component G is actually physically connected to a component B. This creates a circular relationship.
As far as I have been able to tell this system is not possible to implement fully in a relational database. Instead I have implemented the below design.
I grouped components D through F together as they have similar attributes, and the user will simply register their existence, but must reference other materials to determine the configuration. I also removed the link from component B and G, as a component G cannot exist without a component C.
My main issue with this design is that when, for example, two component C instances and a number of components D through F are related to a component B, there is no way to tell which components D through F are associated with a specific component C. In an ideal world, I'd like to be able to match the primary key of any component and pull up the information each item in the specific chain of components that the matching component exists within.
Sorry for the vagueness, but I can't speak on the specifics. Thank you.