sql - avoiding duplicate data in a "customers also bought" table -

i writing feature store website suggest other products other customers bought products in current customer's shopping cart.

my plan have job trawls orders database , updates many-to-many table tracking how each pair of products associated 1 another. thought have structure this:

+--------------+--------------+----------------+ | product_a_id | product_b_id | times_together | +--------------+--------------+----------------+ | 12           | 53           | 118            | +--------------+--------------+----------------+ 

then realized not avoid duplication of data in table definition rows this:

+--------------+--------------+----------------+ | product_a_id | product_b_id | times_together | +--------------+--------------+----------------+ | 53           | 12           | 118            | +--------------+--------------+----------------+ 

so increment association new order, have 2 queries:

update also_bought set times_together = times_together + 1 product_a_id = 12 , product_b_id = 53; update also_bought set times_together = times_together + 1 product_a_id = 53 , product_b_id = 12; 

is there more elegant structure update only 1 query, , avoid rows duplicate data in table?

in also_bought table, add check constraint make sure product_a_id < product_b_id. make job add entries in order. avoids duplication in table.

but since want able pairs of product ids in simple way, create view that's union of also_bought itself, id columns reversed:

create view vw_also_bought select product_a_id, b product_b_id, times_together (   select product_a_id a, product_b_id b, times_together   also_bought    union    select product_b_id a, product_a_id b, times_together   also_bought ) 

now can any product id in single column , corresponding paired product id , buy counts other columns.