There is no magic, 3 years back I was dreaming to build a all-in-one b2b portal(unsuccessful of course), to store attributes for different products, I use two tables for product, a primary table contains all common columns, frequent query is against this table, so you have good performance. meanwhile thru pkey its links to a secondary table, it contains pkey columns, "colname" column,"value" column, so a product type specific attributes can be stored. To project all attributes is still ok, but search based on secondary table columns is performance killer. The where clause can be very complicated, if DBMS does not support nested sql, there is even more problem.
Recently I saw a workflow product use the same design to store user defined properties.