Skip to content
intermediate Phase 37 · Database Fundamentals

Magento Entity Tables

Core entity tables in Magento: catalog_product_entity, catalog_category_entity, customer_entity, sales_order

45m
0 problems
Topic Progress 0%

Catalog Product Entity

catalog_product_entity Table

The central table for all products in Magento:

CREATE TABLE catalog_product_entity (
    entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
    attribute_set_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
    type_id VARCHAR(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
    sku VARCHAR(255) DEFAULT NULL COMMENT 'SKU',
    has_options SMALLINT NOT NULL DEFAULT 0 COMMENT 'Has Options',
    required_options SMALLINT NOT NULL DEFAULT 0 COMMENT 'Required Options',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
    PRIMARY KEY (entity_id),
    UNIQUE INDEX IDX_SKU (sku),
    INDEX IDX_ATTRIBUTE_SET_ID (attribute_set_id),
    INDEX IDX_TYPE_ID (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Catalog Product Entity';

-- Query products with basic info
SELECT 
    entity_id,
    sku,
    type_id,
    attribute_set_id,
    has_options,
    created_at,
    updated_at
FROM catalog_product_entity
WHERE type_id = 'simple'
LIMIT 10;

Product Entity Fields

Field Description Example
entity_id Unique product identifier 123
attribute_set_id Links to attribute set 4
type_id Product type simple, configurable
sku Stock keeping unit 'TSHIRT-001'
has_options Product has custom options 0 or 1
required_options Options are required 0 or 1

Catalog Category Entity

catalog_category_entity Table

Stores category hierarchy and metadata:

CREATE TABLE catalog_category_entity (
    entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
    attribute_set_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
    parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Parent Category ID',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
    position INT NOT NULL DEFAULT 0 COMMENT 'Position',
    level INT NOT NULL DEFAULT 0 COMMENT 'Level',
    children_count INT NOT NULL DEFAULT 0 COMMENT 'Children Count',
    PRIMARY KEY (entity_id),
    INDEX IDX_PARENT_ID (parent_id),
    INDEX IDX_LEVEL (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Catalog Category Entity';

Category Tree Structure

-- The path column stores the full ancestor path
-- Format: /root_id/parent_id/category_id

SELECT 
    entity_id,
    parent_id,
    path,
    level,
    children_count,
    position
FROM catalog_category_entity
ORDER BY path;

+-----------+-----------+---------+-------+---------------+----------+
| entity_id | parent_id | path    | level | children_count | position |
+-----------+-----------+---------+-------+---------------+----------+
|         1 |         0 | 1       |     0 |              1 |        0 |
|         2 |         1 | 1/2     |     1 |              3 |        0 |
|         3 |         2 | 1/2/3   |     2 |              2 |        1 |
|         4 |         2 | 1/2/4   |     2 |              0 |        2 |
|         5 |         3 | 1/2/3/5 |     3 |              0 |        1 |
+-----------+-----------+---------+-------+---------------+----------+

// Get category path components
$path = '1/2/3/5';
$ids = explode('/', $path);
// Returns [1, 2, 3, 5] - all ancestor IDs

Key Relationships

-- category_id to product_id mapping
SELECT * FROM catalog_category_product
WHERE category_id = 3;
+-------------+------------+-----------+
| category_id | product_id | position  |
+-------------+------------+-----------+
|           3 |         10 |         1 |
|           3 |         15 |         2 |
|           3 |         20 |         3 |
+-------------+------------+-----------+

Customer Entity

customer_entity Table

Stores customer account information:

CREATE TABLE customer_entity (
    entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
    email VARCHAR(255) DEFAULT NULL COMMENT 'Email',
    group_id SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Group ID',
    default_billing INT UNSIGNED DEFAULT NULL COMMENT 'Default Billing',
    default_shipping INT UNSIGNED DEFAULT NULL COMMENT 'Default Shipping',
    confirmation VARCHAR(255) DEFAULT NULL COMMENT 'Confirmation Key',
    created_in VARCHAR(255) DEFAULT NULL COMMENT 'Created In',
    dob DATE DEFAULT NULL COMMENT 'Date of Birth',
    gender SMALLINT UNSIGNED DEFAULT NULL COMMENT 'Gender',
    prefix VARCHAR(40) DEFAULT NULL COMMENT 'Name Prefix',
    middlename VARCHAR(40) DEFAULT NULL COMMENT 'Middle Name',
    suffix VARCHAR(40) DEFAULT NULL COMMENT 'Name Suffix',
    default_store SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Default Store',
    group_id SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Customer Group',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
    failure_num SMALLINT UNSIGNED DEFAULT 0 COMMENT 'Failure Number',
    first_login TIMESTAMP NULL DEFAULT NULL COMMENT 'First Login At',
    last_login TIMESTAMP NULL DEFAULT NULL COMMENT 'Last Login At',
    PRIMARY KEY (entity_id),
    UNIQUE INDEX IDX_EMAIL (email),
    INDEX IDX_GROUP_ID (group_id),
    INDEX IDX_DEFAULT_BILLING (default_billing)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customer Entity';

Customer Address Entity

CREATE TABLE customer_address_entity (
    entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
    parent_id INT UNSIGNED NOT NULL COMMENT 'Customer ID',
    region_id INT UNSIGNED DEFAULT NULL COMMENT 'Region ID',
    country_id VARCHAR(2) DEFAULT NULL COMMENT 'Country ID',
    postcode VARCHAR(255) DEFAULT NULL COMMENT 'Zip/Postal Code',
    fax VARCHAR(255) DEFAULT NULL COMMENT 'Fax Number',n    city VARCHAR(255) DEFAULT NULL COMMENT 'City',
    name VARCHAR(255) DEFAULT NULL COMMENT 'Name',
    street VARCHAR(255) DEFAULT NULL COMMENT 'Street Address',
    telephone VARCHAR(255) DEFAULT NULL COMMENT 'Phone Number',
    company VARCHAR(255) DEFAULT NULL COMMENT 'Company',
    region VARCHAR(255) DEFAULT NULL COMMENT 'Region',
    PRIMARY KEY (entity_id),
    INDEX IDX_PARENT_ID (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customer Address Entity';

// Customer relationships
$customer->getId();           // entity_id
$customer->getPrimaryBillingAddress();   // default_billing → customer_address_entity
$customer->getPrimaryShippingAddress();  // default_shipping → customer_address_entity
$customer->getAddresses();    // All addresses for this customer

Sales Order Tables

sales_order Table

Core order information (non-EAV, flat structure):

CREATE TABLE sales_order (
    entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
    state VARCHAR(32) DEFAULT NULL COMMENT 'State',
    status VARCHAR(32) DEFAULT NULL COMMENT 'Status',
    coupon_code VARCHAR(255) DEFAULT NULL COMMENT 'Coupon Code',
    protect_code VARCHAR(255) DEFAULT NULL COMMENT 'Protect Code',
    shipping_description VARCHAR(255) DEFAULT NULL COMMENT 'Shipping Description',
    customer_id INT UNSIGNED DEFAULT NULL COMMENT 'Customer ID',
    base_discount_amount DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Discount Amount',
    base_grand_total DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Grand Total',
    base_shipping_amount DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
    base_subtotal DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Subtotal',
    base_tax_amount DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Tax Amount',
    discount_amount DECIMAL(12,4) DEFAULT NULL COMMENT 'Discount Amount',
    grand_total DECIMAL(12,4) DEFAULT NULL COMMENT 'Grand Total',
    shipping_amount DECIMAL(12,4) DEFAULT NULL COMMENT 'Shipping Amount',
    subtotal DECIMAL(12,4) DEFAULT NULL COMMENT 'Subtotal',
    tax_amount DECIMAL(12,4) DEFAULT NULL COMMENT 'Tax Amount',
    store_id SMALLINT UNSIGNED DEFAULT NULL COMMENT 'Store ID',
    customer_email VARCHAR(255) DEFAULT NULL COMMENT 'Customer Email',
    customer_firstname VARCHAR(255) DEFAULT NULL COMMENT 'Customer First Name',
    customer_lastname VARCHAR(255) DEFAULT NULL COMMENT 'Customer Last Name',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
    PRIMARY KEY (entity_id),
    INDEX IDX_CUSTOMER_ID (customer_id),
    INDEX IDX_STORE_ID (store_id),
    INDEX IDX_STATE (state),
    INDEX IDX_CREATED_AT (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Sales Order';

Order Item Table

CREATE TABLE sales_order_item (
    item_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Item ID',
    order_id INT UNSIGNED NOT NULL COMMENT 'Order ID',
    parent_item_id INT UNSIGNED DEFAULT NULL COMMENT 'Parent Item ID',
    sku VARCHAR(255) DEFAULT NULL COMMENT 'SKU',
    name VARCHAR(255) DEFAULT NULL COMMENT 'Name',
    qty_ordered DECIMAL(12,4) DEFAULT NULL COMMENT 'Qty Ordered',
    price DECIMAL(12,4) DEFAULT NULL COMMENT 'Price',
    base_price DECIMAL(12,4) DEFAULT NULL COMMENT 'Base Price',
    discount_amount DECIMAL(12,4) DEFAULT NULL COMMENT 'Discount Amount',
    row_total DECIMAL(12,4) DEFAULT NULL COMMENT 'Row Total',
    product_id INT UNSIGNED DEFAULT NULL COMMENT 'Product ID',
    PRIMARY KEY (item_id),
    INDEX IDX_ORDER_ID (order_id),
    INDEX IDX_PRODUCT_ID (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Sales Order Item';

Order Relationship Map

sales_order (1) ──→ (N) sales_order_item
sales_order (1) ──→ (N) sales_order_payment
sales_order (1) ──→ (N) sales_order_status_history
sales_order (1) ──→ (1) sales_order_address (shipping)
sales_order (1) ──→ (1) sales_order_address (billing)

sales_order (N) ←── (1) customer_entity
sales_order_item (N) ←── (1) catalog_product_entity

Quiz

1. What does the 'path' column in catalog_category_entity store?

Question 1 options

2. What is the relationship between customer_entity and customer_address_entity?

Question 2 options

3. Why does sales_order use a flat structure instead of EAV?

Question 3 options

Flashcards

Question

What is catalog_product_entity?

Click to reveal answer

Answer

Core table storing product metadata: entity_id, sku, type_id, attribute_set_id

Question

What does the category path represent?

Click to reveal answer

Answer

Slash-separated ancestor IDs showing full hierarchy: root/parent/category

Question

How are customer addresses linked?

Click to reveal answer

Answer

Via default_billing and default_shipping foreign keys to customer_address_entity

Question

What are the main sales_order columns?

Click to reveal answer

Answer

entity_id, state, status, customer_id, grand_total, store_id, created_at

Question

What links sales_order_item to products?

Click to reveal answer

Answer

product_id foreign key to catalog_product_entity.entity_id

Revision Notes

Key Takeaways

  • 1. catalog_product_entity stores product metadata (entity_id, sku, type_id)
  • 2. catalog_category_entity uses path column for hierarchy (1/2/3/5)
  • 3. customer_entity links to addresses via default_billing/default_shipping
  • 4. sales_order uses flat structure for performance, not EAV
  • 5. sales_order_item links to products via product_id

Interview Tips

  • • Explain the category tree structure using the path column
  • • Discuss why orders use flat tables instead of EAV
  • • Describe customer-address relationships and default selection

Cheat Sheet

Entity Tables:
  catalog_product_entity   → entity_id, sku, type_id, attribute_set_id
  catalog_category_entity  → entity_id, parent_id, path, level, position
  customer_entity          → entity_id, email, group_id, default_billing
  customer_address_entity  → entity_id, parent_id, street, city, country_id
  sales_order              → entity_id, customer_id, state, grand_total
  sales_order_item         → item_id, order_id, product_id, qty_ordered, price

Category Path:
  '1/2/3/5' → root=1, parent=2, category=3, leaf=5