Products relation structure

database-design

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

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:

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

Best Answer

Here the more clear and powerful model for you domain

Related Solutions

Storing Different Products and Joining on table Names

I'm not sure why you need different tables for different product details. I'm going to guess though, that it's because your product types are so different that they have very different attributes (clothing will have sizes: S, M, L whereas books will have have an indicator of hardcover vs. paperback, etc...). You might want to try a more generic solution, where you have a table of product attribute types and another table of attributes. Something like this:

product
-------
  product_id
  name

product_attribute_type
----------------------
  attribute_type_id
  attribute_type_name

product_attribute
-----------------
  product_attribute_id
  attribute_type_id (FK to product_attribute_type)
  product_id (FK to product)
  value

Then you can have data that looks like this

product
-------
ID | name
-----------
1  | Awesome pants
2  | Book of cool stuff
3  | 1337 software

product_attribute_type
----------------------
ID | attribute_type_name
------------------------
1  | waist size
2  | number of pages
3  | system requirements
4  | inseam

product_attribute
-----------------
attribute_type_id | product_id | value
--------------------------------------
1                 | 1          | 33 inches
4                 | 1          | 34 inches
3                 | 3          | Win7, 4 GB RAM
2                 | 2          | 703 pages

Getting info on a product is easy:

select product.name, product_attribute.value, product_attribute_type.name
from product
inner join product_attribute on product.product_id = product_attribute.product_id
inner join product_attribute_type on product_attribute_type.attribute_type_id = product_attribute.attribute_type_id
where product.product_id = $PRODUCT_ID;

Database Design – Best Logical Structure for Different Entity Categories

I recommend you to read about the dangers of the Entity-Attribute-Value pattern on this presentation by @Bill Karwin.

One of the solutions is storing NoSQL-like data in a serialized BLOB, if you do not need to read and write individual properties. That is, storing a key (product) as the primary key, and the variable property-value pairs all together in a single blob. This is a non-normalized structure, leaving the responsibility of the constraints to the application layer, but it works great for certain patterns (the same that usually work well with NoSQL solutions). Even if you need indexing, you can create inverted indexes on a separate table as needed.

This and other solutions can be seen on this presentation in the latest MySQL Conference, including when you should search for alternative database engines.

Related Question