Best Practices for Semantic Data Modeling for Perfor… EAV is notoriusly problematic as it leads to severe deployment performance and scalability problems. The Whitepaper in the link, released by the SQL Server Customer Advisory Team tries to offer some guidance to deploy a succesful EAV model.
Here you go. An illustrative story: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
Applying normalization to your problem, the solution is as given. Run and see it on SQL Fiddle. CREATE TABLE products ( product_id int AUTO_INCREMENT PRIMARY KEY, name varchar(20), description varchar(30) ); INSERT INTO products (name, description) VALUES (‘Rug’, ‘A cool rug’ ), (‘Cup’, ‘A coffee cup’); — ======================================== CREATE TABLE variants ( variant_id int AUTO_INCREMENT … Read more
There is a difference between EAV done faithfully or badly; 5NF done by skilled people or by those who are clueless. Sixth Normal Form is the Irreducible Normal Form (no further Normalisation is possible). It eliminates many of the problems that are common, such as The Null Problem, and provides the ultimate method identifying missing … Read more
You’re making a common mistake of database design, storing name in one column and value in another column. This is not a relational database design. Each attribute should be named by the column name. Color, pages, shirt size, publish date, should be column names. If each product type has a distinct set of attributes, there … Read more
Almost all Magento Models have a corresponding Collection object that can be used to fetch multiple instances of a Model. To instantiate a Product collection, do the following $collection = Mage::getModel(‘catalog/product’)->getCollection(); Products are a Magento EAV style Model, so you’ll need to add on any additional attributes that you want to return. $collection = Mage::getModel(‘catalog/product’)->getCollection(); … Read more
This is not to be considered an exhaustive answer, but just a few points on the topic. Since the question is also tagged with the [sql] tag, let me say that, in general, relational databases aren’t particularly suitable for storing data using the EAV model. You can still design an EAV model in SQL, but … Read more
You can use such methods within your setup script: Use Varien_Db_Ddl_Table class to create new tables, where you can configure all the fields, keys, relations in combination with $this->getConnection()->createTable($tableObject) Example: /* @var $this Mage_Core_Model_Resource_Setup */ $table = new Varien_Db_Ddl_Table(); $table->setName($this->getTable(‘module/table’)); $table->addColumn(‘id’, Varien_Db_Ddl_Table::TYPE_INT, 10, array(‘unsigned’ => true, ‘primary’ => true)); $table->addColumn(‘name’, Varien_Db_Ddl_Table::TYPE_VARCHAR, 255); $table->addIndex(‘name’, ‘name’); $table->setOption(‘type’, … Read more
Great question, but of course, there is no “one true way”. As per @BenV, Magento does use the EAV model. My experience with it has been overwhelmingly positive, however it does trip up other users. Some considerations: 1. Performance. EAV requires complex, multi-table joins to populate your object with the relevant attributes. That does incur … Read more
You could have a design like: +—————+ +——————-+ | PRODUCTS |—–< PRODUCT_VARIANTS | +—————+ +——————-+ | #product_id | | #product_id | | product_name | | #variant_id | +—————+ | sku_id | | +——————-+ | | +——–^——–+ +——–^——–+ | PRODUCT_OPTIONS |—–< VARIANT_VALUES | +—————–+ +—————–+ | #product_id | | #product_id | | #option_id | | #variant_id … Read more