Magento provides ecommerce solutions to businesses regardless of their type or size. In order to support a wide variety of business models Magento's database needs to be flexible and scalable.
Magento databases use the EAV model to store data. This approach enables merchants to add and update attributes without modifying the core database schema.
Learn everything about Magento database types and take full advantage of its atypical database structure.
EAV Data Model
The EAV (Entity-Attribute-Value) data model stores entities, attributes, and attribute-value pairs in separate tables.
- Entity - An entity is an individual customer, product, order, etc. Every entity needs to have a unique database record.
- Attribute - Attributes describe an entity. For example, a product has a color, size, or price, while a customer has a name or email. Attribute tables contain columns with attribute IDs, names, data types, as well as columns for validating input values.
- Value - The value is paired with an attribute. For example, the attribute Price has the value $11, while the value of the attribute Email is firstname.lastname@example.org.
Ecommerce stores need to use lots of attributes to describe different entities. However, only a small number of attributes are used for individual entity-attribute pairs. This means that the majority of rows in a conventional database model contain a NULL value.
In a relational schema, attributes are stored as columns, while the EAV model records attributes in rows. This approach allows database administrators to add and modify attributes easily and also avoid storing empty values.
|EAV Advantages||EAV Drawbacks|
|Scalable and more flexible than the traditional relational model.||Queries require multiple table joins and are slower than in relational databases.|
|Administrators can introduce new attributes without altering the database structure.||Tasks that involve large volumes of EAV structured data may require additional logic and tools.|
|Conserves space by not storing NULL values.||Developers unfamiliar with the EAV model may encounter a steep learning curve.|
Magento utilizes the EAV model and the standard relational database schema to overcome some of the limitations of a pure EAV approach.
Magento records data sets across multiple core tables. Common Magento database tables include:
Large ecommerce stores need to use many additional tables to effectively store, relate, and extract valuable data from the database.
The rows of the
catalog_product_entity table represent individual products. When an administrator creates a new product in the Magento Admin, the system inserts a new table row.
The entry typically contains the following attributes:
- entity_id - The
entity_idis the primary key of the
catalog_product_entitytable. Each product receives its unique
- entity_type_id - Magento separates entities into several categories such as customers, addresses, products, etc. The
entity_type_iddefines the category. For example, the number 4 is the default value of the product category.
- attribute_set_id - Products that share the same set of descriptors have the same
- sku - Every product created in Magento receives a unique SKU (Stock Keeping Unit) designation.
- created_at - Displays the time and date when each product was created.
catalog_category_product establishes a relationship between products and categories. If a product appears in several categories, it can have multiple table entries.
Rows in the
catalog_category_entity table contain data about individual categories.
A row in the
customer_entity table contains data about a registered account/user. The table contains basic customer information such as the customer's name, email, address, etc.
- entity_id - When a customer signs up, the system creates a new row within the
entity_idis immutable and is the primary key of the
- entity_type_id - The
entity_type_idvalue shows which type of entity this record represents. The default value for the customer entity in Magento is 1.
- email - This field records the email address a customer enters when creating an account.
- created_at - This column shows the time and date the user joined the website.
Data about every user that signs up on a Magento website is stored in the
customer_entity table even if they do not complete their purchase.
sales_order table registers orders made on the website indicates which customer made the order, the order total, and when the customer made the order.
- entity_id - Once a customer creates an order, the system creates a unique id value. The
entity_idvalue is the primary key of the
- customer_id - This column is the unique identifier of the customer who placed this particular order. The
customer_idvalue represents the customer's
entity_idvalue from the
- subtotal - The subtotal represents the total amount the customer paid using this individual order.
- created_at - The exact date and time when the customer created the order.
Rows in the
sales_order_item table record individual order items. Each row contains details about individual products in each order, the quantity purchased, and the order associated with the item.
- item_id - The
item_idcolumn is the primary key of the
sales_order_itemtable. If a customer purchases multiple products within the same order, each product creates a new row in the
- name - The textual name of the product.
- product_id - The
product_idcorresponds to the
entity_idvalue in the
- order_id - The
order_idcorresponds to the
entity_idvalue in the
sales_ordertable. It relates an individual order item to the order.
- qty_ordered - This column shows the number of units of the product included in a specific order.
- price - The
pricecolumn contains prices for each product within the customer's order.
Magento stores shopping cart instances in the
quotes table. The system stores both converted and abandoned shopping carts. The number of records can inflate the
quotes table over time. It is necessary to delete records such as unconverted cart entries periodically.
You learned how the EAV model works and how Magento utilizes EAV to enable merchants to manage databases and introduce new attributes.
This approach gives Magento merchants lots of flexibility and the opportunity to grow their product portfolio and customer base without compromising database speeds and performance.