Search code examples
e-commerce

Should set of product attribute considered a column stored as array?

Supposedly I got a products table:

Id product_name
1 Jeans Shirt
2 Pie

Except the variant of product with options like:

id product_id name
1 1 Color
2 2 Flavor

So, I want to storing a set of attribute

  • Jeans Shirt : manufacturing date, material, pattern design, style, origin, seasons (autumn,summer,spring,winter)

  • Pie : Recommended age, Ingredients, Food type, Flavor, Production date, Expiry date )

With all of this I came up like:

  1. I store a set a attributes as a columns and it would be an array:
Id product_name attr_set
1 Jeans Shirt [manufacturing date, material, ....]
2 Pie [Recommended age, Ingredients, Food ty, .... ]
  1. I split into a separately a table a linked into a table product with 1-N relations :
Id product_id attr_name
1 1 manufacturing date
2 1 material
.. ... ....
11 2 Recommended age
12 2 Ingredients

All set attribute I intended to bring is the default which meant all attribute is based on product , admin cant CRUD its ? So I wonder What case would be reasonable when designing the database in e-commerce ?

https://i.ibb.co/5WyMnPV/Screenshot-2024-04-05-190859.png

My database

Solution

  • You will need to think about how you are intending to store the values. If those arrays in solution 1 are actually textual values (varchars or the like), then think again. You will likely need statistics and ease for searching the individual items, so you will need to comply to 1NF and avoid having fields that contain multiple elements.

    Of course, some RDBMS systems allow the type of array, which is an alternative (example: https://www.postgresql.org/docs/current/arrays.html), but you can easily get into trouble with the number of elements or the need to elaborate on those elements if they turn out to be more than just the value you are storing in the array and you will have lots of troubles with values semantically identical, but technically different, such as manufacturing date vs manufactured date. So it makes sense to have predefined possible values for attributes in a separate table and all uses of values would be done through their id, so you will have the attribute names stored in a single place and changing those values would be trivial, without dangers of inconsistency due to redundancy. You will also have difficulty migrating array values between different flavors of RDBMSs or to use arrays in older versions of the same RDBMS if you end up needing it.

    So, for theoretical and practical reasons you might choose the following setup:

    attributes

    Id name
    1 manufacturing date
    2 material

    and

    product_attributes

    Id product_id attr_id
    1 1 1
    2 1 2
    .. ... ....
    11 2 3
    12 1 4