Sales & Orders Database Structure

The Sales module is one of the most important modules in Magento 2, handling all aspects of orders, invoices, shipments, and payments. This module contains tables with the sales_* prefix.

Key Relationships

Sales Tables Relationships

Note: This is a simplified diagram showing the main relationships.

Core Sales Tables

sales_order

The main table for orders. Each row represents a single order in the system.

Column Type Description
entity_id int Primary key
increment_id varchar Human-readable order number (e.g., 100000001)
state varchar Current order state (new, processing, complete, etc.)
status varchar Current order status (can be customized)
customer_id int Foreign key to customer_entity.entity_id
grand_total decimal Total order amount including tax and shipping
subtotal decimal Order subtotal without tax and shipping
created_at timestamp When the order was created
updated_at timestamp When the order was last updated
store_id smallint Store view ID where order was placed

sales_order_item

Contains individual items within an order. One order can have multiple items.

Column Type Description
item_id int Primary key
order_id int Foreign key to sales_order.entity_id
product_id int Foreign key to catalog_product_entity.entity_id
sku varchar Product SKU
name varchar Product name
qty_ordered decimal Quantity ordered
price decimal Item price
row_total decimal Total for this item (price × quantity)

sales_order_address

Stores billing and shipping addresses for orders.

Column Type Description
entity_id int Primary key
parent_id int Foreign key to sales_order.entity_id
address_type varchar 'billing' or 'shipping'
firstname varchar First name
lastname varchar Last name
street varchar Street address
city varchar City
region varchar State/Province
postcode varchar Postal code
country_id varchar Country code

Payment & Transaction Tables

sales_order_payment

Stores payment information for orders.

Column Type Description
entity_id int Primary key
parent_id int Foreign key to sales_order.entity_id
method varchar Payment method code
amount_ordered decimal Original order amount
amount_paid decimal Amount paid

Invoice Tables

sales_invoice

Stores invoice information. Each invoice is linked to an order.

Column Type Description
entity_id int Primary key
order_id int Foreign key to sales_order.entity_id
increment_id varchar Human-readable invoice number
created_at timestamp When the invoice was created
grand_total decimal Total invoice amount
state int Invoice state (1=open, 2=paid, 3=canceled)

Shipment Tables

sales_shipment

Stores shipment information. Each shipment is linked to an order.

Column Type Description
entity_id int Primary key
order_id int Foreign key to sales_order.entity_id
increment_id varchar Human-readable shipment number
created_at timestamp When the shipment was created
total_qty decimal Total quantity shipped

Credit Memo Tables

sales_creditmemo

Stores credit memo (refund) information. Each credit memo is linked to an order.

Column Type Description
entity_id int Primary key
order_id int Foreign key to sales_order.entity_id
increment_id varchar Human-readable credit memo number
created_at timestamp When the credit memo was created
grand_total decimal Total refund amount
state int Credit memo state

Data Analysis Tips

When analyzing sales data:

  • Join sales_order with sales_order_item to get product-level order data
  • Use created_at timestamps for time-based analysis
  • The state and status fields in sales_order are crucial for order flow analysis
  • For revenue analysis, use grand_total from sales_order table