What are the differences between EAV and Flat catalogs?

Question

Can anyone provide a clarification on the differences between EAV and Flat catalogs, when accessing the sub categories of a category?

Answer 1

If flat catalog is enabled, then in any product list the flat tables will be used. The flat tables are generated one per store view when rebuilding the flat index. They contain all the attributes marked as 'Used in product listing', one column for each attribute. This is a big boost in performance because instead of a lot of joins to get the product attributes Magento only reads from one table (actually 2, the flat table and the prices index table).
IN the product view page the flat table is not used. Instead all the attributes are loaded from the EAV tables.

Answer 2

Your EAV attribute is used as a column of flat table (catalog_product_flat_*) if:

• Use In Layered Navigation ->not No
• Use for Promo Rule Conditions ->yes
• Used in Product Listing ->yes
• Used for Sorting in Product Listing ->yes
• Attribute type is 'static'
• And attribute is system attribute like [status, tax_class_id, price, weight]

Flat tables have great performance rather than multiple joins of EAV structure when loading collection of products. Flat tables of category (catalog_category_flat_store_*) is also like above.

Answer 3

Eav ;- EAV is an entity attribute value database model, where data is fully in normalized form. Each column data value is stored in their respective data type table. Flat table the Flat model uses only one table, therefore it’s not normalized and uses more database space. It clears the EAV overhead

Note:- For small quantity data we use Flat table insted of EAV table, because EAV take more space that the flat table so getting data from a single table is faster that the Eav table