Two way relationships in SQL queries

Question

I have a small database that is used to track parts. for the sake of this example the table looks like this:

PartID (PK), int
PartNumber, Varchar(50), Unique
Description, Varchar(255)

I have a requirement to define that certain parts are classified as similar to each other. To do this I have setup a second table that looks like this:

PartID, (PK), int
SecondPartID, (PK), int
ReasonForSimilarity, Varchar(255)

Then a many-to-many relationship has been setup between the two tables.

The problem comes when I need to report on the parts that are considered similar because the relationship is two way I.E. if part XYZ123 is similar to ABC678 then ABC678 is considered to be similar to XYZ123. So if I wanted to list all parts that are similar to a given part I either need to ensure the relationship is setup in both directions (which is bad because data is duplicated) or need to have 2 queries that look at the table in both directions. Neither of these solutions feels right to me.

So, how should this problem be approached? Can this be solved with SQL alone or does my design need to change to accommodate the business requirement?

Consider the following parts XYZ123, ABC123, ABC234, ABC345, ABC456 & EFG456 which have been entered into the existing structure entered above. You could end up with data that looks like this (omitting the reason field which is irrelevant at this point):

PartID, SecondPartID
XYZ123, ABC123
XYZ123, ABC234
XYZ123, ABC345
XYZ123, ABC456
EFG456, XYZ123

My user wants to know "Which parts are similar to XYZ123". This could be done using a query like so:

SELECT SecondPartID
FROM tblRelatedParts
WHERE PartID = 'XYZ123'

The problem with this though is it will not pick out part EFG456 which is related to XYZ123 despite the fact that the parts have been entered the other way round. It is feasible that this could happen depending on which part the user is currently working with and the relationship between the parts will always be two-way.

The problem I have with this though is that I now need to check that when a user sets up a relationship between two parts it does not already exist in the other direction.

@Goran

I have done some initial tests using your suggestion and this is how I plan to approach the problem using your suggestion.

The data listed above is entered into the new table (Note that I have changed the partID to part number to make the example clearer; the semantics of my problem haven't changed though)

The table would look like this:

RelationshipID, PartNumber
1, XYZ123
1, ABC123
2, XYZ123
2, ABC234
3, XYZ123
3, ABC345
4, XYZ123
4, ABC456
5, EFG456
5, XYZ123

I can then retrieve a list of similar parts using a query like this:

SELECT PartNumber
FROM tblPartRelationships
WHERE RelationshipID ANY (SELECT RelationshipID
                          FROM tblPartRelationships
                          WHERE PartNumber = 'XYZ123')

I'll carry out some more tests and if this works I'll feedback and accept the answer.

Answer 1

I've dealt with this issue by setting up a relationship table.

Part table:

PartID (PK), int

PartNumber, Varchar(50), Unique

Description, Varchar(255)

PartRelationship table:

RelationshipId (FK), int

PartID (FK), int

Relationship table:

RelationshipId (PK), int

Now similar parts simply get added to Relationship table:

RelationshipId, PartId

1,1

1,2

Whenever you add another part with relationshipId = 1 it is considered similar to any part with relationshipId = 1.

Possible API solutions for adding relationships:

• Create new relationship for each list of similar parts. Let client load, change and update the entire list whenever needed.
• Retrieve relationship(s) for a similar object. Filter the list by some criteria so that only one remains or let client choose from existing relationships. Create, remove PartRelationship record as needed.
• Retrieve list of relationships from Relationship table. Let client specify parts and relationships. Create, remove PartRelationship records as needed.