Magento Database Types Explained

Introduction

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 customeremail@example.com.

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.

Magento utilizes the EAV model and the standard relational database schema to overcome some of the limitations of a pure EAV approach.

Magento Databases

Magento records data sets across multiple core tables. Common Magento database tables include:

• catalog_product_entity
• catalog_category_entity
• catalog_category_product
• customer_entity
• sales_order
• sales_order_item
• quote

Large ecommerce stores need to use many additional tables to effectively store, relate, and extract valuable data from the database.

Products

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_id is the primary key of the catalog_product_entity table. Each product receives its unique entity_id.
• entity_type_id - Magento separates entities into several categories such as customers, addresses, products, etc. The entity_type_id defines 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 attribute_set_id.
• 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.

The 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.

Customers

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 customer_entity table. The entity_id is immutable and is the primary key of the customer_entity table.
• entity_type_id - The entity_type_id value 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

The 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_id value is the primary key of the sales_order table.
• customer_id - This column is the unique identifier of the customer who placed this particular order. The customer_id value represents the customer's entity_id value from the customer_entity table.

• 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_id column is the primary key of the sales_order_item table. If a customer purchases multiple products within the same order, each product creates a new row in the sales_order_item table.
• name - The textual name of the product.
• product_id - The product_id corresponds to the entity_id value in the catalog_product_entity table.

• order_id - The order_id corresponds to the entity_id value in the sales_order table. 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 price column 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.

Conclusion

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.