Bilateral Relationships in SQL Queries

Question

I have a small database that is used to track details. for this example, the table looks like this:

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

I have a requirement to determine that certain parts are classified as similar to each other. To do this, I set up a second table that looks like this:

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

Then, a many-to-many relationship was established between the two tables.

The problem arises when I need to report those parts that are considered similar, because relationships are two ways of IE. If part XYZ123 is similar to ABC678, then ABC678 is considered similar to XYZ123. Therefore, if I would like to list all the parts that are similar to this part, I either have to make sure that the relationship is configured in both directions (which is bad because the data is duplicated), or you need to have 2 queries that look at the table in both directions, None of these solutions seem right to me.

So, how to approach this problem? Can this be allowed only with SQL, or should my design be changed to meet business requirements?

Consider the following parts XYZ123, ABC123, ABC234, ABC345, ABC456 and EFG456, which were introduced into the existing structure indicated above. You can get data that looks like this (excluding the reason field, which currently does not matter):

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

My user wants to know: "Which parts are similar to the XYZ123." This can be done using this query:

SELECT SecondPartID FROM tblRelatedParts WHERE PartID = 'XYZ123' 

The problem with this, however, will not highlight the part of the EFG456 that is associated with the XYZ123, despite the fact that the parts were reversed. It is possible that this can happen depending on which part the user is working with, and the ratio between the parts will always be two-way.

The problem with this is that now I need to check that when the user establishes a connection between the two parts, he does not yet exist in the other direction.

@Goran

I did 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 a new table (note that I changed partID to part number to make clearer example, the semantics of my problem did not change, though)

The table will look like this:

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

Then I can get a list of similar parts using this query:

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

I will do some more tests, and if that works, I will answer and accept the answer.

Answer 1

I ran into this problem setting a relationship table.

Parts Table:

PartID (PK), int

PartNumber , Varchar (50), Unique

Description , Varchar (255)

PartRelationship table:

Relatively (FK), int

PartID (FK), int

Relations table:

RelationshipId (PK), int

Now similar parts are simply added to the relationship table:

RelationshipId, PartId

1,1

1,2

Whenever you add another part with the ratio Id = 1, it is considered similar to any part with the ratio Id = 1.

Possible APIs for adding relationships:

• Create new relationships for each list of similar parts. Allow the customer to download, modify and update the entire list when necessary.
• Get the relationship (s) for a similar object. Filter the list according to some criteria so that there is one left or allow the client to choose from existing relationships. Create, delete the PartRelationship entry as needed.
• Get a list of relationships from the relationship table. Let the customer specify details and relationships. Create, delete PartRelationship entries as needed.

Answer 2

Add a CHECK constraint, for example.

 CHECK (PartID < SecondPartID); 

Answer 3

I know this is old, but why not just execute this request with your original schema? Fewer tables and rows.

 SELECT SecondPartID FROM tblRelatedParts WHERE PartID = 'XYZ123' UNION SELECT PartID FROM tblRelatedParts WHERE SecondPartID = 'XYZ123' 

I deal with a similar problem and consider two approaches and wonder why you thought the scheme with the relationship table was the best. It seems that the original problem still exists in the sense that you still need to manage the relationship between them on both sides.

Answer 4

How about having two lines for each similarity. For example, if you have objects A, B, which you will find in the link table

 AB BA 

I know that you will double the relationship data, but they are integer, so it will not kill your database. Instead, you have some advantages:

• you will not use union. The union is killed in any dbms. Especially if you have an order or a group for
• you can implement a more specific relation: a refers to b, but b does not apply to a. For example, John can replace Dave, but Dave cannot replace John.