1

I'm trying to design a products relations table structure. There is an existing product table that has the following fields:

enter image description here

A product could be related to other product but also to a category or a subcategory. Or even, have the three type or just two type of relations at same time.

At the same time, a product subcategory could have a relation to other subcategory or category also to a product.

The main goal for what this is for is when a product is selected by the user, he can get suggestions to other product related to the one selected.

So whats the best table structure you can suggest to store those relations?

I was thinkin some like this: enter image description here

But I dont know if thats reliable way.

Thank you in advance!

EDIT:

Maybe I did not explain well. Lets say a product x is related to products in Category Y and/or also related to products in Subcategory Z. But could also be related directly to a single product. There are chances to a product have the three types of relations or only two or one of them.

And the Subcategory field of product could be have the three types of relations too.. Or none of them..

Improve this question
2
  • Is CODE meaningful? In what way? Normally SKUs aren't imbued with meaning!
    – Vérace
    Jul 4, 2016 at 22:23
  • Hi @Vérace yes, it is important. It's kind of an SKU. The products table already exists and it was made that way. Don't know why. Thank you!
    – HTOaa
    Jul 5, 2016 at 14:47

1 Answer 1

Reset to default
-1

ER diagram for product, product category tree and related products

Here the more clear and powerful model for you domain

Improve this answer
3
  • Hi, thank you for your answer. I can't figure out with that model, how can a product be related with a Category and/or Subcategory.? I mean, lets say a product x is related to products in Category Y and/or also related to products in Subcategory Z. But could also be related directly to a single product.
    – HTOaa
    Jul 5, 2016 at 16:26
  • Product related to category, which have hierarchal structure. Also product related to other products. So you can calculate all suggestions you want.
    – Artemy Prototyping
    Jul 6, 2016 at 12:47
  • If you want relate product to some category just add one more table one to many from product to categories.
    – Artemy Prototyping
    Jul 6, 2016 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.