Solved Relations between products : Similar Products. (1 Viewer)

[KitaYama]

This is a branch of This thread.
My case seems to be slightly different from the mentioned one. Hence, I thought a new thread may prevent over lapping comments and problems.

I have a product table. Currently 48895 records. And of course increasingly in a daily basis.
Some products are similar to each other. This similarity has no specific rule that can be fit to use tags etc.
I need simply set ProductA is similar to ProductB. (Manually or picking from a subform, listbox, ...)

Some requirement:

• If ProductA is similar to ProductB, then when I filter Products form for each product, I need to see the other product too. (a listbox or subform, or any other way)
• If ProductA is similar to ProductB and ProductB is similar to ProductC, then searching for each product, should bring up the other two product.
  (The famous rule : If A=B and B=C then A=C) --> for my case, simply change = sign to Similar
• Any product may have one or many similar products.
• There's no limit in the count of similar products.

I really appreciate any advice on the table design.
Thanks

@MajP I think it's all I need.
Take your time and advise whenever you have some free time.
Thanks again.

Note: In my real world (my profession, CAD), similar designs are set as Configuration. And these similar parts are managed by PDM with a SQL server as BE.
I was just thinking to do it in Access (because I need some custom capabilities that can not be managed in an out of box software)

 

[ebs17]

In a real world situation, if ProductA is similar to ProductB and ProductB is similar to ProductC, then I expect to see both ProductA and ProductB in the results when I search for Products that are similar to ProductC.

If ProductA is similar to ProductB and ProductB is similar to ProductC, then ProductA must also be similar to ProductC. This third pairing must also be reflected in the junction table, and then you will always find all products that have been explained and defined similarly to one.

 

[KitaYama]

If ProductA is similar to ProductB and ProductB is similar to ProductC, then ProductA must also be similar to ProductC. This third pairing must also be reflected in the junction table, and then you will always find all products that have been explained and defined similarly to one.

You mean if I have 10 similar products I have to write 100 records to the junction table?
A similar to B
A similar to C
A similar to D
A similar to E
.....
B similar to C
B similar to D
B similar to E
.....
C similar to B
C similar to D
C similar to E
.......

If I misunderstood your suggestion, could you please expand on what you mean?
thanks.

 

[ebs17]

You mean if I have 10 similar products I have to write so many records to the junction table?

Yes, same is same, and similar is similar. Don't be afraid of lots of records, that's what SQL is for and how to use it well.

B similar to C
.....
C similar to B

Of course, such duplications would not be recorded; one connection is sufficient.

But the statement alone is certainly similar and therefore a classification is not enough. As already mentioned in the other topic, you would have to bring attributes into play through which a qualitative connection can be established between products.

 

[KitaYama]

you would have to bring attributes into play through

In my case it's not possible. I will think over it again to see if I can find a way, but at present I don't see any possible way to use attributes.
Thanks for the suggestion though.

 

[gemma-the-husky]

Well another way is to have a "similar to" tag, and mark all products with a text or code reference. Then your query can include the product code, and all records with matching tags, but it's likely to become a data maintenance issue. If records need multiple tags to achieve what you want it's even more fiddly.

I've no idea how search engines for instance achieve the results of their text searches so quickly, but maybe that's the sort of thing you need.

 

[gemma-the-husky]

Actually, I had something like this to manage sales orders from depots, where all depos had limited capacity. If a depot's capacity was full, the system would let the users try other local depots.

Depot A might be local to depots B and C. Depot C might be paired with depot D, but that doesn't mean depot A was paired with depot D. I did that by having them set up the depot pairs both ways.

So if they entered an order for Depot A, they could switch it to any of Depot A's paired depots.

 

[KitaYama]

I think I have an idea that may work. It's very simple. A normal table and only a few line of code.
I will see if I can put up a sample database.

 

[MajP]

Not sure if this is of interest, but this does all the reverse and transitive relations.
If you relate A to B, C, D
A B
A C
A D

this creates
B A
C A
D A

B C
B D

C B
C D

D B
D C

You can test.
Relate A to B, C, D from the main form. Then move to the next main form records.

  Dim rs As DAO.Recordset
  Dim colProds As New Collection
  Dim prod As Long
  Dim i As Integer
  Dim j As Integer
 
  Set rs = CurrentDb.OpenRecordset("Select * from tblProductRelations where Product1_ID_FK = " & ProdID)
 
  'Relate all internal
  colProds.Add ProdID
  Do While Not rs.EOF
      
      prod = rs!Product2_ID_FK
      colProds.Add prod
     ' Debug.Print "add prods " & getProductName(rs!Product2_ID_FK)
      rs.MoveNext
    Loop
   For i = 1 To colProds.Count - 1
   For j = i + 1 To colProds.Count
      Debug.Print "relating " & getProductName(colProds(i)) & " to " & getProductName(colProds(j))
      InsertRelation colProds(i), colProds(j)
      InsertReverseRelation colProds(i), colProds(j)
    Next j
    Next i

   rs.Close
 
