Catalog Database Structure

The Catalog module is the core of Magento's product management system. It stores all product, category, and attribute information. This module primarily uses the EAV (Entity-Attribute-Value) pattern, which allows for flexible attribute management but creates a more complex database structure.

Key Relationships

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

Understanding EAV Structure

The main entity tables (like catalog_product_entity) only store basic entity information
Actual attribute values are stored in separate value tables based on data type (varchar, int, decimal, text, datetime)
To get complete product information, you need to join multiple tables

Product Tables

catalog_product_entity

The main product entity table. Each row represents a unique product in the system.

Column	Type	Description
entity_id	int	Primary key
sku	varchar	Product SKU (Stock Keeping Unit)
type_id	varchar	Product type (simple, configurable, bundle, etc.)
attribute_set_id	smallint	Foreign key to eav_attribute_set.attribute_set_id
created_at	timestamp	When the product was created
updated_at	timestamp	When the product was last updated

catalog_product_entity_* (EAV Value Tables)

These tables store the actual attribute values for products. There are separate tables for different data types.

Table	Data Type	Example Attributes
catalog_product_entity_varchar	String values	name, url_key, image, thumbnail
catalog_product_entity_int	Integer values	status, visibility, tax_class_id
catalog_product_entity_decimal	Decimal values	price, special_price, weight
catalog_product_entity_text	Long text values	description, short_description
catalog_product_entity_datetime	Date/time values	special_from_date, special_to_date

Category Tables

catalog_category_entity

The main category entity table. Each row represents a category in the catalog.

Column	Type	Description
entity_id	int	Primary key
parent_id	int	Parent category ID (forms a tree structure)
path	varchar	Path from root category (e.g., "1/2/3")
position	int	Position within parent category
level	int	Depth in category tree (root = 0)
created_at	timestamp	When the category was created
updated_at	timestamp	When the category was last updated

catalog_category_product

Maps products to categories (many-to-many relationship).

Column	Type	Description
entity_id	int	Primary key
category_id	int	Foreign key to catalog_category_entity.entity_id
product_id	int	Foreign key to catalog_product_entity.entity_id
position	int	Product position within category

Product Relationship Tables

catalog_product_relation

Stores relationships between parent and child products (e.g., configurable products and their simple variants).

Column	Type	Description
parent_id	int	Foreign key to catalog_product_entity.entity_id (parent product)
child_id	int	Foreign key to catalog_product_entity.entity_id (child product)

catalog_product_link

Stores product links (related, up-sell, cross-sell, etc.).

Column	Type	Description
link_id	int	Primary key
product_id	int	Foreign key to catalog_product_entity.entity_id (source product)
linked_product_id	int	Foreign key to catalog_product_entity.entity_id (target product)
link_type_id	smallint	Type of link (1=related, 4=up-sell, 5=cross-sell)

Price Tables

catalog_product_index_price

Indexed product prices for faster catalog browsing. This is a denormalized table for performance.

Column	Type	Description
entity_id	int	Foreign key to catalog_product_entity.entity_id
customer_group_id	smallint	Customer group ID
website_id	smallint	Website ID
price	decimal	Base price
final_price	decimal	Final price after discounts
min_price	decimal	Minimum price (for configurable products)
max_price	decimal	Maximum price (for configurable products)

Data Analysis Tips

For basic product information, use EAV joins on value tables.
For product-category relationships, join catalog_category_product with catalog_category_entity and catalog_product_entity.
For price analysis, use catalog_product_index_price for better performance.