405 lines
24 KiB
Plaintext
405 lines
24 KiB
Plaintext
-- Essential Magento 2 Core Table Schemas (Curated)
|
|
-- For LLM Question Generation Context
|
|
|
|
-- ========= Product Related =========
|
|
|
|
-- catalog_product_entity: Core product table
|
|
DROP TABLE IF EXISTS `catalog_product_entity`;
|
|
CREATE TABLE `catalog_product_entity` (
|
|
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
|
|
`attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
|
|
`type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
|
|
`sku` varchar(64) NOT NULL COMMENT 'SKU',
|
|
`has_options` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',
|
|
`required_options` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Required Options',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Creation Time',
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Update Time',
|
|
PRIMARY KEY (`entity_id`),
|
|
KEY `CATALOG_PRODUCT_ENTITY_SKU` (`sku`)
|
|
) ENGINE=InnoDB COMMENT='Catalog Product Table';
|
|
|
|
-- eav_attribute: Defines attributes (used by products, categories, customers)
|
|
DROP TABLE IF EXISTS `eav_attribute`;
|
|
CREATE TABLE `eav_attribute` (
|
|
`attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Attribute ID',
|
|
`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity Type ID',
|
|
`attribute_code` varchar(255) NOT NULL COMMENT 'Attribute Code',
|
|
`backend_type` varchar(8) NOT NULL DEFAULT 'static' COMMENT 'Backend Type',
|
|
`frontend_input` varchar(50) DEFAULT NULL COMMENT 'Frontend Input',
|
|
`frontend_label` varchar(255) DEFAULT NULL COMMENT 'Frontend Label',
|
|
`is_required` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Defines Is Required',
|
|
`is_user_defined` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Defines Is User Defined',
|
|
PRIMARY KEY (`attribute_id`),
|
|
UNIQUE KEY `EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE` (`entity_type_id`,`attribute_code`)
|
|
) ENGINE=InnoDB COMMENT='Eav Attribute';
|
|
|
|
-- eav_entity_type: Defines entity types for EAV
|
|
DROP TABLE IF EXISTS `eav_entity_type`;
|
|
CREATE TABLE `eav_entity_type` (
|
|
`entity_type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Type ID',
|
|
`entity_type_code` varchar(50) NOT NULL COMMENT 'Entity Type Code',
|
|
`entity_table` varchar(255) DEFAULT NULL COMMENT 'Entity Table',
|
|
PRIMARY KEY (`entity_type_id`)
|
|
) ENGINE=InnoDB COMMENT='Eav Entity Type';
|
|
|
|
-- catalog_product_entity_varchar: Product Varchar Attributes (e.g., name)
|
|
DROP TABLE IF EXISTS `catalog_product_entity_varchar`;
|
|
CREATE TABLE `catalog_product_entity_varchar` (
|
|
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
|
|
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
|
|
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
|
|
`entity_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity ID',
|
|
`value` varchar(255) DEFAULT NULL COMMENT 'Value',
|
|
PRIMARY KEY (`value_id`),
|
|
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
|
|
CONSTRAINT `CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
|
|
CONSTRAINT `CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Catalog Product Varchar Attribute Backend Table';
|
|
|
|
-- catalog_product_entity_int: Product Integer Attributes (e.g., status, visibility)
|
|
DROP TABLE IF EXISTS `catalog_product_entity_int`;
|
|
CREATE TABLE `catalog_product_entity_int` (
|
|
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
|
|
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
|
|
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
|
|
`entity_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity ID',
|
|
`value` int(11) DEFAULT NULL COMMENT 'Value',
|
|
PRIMARY KEY (`value_id`),
|
|
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
|
|
CONSTRAINT `CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
|
|
CONSTRAINT `CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Catalog Product Integer Attribute Backend Table';
|
|
|
|
-- catalog_product_entity_decimal: Product Decimal Attributes (e.g., price, weight)
|
|
DROP TABLE IF EXISTS `catalog_product_entity_decimal`;
|
|
CREATE TABLE `catalog_product_entity_decimal` (
|
|
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
|
|
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
|
|
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
|
|
`entity_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity ID',
|
|
`value` decimal(20,6) DEFAULT NULL COMMENT 'Value',
|
|
PRIMARY KEY (`value_id`),
|
|
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
|
|
CONSTRAINT `CAT_PRD_ENTT_DEC_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
|
|
CONSTRAINT `CAT_PRD_ENTT_DEC_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Catalog Product Decimal Attribute Backend Table';
|
|
|
|
-- catalog_product_index_price: Indexed product prices (important for frontend)
|
|
DROP TABLE IF EXISTS `catalog_product_index_price`;
|
|
CREATE TABLE `catalog_product_index_price` (
|
|
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
|
|
`customer_group_id` int(10) unsigned NOT NULL COMMENT 'Customer Group ID',
|
|
`website_id` smallint(5) unsigned NOT NULL COMMENT 'Website ID',
|
|
`price` decimal(20,6) DEFAULT NULL COMMENT 'Price',
|
|
`final_price` decimal(20,6) DEFAULT NULL COMMENT 'Final Price',
|
|
`min_price` decimal(20,6) DEFAULT NULL COMMENT 'Min Price',
|
|
`max_price` decimal(20,6) DEFAULT NULL COMMENT 'Max Price',
|
|
PRIMARY KEY (`entity_id`,`customer_group_id`,`website_id`)
|
|
) ENGINE=InnoDB COMMENT='Catalog Product Price Index Table';
|
|
|
|
|
|
-- ========= Category Related =========
|
|
|
|
-- catalog_category_entity: Core category table
|
|
DROP TABLE IF EXISTS `catalog_category_entity`;
|
|
CREATE TABLE `catalog_category_entity` (
|
|
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
|
|
`parent_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Parent Category ID',
|
|
`path` varchar(255) NOT NULL COMMENT 'Tree Path',
|
|
`position` int(11) NOT NULL COMMENT 'Position',
|
|
`level` int(11) NOT NULL DEFAULT 0 COMMENT 'Tree Level',
|
|
`children_count` int(11) NOT NULL COMMENT 'Child Count',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Creation Time',
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Update Time',
|
|
PRIMARY KEY (`entity_id`),
|
|
KEY `CATALOG_CATEGORY_ENTITY_PATH` (`path`)
|
|
) ENGINE=InnoDB COMMENT='Catalog Category Table';
|
|
|
|
-- catalog_category_entity_varchar: Category Varchar Attributes (e.g., name)
|
|
DROP TABLE IF EXISTS `catalog_category_entity_varchar`;
|
|
CREATE TABLE `catalog_category_entity_varchar` (
|
|
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
|
|
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
|
|
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
|
|
`entity_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity ID',
|
|
`value` varchar(255) DEFAULT NULL COMMENT 'Value',
|
|
PRIMARY KEY (`value_id`),
|
|
UNIQUE KEY `CATALOG_CATEGORY_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
|
|
CONSTRAINT `CAT_CTGR_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
|
|
CONSTRAINT `CAT_CTGR_ENTT_VCHR_ENTT_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Catalog Category Varchar Attribute Backend Table';
|
|
|
|
-- catalog_category_product: Links products to categories
|
|
DROP TABLE IF EXISTS `catalog_category_product`;
|
|
CREATE TABLE `catalog_category_product` (
|
|
`entity_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Entity ID', -- This is just a link table ID, not product or category ID
|
|
`category_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Category ID',
|
|
`product_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Product ID',
|
|
`position` int(11) NOT NULL DEFAULT 0 COMMENT 'Position',
|
|
PRIMARY KEY (`entity_id`,`category_id`,`product_id`), -- Modified PK for clarity if entity_id is just an auto_increment
|
|
UNIQUE KEY `CATALOG_CATEGORY_PRODUCT_CATEGORY_ID_PRODUCT_ID` (`category_id`,`product_id`),
|
|
CONSTRAINT `CAT_CTGR_PRD_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE,
|
|
CONSTRAINT `CAT_CTGR_PRD_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Catalog Product To Category Linkage Table';
|
|
|
|
|
|
-- ========= Inventory Related =========
|
|
|
|
-- inventory_source_item: Multi-Source Inventory (MSI) stock levels
|
|
DROP TABLE IF EXISTS `inventory_source_item`;
|
|
CREATE TABLE `inventory_source_item` (
|
|
`source_item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`source_code` varchar(255) NOT NULL COMMENT 'Usually "default" for single source',
|
|
`sku` varchar(64) NOT NULL,
|
|
`quantity` decimal(12,4) NOT NULL DEFAULT 0.0000,
|
|
`status` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT '0 = Out of Stock, 1 = In Stock',
|
|
PRIMARY KEY (`source_item_id`),
|
|
UNIQUE KEY `INVENTORY_SOURCE_ITEM_SOURCE_CODE_SKU` (`source_code`,`sku`)
|
|
) ENGINE=InnoDB COMMENT='Inventory Source Item (MSI)';
|
|
|
|
-- cataloginventory_stock_status: Indexed stock status (often used by frontend)
|
|
DROP TABLE IF EXISTS `cataloginventory_stock_status`;
|
|
CREATE TABLE `cataloginventory_stock_status` (
|
|
`product_id` int(10) unsigned NOT NULL COMMENT 'Product ID',
|
|
`website_id` smallint(5) unsigned NOT NULL COMMENT 'Website ID',
|
|
`stock_id` smallint(5) unsigned NOT NULL COMMENT 'Stock ID (usually 1 for default)',
|
|
`qty` decimal(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Qty',
|
|
`stock_status` smallint(5) unsigned NOT NULL COMMENT 'Stock Status (0=Out of Stock, 1=In Stock)',
|
|
PRIMARY KEY (`product_id`,`website_id`,`stock_id`)
|
|
) ENGINE=InnoDB COMMENT='Cataloginventory Stock Status';
|
|
|
|
|
|
-- ========= Customer Related =========
|
|
|
|
-- customer_entity: Core customer table
|
|
DROP TABLE IF EXISTS `customer_entity`;
|
|
CREATE TABLE `customer_entity` (
|
|
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
|
|
`website_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Website ID',
|
|
`email` varchar(255) DEFAULT NULL COMMENT 'Email',
|
|
`group_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Group 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',
|
|
`is_active` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Is Active',
|
|
`firstname` varchar(255) DEFAULT NULL COMMENT 'First Name',
|
|
`lastname` varchar(255) DEFAULT NULL COMMENT 'Last Name',
|
|
`default_billing` int(10) unsigned DEFAULT NULL COMMENT 'Default Billing Address ID',
|
|
`default_shipping` int(10) unsigned DEFAULT NULL COMMENT 'Default Shipping Address ID',
|
|
PRIMARY KEY (`entity_id`),
|
|
UNIQUE KEY `CUSTOMER_ENTITY_EMAIL_WEBSITE_ID` (`email`,`website_id`)
|
|
) ENGINE=InnoDB COMMENT='Customer Entity';
|
|
|
|
-- customer_address_entity: Customer addresses
|
|
DROP TABLE IF EXISTS `customer_address_entity`;
|
|
CREATE TABLE `customer_address_entity` (
|
|
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
|
|
`parent_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer ID (Parent)',
|
|
`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',
|
|
`city` varchar(255) NOT NULL COMMENT 'City',
|
|
`country_id` varchar(255) NOT NULL COMMENT 'Country',
|
|
`firstname` varchar(255) NOT NULL COMMENT 'First Name',
|
|
`lastname` varchar(255) NOT NULL COMMENT 'Last Name',
|
|
`postcode` varchar(255) DEFAULT NULL COMMENT 'Zip/Postal Code',
|
|
`region_id` int(10) unsigned DEFAULT NULL COMMENT 'State/Province ID',
|
|
`street` text NOT NULL COMMENT 'Street Address',
|
|
`telephone` varchar(255) NOT NULL COMMENT 'Phone Number',
|
|
PRIMARY KEY (`entity_id`),
|
|
KEY `CUSTOMER_ADDRESS_ENTITY_PARENT_ID` (`parent_id`),
|
|
CONSTRAINT `CUSTOMER_ADDRESS_ENTITY_PARENT_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`parent_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Customer Address Entity';
|
|
|
|
-- customer_group: Defines customer groups
|
|
DROP TABLE IF EXISTS `customer_group`;
|
|
CREATE TABLE `customer_group` (
|
|
`customer_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`customer_group_code` varchar(32) NOT NULL COMMENT 'Customer Group Code',
|
|
`tax_class_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Tax Class ID',
|
|
PRIMARY KEY (`customer_group_id`)
|
|
) ENGINE=InnoDB COMMENT='Customer Group';
|
|
|
|
|
|
-- ========= Sales (Order) Related =========
|
|
|
|
-- sales_order: Core order table
|
|
DROP TABLE IF EXISTS `sales_order`;
|
|
CREATE TABLE `sales_order` (
|
|
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
|
|
`state` varchar(32) DEFAULT NULL COMMENT 'State (e.g. new, processing, complete)',
|
|
`status` varchar(32) DEFAULT NULL COMMENT 'Status (e.g. pending, processing, complete, canceled)',
|
|
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store ID',
|
|
`customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer ID',
|
|
`grand_total` decimal(20,4) DEFAULT NULL COMMENT 'Grand Total in order currency',
|
|
`base_grand_total` decimal(20,4) DEFAULT NULL COMMENT 'Base Grand Total in base currency',
|
|
`subtotal` decimal(20,4) DEFAULT NULL COMMENT 'Subtotal',
|
|
`base_subtotal` decimal(20,4) DEFAULT NULL COMMENT 'Base Subtotal',
|
|
`shipping_amount` decimal(20,4) DEFAULT NULL COMMENT 'Shipping Amount',
|
|
`base_shipping_amount` decimal(20,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
|
|
`tax_amount` decimal(20,4) DEFAULT NULL COMMENT 'Tax Amount',
|
|
`base_tax_amount` decimal(20,4) DEFAULT NULL COMMENT 'Base Tax Amount',
|
|
`discount_amount` decimal(20,4) DEFAULT NULL COMMENT 'Discount Amount',
|
|
`base_discount_amount` decimal(20,4) DEFAULT NULL COMMENT 'Base Discount Amount',
|
|
`customer_email` varchar(128) DEFAULT NULL COMMENT 'Customer Email',
|
|
`customer_firstname` varchar(128) DEFAULT NULL COMMENT 'Customer Firstname',
|
|
`customer_lastname` varchar(128) DEFAULT NULL COMMENT 'Customer Lastname',
|
|
`customer_group_id` int(11) DEFAULT NULL,
|
|
`increment_id` varchar(50) DEFAULT NULL COMMENT 'Order Increment ID (human readable)',
|
|
`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 KEY `SALES_ORDER_INCREMENT_ID_STORE_ID` (`increment_id`,`store_id`),
|
|
KEY `SALES_ORDER_CUSTOMER_ID` (`customer_id`),
|
|
CONSTRAINT `SALES_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB COMMENT='Sales Flat Order';
|
|
|
|
-- sales_order_item: Items within an order
|
|
DROP TABLE IF EXISTS `sales_order_item`;
|
|
CREATE TABLE `sales_order_item` (
|
|
`item_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Item ID',
|
|
`order_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Order ID',
|
|
`product_id` int(10) unsigned DEFAULT NULL COMMENT 'Product ID',
|
|
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store ID',
|
|
`sku` varchar(255) DEFAULT NULL COMMENT 'Sku',
|
|
`name` varchar(255) DEFAULT NULL COMMENT 'Name',
|
|
`qty_ordered` decimal(12,4) DEFAULT 0.0000 COMMENT 'Qty Ordered',
|
|
`price` decimal(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Price in order currency',
|
|
`base_price` decimal(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Base Price in base currency',
|
|
`row_total` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT 'Row Total',
|
|
`base_row_total` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT 'Base Row Total',
|
|
`discount_amount` decimal(20,4) DEFAULT 0.0000 COMMENT 'Discount Amount',
|
|
`base_discount_amount` decimal(20,4) DEFAULT 0.0000 COMMENT 'Base Discount Amount',
|
|
`tax_amount` decimal(20,4) DEFAULT 0.0000 COMMENT 'Tax Amount',
|
|
`base_tax_amount` decimal(20,4) DEFAULT 0.0000 COMMENT 'Base Tax Amount',
|
|
PRIMARY KEY (`item_id`),
|
|
KEY `SALES_ORDER_ITEM_ORDER_ID` (`order_id`),
|
|
CONSTRAINT `SALES_ORDER_ITEM_ORDER_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Sales Flat Order Item';
|
|
|
|
-- sales_order_payment: Payment information for an order
|
|
DROP TABLE IF EXISTS `sales_order_payment`;
|
|
CREATE TABLE `sales_order_payment` (
|
|
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
|
|
`parent_id` int(10) unsigned NOT NULL COMMENT 'Order ID (Parent)',
|
|
`method` varchar(128) DEFAULT NULL COMMENT 'Payment Method Code',
|
|
`amount_ordered` decimal(20,4) DEFAULT NULL COMMENT 'Amount Ordered',
|
|
`amount_paid` decimal(20,4) DEFAULT NULL COMMENT 'Amount Paid',
|
|
`cc_last_4` varchar(100) DEFAULT NULL COMMENT 'Cc Last 4 (if applicable)',
|
|
`cc_type` varchar(32) DEFAULT NULL COMMENT 'Cc Type (if applicable)',
|
|
PRIMARY KEY (`entity_id`),
|
|
KEY `SALES_ORDER_PAYMENT_PARENT_ID` (`parent_id`),
|
|
CONSTRAINT `SALES_ORDER_PAYMENT_PARENT_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`parent_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Sales Flat Order Payment';
|
|
|
|
-- sales_order_grid: Denormalized order data for admin grid
|
|
DROP TABLE IF EXISTS `sales_order_grid`;
|
|
CREATE TABLE `sales_order_grid` (
|
|
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID (Order ID)',
|
|
`status` varchar(32) DEFAULT NULL COMMENT 'Status',
|
|
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store ID',
|
|
`increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment ID',
|
|
`customer_name` varchar(255) DEFAULT NULL COMMENT 'Customer Name',
|
|
`customer_email` varchar(255) DEFAULT NULL COMMENT 'Customer Email',
|
|
`grand_total` decimal(20,4) DEFAULT NULL COMMENT 'Grand Total',
|
|
`base_grand_total` decimal(20,4) DEFAULT NULL COMMENT 'Base Grand Total',
|
|
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',
|
|
`billing_name` varchar(255) DEFAULT NULL COMMENT 'Billing Name',
|
|
`shipping_name` varchar(255) DEFAULT NULL COMMENT 'Shipping Name',
|
|
PRIMARY KEY (`entity_id`),
|
|
UNIQUE KEY `SALES_ORDER_GRID_INCREMENT_ID_STORE_ID` (`increment_id`,`store_id`)
|
|
) ENGINE=InnoDB COMMENT='Sales Flat Order Grid';
|
|
|
|
|
|
-- ========= Review Related =========
|
|
-- review: Core review table
|
|
DROP TABLE IF EXISTS `review`;
|
|
CREATE TABLE `review` (
|
|
`review_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Review ID',
|
|
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Review create date',
|
|
`entity_pk_value` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Product ID (usually)',
|
|
`status_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Status code (e.g. pending, approved)',
|
|
PRIMARY KEY (`review_id`),
|
|
KEY `REVIEW_ENTITY_PK_VALUE` (`entity_pk_value`)
|
|
) ENGINE=InnoDB COMMENT='Review base information';
|
|
|
|
-- review_detail: Details of the review
|
|
DROP TABLE IF EXISTS `review_detail`;
|
|
CREATE TABLE `review_detail` (
|
|
`detail_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Review detail ID',
|
|
`review_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Review ID',
|
|
`store_id` smallint(5) unsigned DEFAULT 0 COMMENT 'Store ID',
|
|
`title` varchar(255) NOT NULL COMMENT 'Title',
|
|
`detail` text NOT NULL COMMENT 'Detail description',
|
|
`nickname` varchar(128) NOT NULL COMMENT 'User nickname',
|
|
`customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer ID',
|
|
PRIMARY KEY (`detail_id`),
|
|
KEY `REVIEW_DETAIL_REVIEW_ID` (`review_id`),
|
|
CONSTRAINT `REVIEW_DETAIL_REVIEW_ID_REVIEW_REVIEW_ID` FOREIGN KEY (`review_id`) REFERENCES `review` (`review_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB COMMENT='Review detail information';
|
|
|
|
-- review_status: Defines review statuses
|
|
DROP TABLE IF EXISTS `review_status`;
|
|
CREATE TABLE `review_status` (
|
|
`status_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Status ID',
|
|
`status_code` varchar(32) NOT NULL COMMENT 'Status code (e.g. Pending, Approved, Not Approved)',
|
|
PRIMARY KEY (`status_id`)
|
|
) ENGINE=InnoDB COMMENT='Review statuses';
|
|
|
|
|
|
-- ========= Store Configuration =========
|
|
-- store: Defines store views
|
|
DROP TABLE IF EXISTS `store`;
|
|
CREATE TABLE `store` (
|
|
`store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store ID',
|
|
`code` varchar(32) DEFAULT NULL COMMENT 'Code',
|
|
`website_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Website ID',
|
|
`group_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Group ID',
|
|
`name` varchar(255) NOT NULL COMMENT 'Store Name',
|
|
`is_active` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store Activity',
|
|
PRIMARY KEY (`store_id`),
|
|
UNIQUE KEY `STORE_CODE` (`code`)
|
|
) ENGINE=InnoDB COMMENT='Stores';
|
|
|
|
-- store_website: Defines websites
|
|
DROP TABLE IF EXISTS `store_website`;
|
|
CREATE TABLE `store_website` (
|
|
`website_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Website ID',
|
|
`code` varchar(32) DEFAULT NULL COMMENT 'Code',
|
|
`name` varchar(64) DEFAULT NULL COMMENT 'Website Name',
|
|
`default_group_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Default Group ID',
|
|
PRIMARY KEY (`website_id`),
|
|
UNIQUE KEY `STORE_WEBSITE_CODE` (`code`)
|
|
) ENGINE=InnoDB COMMENT='Websites';
|
|
|
|
-- core_config_data: Stores system configuration values
|
|
DROP TABLE IF EXISTS `core_config_data`;
|
|
CREATE TABLE `core_config_data` (
|
|
`config_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Config ID',
|
|
`scope` varchar(8) NOT NULL DEFAULT 'default' COMMENT 'Config Scope (default, websites, stores)',
|
|
`scope_id` int(11) NOT NULL DEFAULT 0 COMMENT 'Config Scope ID',
|
|
`path` varchar(255) NOT NULL DEFAULT 'general' COMMENT 'Config Path (e.g. web/seo/use_rewrites)',
|
|
`value` text DEFAULT NULL COMMENT 'Config Value',
|
|
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Updated At',
|
|
PRIMARY KEY (`config_id`),
|
|
UNIQUE KEY `CORE_CONFIG_DATA_SCOPE_SCOPE_ID_PATH` (`scope`,`scope_id`,`path`)
|
|
) ENGINE=InnoDB COMMENT='Config Data';
|
|
|
|
-- ========= URL Rewrites =========
|
|
-- url_rewrite: Manages URL rewrites for SEO
|
|
DROP TABLE IF EXISTS `url_rewrite`;
|
|
CREATE TABLE `url_rewrite` (
|
|
`url_rewrite_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Rewrite ID',
|
|
`entity_type` varchar(32) NOT NULL COMMENT 'Entity type code (e.g. product, category, cms-page)',
|
|
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
|
|
`request_path` varchar(255) DEFAULT NULL COMMENT 'Request Path (SEO friendly URL)',
|
|
`target_path` varchar(255) DEFAULT NULL COMMENT 'Target Path (internal Magento path)',
|
|
`redirect_type` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Redirect Type (0=No, 301, 302)',
|
|
`store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
|
|
`is_autogenerated` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Is rewrite generated automatically flag',
|
|
PRIMARY KEY (`url_rewrite_id`),
|
|
UNIQUE KEY `URL_REWRITE_REQUEST_PATH_STORE_ID` (`request_path`,`store_id`)
|
|
) ENGINE=InnoDB COMMENT='Url Rewrites';
|
|
|
|
-- Note: Some FOREIGN KEY constraints referencing tables not included in this curated list have been removed or commented out
|
|
-- to ensure this curated schema can be loaded independently if needed for testing,
|
|
-- or that the LLM doesn't get confused by missing references in this specific context.
|
|
-- The original schema provided has the full set of constraints. |