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 |
| ...
+----------------------+-----------------------+------+-----+---------------------+----------------+