title: Structure for Products table to store products details from various fields
body: <p>I'm building an application which requires supporting various categories of fields of products (as in products in Fashion, Electronics, Automobile, Grocery etc.), so obviously, for products in every field (&amp; even for their sub-categories), product properties would vary from category to category. </p>
  
  <p><strong>Example:</strong> </p>
  
  <p>Products in <strong>Fashion</strong> field, are like <em>Jeans, Shirts, etc.</em> which have properties like <em>size, color, type, material-type, etc.</em>, whereas Products in <strong>Electronics</strong>, can be like <em>Mobile phones, Laptops, etc.</em> which can have product properties like, <em>processor, memory, storage, size, color, etc.</em></p>
  
  <p><strong>My Question is in regarding, how do I store such information in most generalized &amp; efficient way possible?</strong></p>
  
  <p><strong>What I've thought of a solution:</strong></p>
  
  <p>I've thought of creating <strong>three</strong> tables for maintaining this information.</p>
  
  <ol>
  <li><strong><code>products</code> table:</strong> </li>
  </ol>
  
  <p>This table will contain all products &amp; their categories in <strong>nested set form</strong> such that <strong>each parent</strong> would act as a <strong>category</strong> and <strong>each leaf</strong> would act as a <strong>product</strong>. </p>
  
  <p>Along with this, this table would contain meta information about the product (<em>like name, purchasing price, selling price, sku, etc. which would be common to any kind of product</em>). Probable <strong>Table Structure</strong> of this table is as under:</p>
  
  <pre><code>id(PK), 
  client_fk(FK to clients), 
  parent_id(for nested-sets), 
  lft(for nested-sets), 
  rgt(for nested-sets), 
  depth(for nested-sets), 
  name(meta), 
  sku(meta), 
  purchasing_price(meta), 
  selling_price(meta), 
  created_at, 
  updated_at
  </code></pre>
  
  <ol start="2">
  <li>Other two tables will kinda work like EAV model structure. </li>
  </ol>
  
  <p><strong><code>products_props</code>table:</strong> </p>
  
  <p>This table will store all possible properties to any product (from any category) like <em>size, color, processor, memory, storage, etc.</em> with <strong>their meta details</strong> like <em>description, input-type-details, etc.</em> <strong>Table Structure</strong> for this table can be</p>
  
  <p><code>id(PK), name, label, description, input_type, input_details, created_at, updated_at</code></p>
  
  <ol start="3">
  <li><strong><code>products_props_data</code> table</strong>: </li>
  </ol>
  
  <p>This table will contain values for all the properties that product have. <strong>Table Structure</strong> for this table can be:</p>
  
  <p><code>id(PK), product_prop_fk(FK to products_props), product_fk(FK to products), value, created_at, updated_at</code></p>
  
  <p><strong>Reads</strong> on this information would be much more than <strong><em>inserts, updates or deletes</em></strong>.</p>
  
  <p><strong>Questions:</strong></p>
  
  <ol>
  <li>Am I following the correct way?</li>
  <li>Is it efficient?</li>
  <li>Is there another way around?</li>
  <li>Advises/suggestions to achieve what I am trying to.</li>
  <li>How does well-known e-commerce do it? like Amazon, Flipkart, eBay?</li>
  </ol>