End Sub
Public Sub InsertRelation(Prod1 As Long, Prod2 As Long)
  Dim strSql As String
  If Prod1 <> Prod2 Then
    strSql = "Insert into tblProductRelations (product1_ID_FK, Product2_ID_FK) values (" & Prod1 & ", " & Prod2 & ")"
    Debug.Print strSql
    CurrentDb.Execute strSql ' dbFailOnError
  End If
End Sub
Public Sub InsertReverseRelation(Prod1 As Long, Prod2 As Long)
  Dim strSql As String
  If Prod1 <> Prod2 Then
    strSql = "Insert into tblProductRelations (product1_ID_FK, Product2_ID_FK) values (" & Prod2 & ", " & Prod1 & ")"
    Debug.Print strSql
    CurrentDb.Execute strSql ' dbFailOnError
  End If
End Sub

 

[tvanstiphout]

"Similar"?
Similar by what? Because one or more attributes (there is that word again) are the same or in the same range?
Example: Apple and Orange are similar because they are both Edible=True, Weight between 30 and 50, Diameter between 3 and 5, etc. but they are also dissimilar with other attributes such as Color and OptimalClimate.

Some attributes we may care more about than others - this could lead to a weighted calculation as to how similar two items are, which could lead to showing users a ranked list of similar items.

Maybe similar means they are of the same grouping level, e.g. "Fruit", and the groups are created once to fit business-relevant categories and each item can be in one or more.

 

[KitaYama]

Not sure if this is of interest, but this does all the reverse and transitive relations.
If you relate A to B, C, D
A B
A C
A D

this creates
B A
C A
D A

B C
B D

C B
C D

D B
D C

You can test.
Relate A to B, C, D from the main form. Then move to the next main form records.

@MajP Million thanks for the time you put on this.
Last weekend, after I posted the above help request, I came out with a solution that works ( I assume).
You may want to see the attached database for a variation that does the same task, but simpler (Maybe)

By simpler, I mean if you have 10 similar products, you don't need to add 100 records.
( I understand that in a database the count of records is not that important)

Since I don't have MS Access at home, I had to use a remote desktop software from an iPad to my work machine at work.
It was very hard to move between windows, so there are a lot of leftover codes and it still can be done simpler, and a lot of code there is not necessary. It's the remaining of the tests.

But I have to admit that I like the way you came up with that result. Absolutely impressive.
Again and again thanks. I appreciate your help and the time you put on this.

 

[jdraw]

Without some examples and not clear on what similar entails, I googled some attributes of Product.

 

[KitaYama]

"Similar"?
Similar by what? Because one or more attributes (there is that word again) are the same or in the same range?
Example: Apple and Orange are similar because they are both Edible=True, Weight between 30 and 50, Diameter between 3 and 5, etc. but they are also dissimilar with other attributes such as Color and OptimalClimate.

Some attributes we may care more about than others - this could lead to a weighted calculation as to how similar two items are, which could lead to showing users a ranked list of similar items.

Maybe similar means they are of the same grouping level, e.g. "Fruit", and the groups are created once to fit business-relevant categories and each item can be in one or more.

Similarity is different in different businesses. In a business a pen holder may be similar to a speaker case.
The main problem is that, we think within our limited knowledge and it's strange for us to see something outside of the circle.

