4

I’m creating a database design for a webshop. I want to store products with different attributes. Currently I have one table with +100 columns, but I want to optimize this.

This is what I’ve come up with so far. I have some questions (see below) about my design so far.

Disclaimer: this is a database DESIGN. I do not have some php/sql-code because I don’t’ know if this is the correct way to do it. I will try to make this question as substantiated as possible.

Here we go…

I have 3 tables:

  • The first table is the table “products” which will store all the general information about each product (id, name, sku, images, …)
  • The second table is the table “attributes” which will store all the attributes (eg. color, width, height, has_bluetooth, …) but NOT the values
  • The third table stores the values for each attribute (table "attributes_values")

Table: products

Product_id     | Name           | SKU
------------------------------------------------------
1              | iPhone 7       | iphone7
2              | HTC One        | htcone
3              | Galaxy S8      | galaxys8

As you can see, I have 3 products in my database

Table: attributes

Attribute_id    | Name
---------------------------------------
1               | Color
2               | Weight
3               | Height

As you can see, I have 3 different attributes in my database – note that some products will not have each attribute

Table: attributes_values

Attribute_value_id    | Attribute_id    | Product_id    | Value
-----------------------------------------------------------------------
 1                    | 1               | 1             | Black
 2                    | 2               | 1             | 0,125 kg
 3                    | 3               | 1             | 10 cm
 4                    | 1               | 2             | Gold
 5                    | 1               | 2             | 0,15 kg

As you can see, product 1 (the iphone) has 3 attributes, product 2 (the htc one) has 2 attributes and product 3 (the galaxy s8) has zero attributes.

My questions

First of all, is this a good approach? I want to create a “dashboard” in PHP where I can dynamically add new attributes when I add new types of products to my database. That’s why I separated the attributes name and value in 2 different tables.

Secondly, how do I fetch the information from the database. I want to select the product + all the attributes it has (and the values associated with each attribute). I think this is the way to do it. Please correct me if I’m wrong.

SELECT
        p.name, // the product name
        p.sku,    // the product SKU
        v.value, // the attribute value
        a.name // the attribute name
FROM
        products AS p
LEFT JOIN
        attributes_values AS v
ON
        p.product_id = v.product_id
LEFT JOIN
        attributes AS a
ON
        v.attribute_id = a.attribute_id

I hope my questions are as clear as possible. If not, feel free to ask. English is not my native language so excuse me for some grammar errors. Thank you all!

I have found the following links, maybe they can help.

  • I would add a units table to store `kg`, `cm`, ... and add it as a foreign key to the attribute values. – juergen d Jul 15 '17 at 22:47
  • Please read [What topics can I ask about](http://stackoverflow.com/help/on-topic) and [How to ask a good question](http://stackoverflow.com/help/how-to-ask) and [the perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) and how to create a [Minimal, Complete and Verifiable Example](http://stackoverflow.com/help/mcve) – RiggsFolly Jul 16 '17 at 01:17
  • Great table design for a newbie and good presentation (all necesssary information and formated), but unnecessary question. You found all this informations and adapt them, but you don't know what you're doing? Search for "database normalization". – BitAccesser Jul 16 '17 at 04:43
  • 2
    This is a common approach in webshops. It is called EAV. Some things: you don't need `Attribute_value_id`; don't store units in `Value`, but in attributes ("all heights in cm"); maybe use ids for colors (and a table defining them). That being said: BECAUSE a lot of webshops use it, just have a look at anyone of them to see what you probably need in addition (e.g. groups ("shoes"), mandatory attributes ("every shoe has a size)", attributes depending on others, ledger, ...). Also, if this is more than a school project/learning attempt, don't reinvent the wheel, use an existing shop software. – Solarflare Jul 16 '17 at 08:06

0 Answers0