CODEWITHSUNDEEP
phpsqldatabase-designjoinattributes
Kristian
Kristian

Reputation: 1363

How to achieve dynamic attributes while keeping queries clear and effective

I’m trying to come up with a database/model design for product and related dynamic product attributes. However at the modeling part I’m afraid I’m doing something “wrong”, as I end up using A LOT of JOINS. Basically I’m hoping to get some input on how to achieve the desired functionality, perhaps with a more simple design. Should anyone with experience feel that I missed something of importance - then please enlighten me :)

Examples of dynamic product attributes:

The “dynamic” attribute covers two aspects, one where there’s a single value, like weight that equals some value, or the other example color, which holds a lot of different values. In addition to the attribute values, each attribute also has unique language settings. E.g. the attribute key/value can change depending on the customer language.

Structure of my tabels:

Product

- id
- created
- modified

Product_variants

- id
- product_id
- quantity
- price

Attribute

- id
- visible
- required
- comparable

Attribute_group

- id
- type

Attribute_groups_attributes

- attribute_group_id
- attribute_id

Attribute_group_languages

- id
- attribute_group_id
- name
- locale

Attribute_languages

- id
- attribute_id
- name
- value
- locale

Product_attribute

- product_id
- attribute_id
- group
- variant

Example of a query

In order to fetch the newest products and matched attributes, I execute the nasty query below:

SELECT pv.price, pv.special_price, pv.overlay_id, p.id, pv.id variant, pl.url_key, pl.name, UNIX_TIMESTAMP(p.created) created, al.value color_value, a.id color_id,al.name color_name
FROM x_product_attribute pa
INNER JOIN x_attribute_group ag
ON pa.attribute_id = ag.id AND pa.group = 1 AND pa.variant = 0 AND ag.type LIKE 'color%'
INNER JOIN x_attribute_group_languages agl
ON agl.attribute_group_id = ag.id AND agl.locale = :locale
INNER JOIN x_attribute_groups_attributes aga
ON aga.attribute_group_id = pa.attribute_id
INNER JOIN x_product_attribute pa2
ON aga.attribute_id = pa2.attribute_id AND pa2.variant = 1 AND pa2.group = 0
INNER JOIN product_variants pv
ON pa2.product_id = pv.id
INNER JOIN x_attribute_languages al
ON al.attribute_id = pa2.attribute_id AND al.locale = :locale
INNER JOIN x_attribute a
ON a.id = pa2.attribute_id
INNER JOIN products p
ON p.id = pa.product_id
INNER JOIN product_languages pl
ON pl.product_id = p.id
INNER JOIN shop_products sp
ON sp.shop_id = :shop_id AND sp.product_id = pa.product_id
GROUP BY p.id,a.id
ORDER BY p.created DESC,a.sort ASC, p.id DESC

As mentioned, there's a lot of JOINS in a query that should be relative simple. I could split this up into smaller queries, but I'm not sure what would be the most effective - while still mapping and keeping the "dynamic" properties of the attributes.

Upvotes: 3

Views: 716

Answers (2)

Samarth Bhargava
Samarth Bhargava

Reputation: 4228

While NoSQL DB like MongoDB is definitely a better fit, if you must use SQL, I'll recommend that you go through the EAV pattern and study the database design of Magento Commerce.

http://www.magentocommerce.com/wiki/2_-_magento_concepts_and_architecture/magento_database_diagram#the_magento_eav_data_model

That would give a real world feel for how this has been successfully implemented in production systems.

Upvotes: 0

Chris Laplante
Chris Laplante

Reputation: 29658

I would suggest a NoSQL solution, such as MongoDB. It will make designing your application much easier since you can represent your products with simple name-value pairs (just like JSON).

If you stick with SQL, I think that you will find that your application and database become unmanageable.

Upvotes: 1

Related Questions