45 lines
2.3 KiB
MySQL
45 lines
2.3 KiB
MySQL
|
-- Three-layer inventory model: Product -> Pan(盘) -> Bolt(匹)
|
||
|
|
|
||
|
|
-- Pan table: each row = one physical pan/roll, belongs to a product
|
||
|
|
CREATE TABLE IF NOT EXISTS `inv_product_pan` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`pan_id` VARCHAR(32) NOT NULL,
|
||
|
|
`product_id` VARCHAR(32) NOT NULL,
|
||
|
|
`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'pan label: A, B, C...',
|
||
|
|
`position` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'physical location: Shanghai, Nanjing...',
|
||
|
|
`sort_order` INT NOT NULL DEFAULT 0,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `uk_pan_id` (`pan_id`),
|
||
|
|
KEY `idx_product_id` (`product_id`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`),
|
||
|
|
KEY `idx_position` (`position`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Bolt table: each row = one physical bolt/piece, belongs to a pan
|
||
|
|
CREATE TABLE IF NOT EXISTS `inv_product_bolt` (
|
||
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT,
|
||
|
|
`bolt_id` VARCHAR(32) NOT NULL,
|
||
|
|
`pan_id` VARCHAR(32) NOT NULL COMMENT 'FK to inv_product_pan',
|
||
|
|
`length_m` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'length in meters',
|
||
|
|
`sort_order` INT NOT NULL DEFAULT 0,
|
||
|
|
`tenant_id` VARCHAR(32) NOT NULL DEFAULT '',
|
||
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `uk_bolt_id` (`bolt_id`),
|
||
|
|
KEY `idx_pan_id` (`pan_id`),
|
||
|
|
KEY `idx_tenant_id` (`tenant_id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Add composite index for product summary aggregation (panel 1)
|
||
|
|
-- Use PREPARE/EXECUTE to skip if index already exists (MySQL has no CREATE INDEX IF NOT EXISTS).
|
||
|
|
SET @ix = (SELECT COUNT(*) FROM information_schema.STATISTICS
|
||
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_product' AND INDEX_NAME = 'idx_product_name_tenant');
|
||
|
|
SET @s = IF(@ix = 0,
|
||
|
|
'ALTER TABLE `inv_product` ADD KEY `idx_product_name_tenant` (`product_name`, `tenant_id`)',
|
||
|
|
'SELECT 1');
|
||
|
|
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|