Magento 2 Database Overview

Magento 2 Database Overview – Tables, Structure, and Relationships

Magento 2 is a powerful eCommerce platform with a complex and flexible database structure. Whether you’re a developer, store admin, or curious learner, understanding how the database is organized can help you work more efficiently and avoid costly mistakes.

🔍 Why Understand the Magento 2 Database?

  • To write optimized SQL queries or custom modules
  • To debug issues or analyze store data
  • To understand how products, customers, orders, and settings are stored

📦 Magento 2 Database Architecture

Magento 2 uses a **MySQL relational database** with hundreds of tables. It separates different kinds of data into logical groups like products, customers, orders, configuration, and logs.

🔧 Entity-Attribute-Value (EAV) Model

Magento 2 uses EAV for storing flexible entity data like products and customers.

What is EAV?
EAV stands for Entity–Attribute–Value. Instead of storing all attributes in one table, Magento splits them into separate tables.

  • catalog_product_entity: Base product table (entity)
  • catalog_product_entity_varchar: Stores string attributes
  • catalog_product_entity_decimal: Decimal values like price
  • catalog_product_entity_int: Integer attributes like status

This model makes Magento highly flexible but more complex to query.

🗃️ Flat Tables vs EAV

  • EAV Tables – Flexible, but complex and slower for large queries
  • Flat Tables – Denormalized tables for faster frontend performance (e.g., catalog_product_flat)

🧩 Core Table Categories in Magento 2

Category Examples Description
Products catalog_product_* Stores product info (EAV model)
Categories catalog_category_* Handles product category structure
Customers customer_* Customer accounts and attributes (EAV)
Sales sales_order, sales_invoice Orders, invoices, shipments
CMS cms_page, cms_block Static content management
Configuration core_config_data Stores site-wide system configuration
Admin admin_user Admin user accounts

🔄 Key Table Relationships

  • sales_order → sales_order_item: Order to its items
  • customer_entity → customer_address_entity: Customer to addresses
  • catalog_category_product: Many-to-many between products and categories

🛠️ Developer Tips

  • Use Magento’s models and repositories instead of raw SQL when possible
  • Use tools like phpMyAdmin, Adminer, or MySQL Workbench to visualize schema
  • To debug or inspect, use: SHOW TABLES; or DESCRIBE table_name;

📌 Handy SQL Example: List All Products

SELECT e.entity_id, sku, value AS name
FROM catalog_product_entity AS e
JOIN catalog_product_entity_varchar AS v 
  ON e.entity_id = v.entity_id 
  AND v.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code = 'name' 
    AND entity_type_id = 4
  )
LIMIT 10;

Try It Now

✅ Summary

The Magento 2 database may look overwhelming at first, but once you understand its modular design and how EAV works, it becomes a powerful tool. With careful exploration, you’ll be able to build custom features, optimize performance, and become a true Magento pro!