Storing products in order within multiple categories

[mallen]

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.

[Barand]

I would need to know more about the custom "p_order" to give an answer, How is it applied - it's not possible to see with only two records?

 

Do products have a different custom order for each category, or are they listed in that order regardless of category?

[gizmola]

The table looks relationally sound.  It solves the problem of associating a product with a category and a specific order.  Seems like a lot of work to maintain that for lots of products, but if the number of products is small that's not a big deal.

 

If the ordering is exception based, or the case of "featured" product or "featured category product" then I'd have a slightly different structure, but if your use case is as simple as implied, then it looks good.