Database, EAV, and Data Modeling
Flat tables versus EAV
Commerce uses flat tables for transactional data (quote, order, sales) and EAV for entities with variable attributes (products, customers, categories). EAV has entity, attribute, and value tables (catalog_product_entity_varchar, _int, _decimal, _text, _datetime). Backend type determines storage table; frontend input determines rendering. Know attribute properties: type, backend, frontend, source, global vs website vs store scope, and used_in_forms.
Setup and schema
Declarative schema (db_schema.xml) defines tables, columns, indexes, foreign keys, and constraints; data patches handle data migration. Know that schema changes require bin/magento setup:upgrade, and DI compilation regenerates factories/proxies. Indexers (e.g., catalog_product_price, catalogsearch_fulltext) transform EAV data into flat index tables for query performance; invalidated indexers are processed by cron or manually.
Queries and collections
Collections use \Magento\Framework\Model\ResourceModel\Collection with addFieldToFilter, addAttributeToSelect, and setPageSize. Know the difference between addFieldToFilter on EAV attributes (joins) versus static columns. Avoid loading full collections; use getSelect() with joins for reporting.
Traps
- Assuming a custom product attribute is queryable without an index or flat table.
- Forgetting store-scope values require the store ID in the collection.
- Confusing backend_type (storage) with frontend_input (form control).
- Editing db_schema.xml without running setup:upgrade.
Rule of thumb
If an attribute varies per product and is rarely filtered, use EAV; if it is queried heavily, add it to a flat/index table. Example: a "clearance" flag filtered on category pages should be indexed, not read from EAV per request.