title: Entity-Attribute-Value Table Design
body: <p>I am currently designing a database structure for the products section of an ecommerce platform.  It needs to be designed in such a way that makes it possible to sell an infinite number of different types of products with an infinite number of different attributes.  </p>
  
  <p>E.g. The attributes of a laptop would be RAM, Screen Size, Weight, etc.  The attributes of a book would be Author, ISBN, Publisher, etc.</p>
  
  <p>It seems like an EAV structure would be most suitable.</p>
  
  <ul>
  <li>Select a product</li>
  <li>Product belongs to attribute set</li>
  <li>Attribute set contains attributes x and y
  <ul>
  <li>Attribute x is data type datetime (values stored in attribute_values_datetime)</li>
  <li>Attribute y is data type int (values stored in attribute_values_int)</li>
  </ul></li>
  <li>Each attribute definition denotes the type (i,e, x has column type -> datetype)</li>
  </ul>
  
  <p>Assuming the above, could I join the selection to the attribute_values_datetime table to get the right data without getting the result set and building a second query now that the table is known? Would there be a large performance hit constructing a query of this type or would the below be more suitable (although less functional)</p>
  
  <ul>
  <li>Select a product</li>
  <li>Product belongs to attribute set</li>
  <li>Attribute set contains attributes x and y
  <ul>
  <li>Attribute x is data type datetime but stored as TEXT in attribute_values</li>
  <li>Attribute y is data type int but stored as TEXT in attribute_values</li>
  </ul></li>
  </ul>