Jump to content

Storing products in order within multiple categories

Recommended Posts

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.

Share this post

Link to post
Share on other sites

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?

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.