I have a list of products that appear in multiple categories. And they also appear in a custom order using a "order" column.
My table is called "category_assoc" and stores the following.
cat_assoc_id, prod_id, cat_id, p_order
I will try to type it out so you can see how its displayed. For example, one product in two categories and is at position 1 and position 8.
cat_assoc_id | prod_id | cat_id | p_order |
001 123 4 1
002 123 9 8
Is this the best way to record items that appear in multiple categories? I didn't want to post my PHP code yet since this is part of the database organizing.