Search code examples
phpmysqldatabasedatabase-designentity-attribute-value

saving different attributes types in single table or multiple tables in mysql

we are planning to built a e-commerce site with PHP.

There are different types of products like mobiles, tshirts, watches, books.

  • Mobile have attributes like "ram , model_name, os" ,
  • T-shirts have attributes like "color, size"

Questions are the following:

  1. Is it better to save all attributes in a single table called Products (even if there will be/ it will create a lot of "NULL" values)?

  2. Save different types of products in different tables like mobile, tshirts, etc ...

in the second case more tables will be created (see the 2 images below).

enter image description here

or

enter image description here

Update

as suggested in answers & comments, i am using EAV model and draw following design, please inform me if you found any mistakes....

enter image description here

Solution

  • The problem you describe is pretty common - it's generally known as "storing polymorphic data in a relational schema". It occurs on Stack Overflow quite regularly.

    You ask "what's better" - that, of course depends on the way you intend to use the data.

    If you don't know in advance what attributes you're going to be storing - for instance, if your ecommerce site is likely to introduce more product types in the future - a purely relational model is unlikely to work, because you have to make schema changes every time you introduce a new type of product.

    If you don't know in advance what sort of queries you need to support, again, the relational model may be a problem. For instance, if you have a filtering mechanism that allows users to search for t-shirts in colour blue, size small, from brand "xyz", you may need to dynamically create a SQL query. This is not particularly easy.

    The EAV model gets round the first problem, but not the second. In fact, it can become really hard to run even simple queries against an EAV datamodel.

    I'd consider a solution where you store the "known upfront" attributes in SQL tables (SKU, price, is_sellable, description etc.), along with the relationships (vendor, category, warehouse etc.). The other, variable data can then live in JSON documents within the database. I'd pay particular attention to the MySQL full text search indexing - you may be able to use this instead of "pure" SQL to run many common queries.