I am developing an auto parts quoting and pricing application for a company that sells auto parts. The client wants the application to handle their internal daily operations, such as calculating parts prices, creating, and managing quotes. To support these requirements, I have designed a database with more than 20 tables.

One of the key challenges the client wants the application to address is a common issue in the auto spare parts industry: parts equivalency. Equivalent parts are parts that can be used interchangeably. For example, if a part with part number (a unique identifier for auto parts across the industry) PN0001 is equivalent to parts with part numbers PN0002, PN0003, and PN0004, then all these parts can replace one another.

I have attached the screenshot of some tables in my database, parts, vehicles, manufacturers, parts_equivalence,vehicle_parts_compatibility. To capture the equivalence information, I created a self-joint table of the parts table, with part_id and equivalent_part_id columns as shown below.
database design for parts quoting and pricing application

If I implement this way, I will end up with a situation which looks like this:
**part_id **equivalent_part_id
PN0001 PN0002
PN0002 PN0003
PN0003 PN0004
PN0004 PN0005
PN0005 PN0006
PN0006 PN0007
PN0007 PN0008
PN0008 PN0009
PN0009 PN0010

PN0001 is equivalent to every parts in this table. But, if at some point I wanted to see if PN0001 is equivalent to PN0010, I have to loop through every records in this table.

If PN0001 = PN0002, and PN0002 = PN0003, so PN0001 = PN0003.

I don’t think this is the accurate way to do this as we are going to miss a lot of things in this situation.

I am unsure how to model this concept effectively in a database. Any advice on structuring this would be greatly appreciated, especially if you’ve encountered a similar problem before.