0

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

  • Is CODE meaningful? In what way? Normally SKUs aren't imbued with meaning! – Vérace Jul 4 '16 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 '16 at 14:47
-1

ER diagram for product, product category tree and related products

Here the more clear and powerful model for you domain

  • 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 '16 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 '16 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 '16 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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