Entity-Attribute-Value (EAV) Model and Product Catalog management

http://en.wikipedia.org/wiki/Entity–attribute–value_model

If you have ever implemented an E-commerce site or any site with a large product catalog, you are likely to have encountered some problems in the database design caused by the large variety in product attributes. A simplistic solution is just to add more columns to take care of different attributes for different products in the Products table. Another schema is using different Product tables for different product groups. However, the code to manipulate different tables then becomes one giant switch case statement, with different cases having different codes for different product tables.

The EAV model is a neat solution that helps to overcome the above problems; albeit with more complex SQL queries. EAV is also known as object–attribute–value model, vertical database model and open schema.

http://www.magentocommerce.com/knowledge-base/entry/magento-for-dev-part-7-advanced-orm-entity-attribute-value

Here is another example taken from the Magento e-commerce software site.
Even for the case of Magento, they have several models instead of using EAV for everything. As I have said before, "Don't use an axe to slaughter a chicken!" There is no need to use EAV if the products catalog can be modelled using a simpler schema.