As I said, there's no rule in the similarity between the products and it's only the user who decides what is similar to what.
Think of it this way :
( Though it's not what we try to do, but it may give you the idea)
Imagine you are an engineer and you start designing a part. You have two options.
1- Start from scratch and design everything.
2- Opening another design that more or less is the same as what you have in mind. Then cut places, delete unnecessary parts, add holes and new parts, finish your job and save it with a new name.

If you go with option 2, you can save a lot of time by re-using an existing design and not repeating the same process.
You may think of it as a template, but no, it's different from a template.

Over the years, you may have used ProductA as a base for ProductB, then have used ProductB as the start point of ProductC and the list goes on.
Then some years later, you want to design a part, you know you can use ProductB and start from there, But you also are sure there was a variation of that part that is closer to what you have in mind (ProductC), but you can not find it. How can you find which part was designed based on ProductB among a total of 44889 products?

Then you think, How about having a table and putting all related productPKs somewhere that we can track back...

You see, what may make two product similar for me, it's not similar for my colleague who sits beside me.
We have different methods for working on the same CAD software, so with my workflow, A is similar to B, but for someone else, A is similar to C.

As you see, there's absolutely no rule that can say A is Similar to B, except the way my mind works and it's for sure different for the one sitting next to me.

 

[KitaYama]

Without some examples and not clear on what similar entails, I googled some attributes of Product.

I think we're going in circles. Please see my previous reply.
Not everything can be categorized into limited groups.

thanks.

 

[GaP42]

There is the concept of similar relating to categories (the problem being how many and exclusivity), and the concept of similar that is about having a relationship to another object/record of the same type - that may be quite arbitrary as @KityYama as described. In that latter area, if the relationships are not arbitrary to the specific user the organisation may have business rules about how these relationships are set up - covering all possible permutations as in the last solution put forward by MajP for generating all of these, or the immediate bi-directional. parent/child, or as a simple ISA relationship pointing to the parent record.
If the items in consideration have attributes captured that are amenable to assigning to "similar" categories that can be handled through an assignment process, however if the requirement is to ascribe an association to another item in the same table as "similar" then the nature of it needs to be known/defined - eg a restriction that says "Object X may be used to construct Object Y , or "is a component of", etc These are used to define hierarchies which can be traversed, or used to show precedents and dependents. The resolution of the many-many self-referential relationship of object to object, with defining one or more characteristic (such as colour, origin, direction (is composed of, is a component of), or perhaps a "mygroup" provides greatest flexibility, but does involves significant thought on maintenance if automated generation rules are to apply.

 

[The_Doc_Man]

I will merely observe that the problem we are having is that if we omit attributes as the basis of this similarity, we are being asked how to quantify a subjective similarity. The only way I can see to do that is to have a junction table that allows you to say "Remember that product XYZ is subjectively similar to product RST." That part of the problem is easy enough.

However, when you objected to having to essentially enumerate all of the other potentially similar products via some transitive relationship that mechanically says "A like B" and "B like C" and therefore let me assert for you that "A like C" - I see a pitfall here.

Suppose that "A like B" and "C like B" are both declared because (to use your earlier example) some of the parts in A are also used in B and some of the parts in C are also used in B. But suppose that somehow, NONE of the parts in A are used in C because the parts in A and C don't overlap each other, they only overlap in B. In other words, A and C are similar to B but not similar to each other. Again, this is a subjective evaluation and I understand the desire to remember it. But to automate it from, say, one click of a button - is not so clear-cut a decision. I hope I'm being clear in my thinking here.

In essence, the only way to record subjective similarities that are not discoverable through property analysis is to manually record EACH choice. Just one man's opinion. It is the impossibility of clear-cut automation (which requires objective data) that makes this a wrinkly little problem.

 

[CJ_London]

I use a similar method to @MajP

I had a pharmaceutical client and they had multiple products that were 'similar'. e.g. aspirin could have many different commercial names and different types e.g. tabs, tablets, caps, capsules etc. (note tabs and caps could be an abbreviation for tablet or capsule - but not always)

I added a field to the product table which acted a bit like a parent/child relationship with the first record being related to itself. To Modify Majp's example I would have

A,A
B,A
C,A
D,A

The first record is identifiable by the parent field = PK (A=A) so a combo could identify the 'parent' records - usually combined with some other criteria to reduce the list.

The first record doesn't have to be the first record in a list, the order doesn't matter. And easy to find all similar products.

 

[KitaYama]

I use a similar method to @MajP

I had a pharmaceutical client and they had multiple products that were 'similar'. e.g. aspirin could have many different commercial names and different types e.g. tabs, tablets, caps, capsules etc. (note tabs and caps could be an abbreviation for tablet or capsule - but not always)

I added a field to the product table which acted a bit like a parent/child relationship with the first record being related to itself. To Modify Majp's example I would have

A,A
B,A
C,A
D,A

The first record is identifiable by the parent field = PK (A=A) so a combo could identify the 'parent' records - usually combined with some other criteria to reduce the list.

The first record doesn't have to be the first record in a list, the order doesn't matter. And easy to find all similar products.

Never thought of this method. Will give it a try to see if I can replicate it.
Don't understand why you need A,A B,A
With your explanation, I think having only A (Parent PK) in this additional field is enough and can be used to filter all records that are similar.
For example

SELECT ProductName FROM tblProducts WHERE relationField=A (or a Number)

 

[CJ_London]

Don't understand why you need A,A B,A

simplistically
tblProducts

ProductPK	pName	SimilarFK
1	product A	1
2	product B	1
3	product C	1
4	product D	1

Since you have 40+k records, I'm assuming this will be a manual user operation to populate the field. So to populate SimilarFK by a user choosing from a combo the rowsource would be

SELECT ProductPK, pName FROM tblProducts WHERE ProductPK=SimilarFK ORDER BY pName

If you didn't populate A,A (1,1 in this example) then looking a similar products to productPK=4 would not return 1

Given you still need to choose an initial 'parent' you would need a method to populate similarPK - depends on the requirement and method being used to populate but at it's simplest you could set it's default value to be the PK or modify the above sql to be a union query so user can assign something like 0 or -1 to indicate it is not similar to anything else

 

[KitaYama]

@CJ_London thanks for the details.