I am working with eCommerce and we're handling the order picking our selfs.
We're currently recoding the order picking system and while we're doing that I was also thinking about re-positioning (optimizing) all the products and sort them in an order that makes it faster to pick an order. Keeping the most sold products as early as possible but still keep products that people often buy together close to each other.
I might sell 100 of product_1, 90 of product_2 and so on but only 10 of product_3. But all 10 of product_3 has been bought together with product_1 and therefore I want them closer to eachother.
I'm not looking for an all SQL solution on this. As I probably need to first fetch all products from most sold to least and then move around products related to each other with PHP.
Anyone have any suggestion on how to calculate this?
I have two SQL tables I could use for this.
mysql> explain order_products;
+-------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| order_id | int(10) unsigned | NO | MUL | NULL | |
| product_id | int(10) unsigned | NO | MUL | NULL | |
| quantity | int(10) unsigned | NO | | NULL | |
| ...
+-------------------+-----------------------+------+-----+---------+----------------+
mysql> explain orders;
+----------------------+-----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| ...
+----------------------+-----------------------+------+-----+---------------------+----------------+
There are many Way : I am suggesting one of them.
add a field product_sold_count in table order_products. By this you can track count , that how many times your product get sold . if product sold count is more means it is more popular. Then
just apply order by product_sold_count ASC ;
it will give you the result as you ask.