1

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

Rick James
  • 135,179
  • 13
  • 127
  • 222
fresher
  • 917
  • 2
  • 20
  • 55
  • make a table called "Attributes" and then as fields do "type" "value", u should read about "Magento and Attributes and Attribute_sets" – KikiTheOne Sep 15 '16 at 07:42
  • as @KikiTheOne pointed out, check out the [EAV model](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) otherwise you'll end up with unnecessary long tables filled with null values. – Julien Lachal Sep 15 '16 at 08:04
  • @KikiTheOne please check here : http://prnt.sc/ci9uzl where under `attribute_id` table i am planning to save `model,size,color` & under `attribute_values_id` table, i want to store `apple, 17inch , blue` is this better approach compare to above 2 i mentioned in question ? is it correct EAV rule ? – – fresher Sep 15 '16 at 11:46
  • You posted this somewhere else, too? – Rick James Sep 16 '16 at 01:03
  • ID's should not be varchars because Auto_increment isn't . and values should be varchars because they can contain chars . maybe this will help u finding "UR" right solution . http://excellencemagentoblog.com/blog/2011/09/07/magento-eav-database-structure/ – KikiTheOne Sep 16 '16 at 06:03
  • @KikiTheOne i will cross check about all data types, except those any other major issues you can see regarding to primary key, forieghn key, performance or any other issues ? – fresher Sep 16 '16 at 06:04

2 Answers2

2

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.

Community
  • 1
  • 1
Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • please check here : http://prnt.sc/ci9uzl where under `attribute_id` table i am planning to save `model,size,color` & under `attribute_values_id` table, i want to store `apple, 17inch , blue` is this better approach compare to above 2 i mentioned in question ? is it correct EAV rule ? – – fresher Sep 15 '16 at 11:47
1

Create product table,

create table property

Fields:

Id    
Name    
...

Create table category

Fields:

Id     
Name    
...

Create tables category_product and category_property...

Create table property_product_value

Fields:

Id     
Property_id
product_id    
Value    
...

Written from mobile, if you have more questions let me know.

MikkaRin
  • 3,026
  • 19
  • 34
4EACH
  • 2,132
  • 4
  • 20
  • 28
  • please check here : http://prnt.sc/ci9uzl where under `attribute_id` table i am planning to save `model,size,color` & under `attribute_values_id` table, i want to store `apple, 17inch , blue` is this better approach compare to above 2 i mentioned in question ? is it correct EAV rule ? – – fresher Sep 15 '16 at 11:47