PHPize Online / SQLize Online  /  SQLtest Online

CREATE TABLE factories ( id int(11) NOT NULL, name varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO factories (id, `name`) VALUES (1, 'TJ Nest 1'), (2, 'TJ Nest 2'); CREATE TABLE `options` ( id int(11) NOT NULL, name varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `options` (id, `name`) VALUES (17, 'Fabric'), (19, 'Wood Stain'); CREATE TABLE option_values ( id int(11) NOT NULL, optoin_id int(11) NOT NULL, name varchar(255) NOT NULL, sku varchar(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO option_values (id, optoin_id, `name`, sku) VALUES (101, 17, 'Taylor Felt Gray', '-CF1001'), (102, 17, 'Essence Ash', '-CF1002'), (103, 17, 'Key Largo Ash', '-CF1003'), (201, 19, 'Mocha', '-WS01'); CREATE TABLE order_cart_items ( id int(11) NOT NULL, session_token varchar(255) DEFAULT NULL, order_id int(11) DEFAULT NULL, product_id int(11) NOT NULL COMMENT 'redundant due variant_id', variant_id int(11) NOT NULL, cart_configuration text, price_base decimal(12,2) UNSIGNED NOT NULL DEFAULT '0.00' COMMENT 'ProductPrice+VariantPrice', price_discounts decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Clearance+CrossSale+Discounts', price_tax decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'BasePrice+Discounts', price_total decimal(12,2) UNSIGNED NOT NULL DEFAULT '0.00' COMMENT 'BasePrice+Discounts+Tax', cart_status enum('DRAFT','ON ORDER','CANCELED','RETURNED','REPLACED') NOT NULL DEFAULT 'DRAFT', date_added datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, date_modified datetime DEFAULT NULL, date_removed datetime DEFAULT NULL, user_added varchar(255) DEFAULT NULL, user_modified varchar(255) DEFAULT NULL, user_removed varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE products ( id int(11) NOT NULL, name varchar(255) NOT NULL, price decimal(12,2) NOT NULL DEFAULT '0.00', sku varchar(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO products (id, `name`, price, sku) VALUES (623, 'Hughes Sofa', '900.00', 'JB10010'), (624, 'Hughes Chair', '600.00', 'JB10020'), (1024, 'Xavier Desk', '400.00', 'JB90010'); CREATE TABLE product_variants ( id int(11) NOT NULL, product_id int(11) NOT NULL, name varchar(255) DEFAULT NULL, config text, config_price decimal(12,2) NOT NULL DEFAULT '0.00', config_sku varchar(255) DEFAULT NULL, sku varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO product_variants (id, product_id, `name`, config, config_price, config_sku, sku) VALUES (1, 623, 'Taylor Felt Gray, Mocha', '[\r\n{\'optoin_id\':17, \'value_id\':101, \'option_name\':\'Fabric\', \'value_name\':\'Taylor Felt Gray\', \'value_charge\':30},\r\n{\'optoin_id\':19, \'value_id\':201, \'option_name\':\'Wood Stain\', \'value_name\':\'Mocha\', \'value_charge\':0}\r\n]', '30.00', '-CF1001-WS01', 'JB10010-CF1001-WS01'), (2, 623, 'Essence Ash, Mocha', '[\r\n{\'optoin_id\':17, \'value_id\':102, \'option_name\':\'Fabric\', \'value_name\':\'Essence Ash\', \'value_charge\':0},\r\n{\'optoin_id\':19, \'value_id\':201, \'option_name\':\'Wood Stain\', \'value_name\':\'Mocha\', \'value_charge\':0}\r\n]', '0.00', '-CF1002-WS01', 'JB10010-CF1002-WS01'), (3, 623, 'Key Largo Ash, Mocha', '[\r\n{\'optoin_id\':17, \'value_id\':103, \'option_name\':\'Fabric\', \'value_name\':\'Key Largo Ash\', \'value_charge\':0},\r\n{\'optoin_id\':19, \'value_id\':201, \'option_name\':\'Wood Stain\', \'value_name\':\'Mocha\', \'value_charge\':0}\r\n]', '0.00', '-CF1003-WS01', 'JB10010-CF1003-WS01'), (4, 624, 'Taylor Felt Gray, Mocha', '[\r\n{\'optoin_id\':17, \'value_id\':101, \'option_name\':\'Fabric\', \'value_name\':\'Taylor Felt Gray\', \'value_charge\':30},\r\n{\'optoin_id\':19, \'value_id\':201, \'option_name\':\'Wood Stain\', \'value_name\':\'Mocha\', \'value_charge\':0}\r\n]', '30.00', '-CF1001-WS01', 'JB10020-CF1001-WS01'), (5, 624, 'Essence Ash, Mocha', '[\r\n{\'optoin_id\':17, \'value_id\':102, \'option_name\':\'Fabric\', \'value_name\':\'Essence Ash\', \'value_charge\':0},\r\n{\'optoin_id\':19, \'value_id\':201, \'option_name\':\'Wood Stain\', \'value_name\':\'Mocha\', \'value_charge\':0}\r\n]', '0.00', '-CF1002-WS01', 'JB10020-CF1002-WS01'), (6, 624, 'Key Largo Ash, Mocha', '[\r\n{\'optoin_id\':17, \'value_id\':103, \'option_name\':\'Fabric\', \'value_name\':\'Key Largo Ash\', \'value_charge\':0},\r\n{\'optoin_id\':19, \'value_id\':201, \'option_name\':\'Wood Stain\', \'value_name\':\'Mocha\', \'value_charge\':0}\r\n]', '0.00', '-CF1003-WS01', 'JB10020-CF1003-WS01'), (7, 1024, NULL, NULL, '0.00', NULL, 'JB90010'); CREATE TABLE protection_plans ( id int(11) NOT NULL, name varchar(255) NOT NULL, percent decimal(5,3) NOT NULL DEFAULT '0.000' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO protection_plans (id, `name`, percent) VALUES (1, 'Base Protection Plan', '0.105'), (2, 'Pet Protection Plan', '0.125'); CREATE TABLE vendors ( id int(11) NOT NULL, name varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE warehouses ( id int(11) NOT NULL, name varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO warehouses (id, `name`) VALUES (1, 'San Diego JB1'), (2, 'San Diego JB2'), (3, 'Hudson JB3'); ALTER TABLE factories ADD PRIMARY KEY (id); ALTER TABLE `options` ADD PRIMARY KEY (id); ALTER TABLE option_values ADD PRIMARY KEY (id), ADD UNIQUE KEY option_value_sku (sku), ADD KEY option_value_option (optoin_id); ALTER TABLE order_cart_items ADD PRIMARY KEY (id), ADD KEY cart_token (session_token), ADD KEY cart_variant_id (variant_id), ADD KEY cart_product (product_id), ADD KEY cart_item_status (cart_status), ADD KEY cart_order (order_id); ALTER TABLE products ADD PRIMARY KEY (id), ADD UNIQUE KEY product_sku (sku); ALTER TABLE product_variants ADD PRIMARY KEY (id), ADD UNIQUE KEY variant_sku (sku) USING BTREE, ADD KEY variant_product_id (product_id); ALTER TABLE protection_plans ADD PRIMARY KEY (id); ALTER TABLE vendors ADD PRIMARY KEY (id); ALTER TABLE warehouses ADD PRIMARY KEY (id); ALTER TABLE factories MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; ALTER TABLE `options` MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20; ALTER TABLE option_values MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=202; ALTER TABLE order_cart_items MODIFY id int(11) NOT NULL AUTO_INCREMENT; ALTER TABLE products MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1025; ALTER TABLE product_variants MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; ALTER TABLE protection_plans MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; ALTER TABLE vendors MODIFY id int(11) NOT NULL AUTO_INCREMENT; ALTER TABLE warehouses MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; ALTER TABLE option_values ADD CONSTRAINT option_value_option_rel FOREIGN KEY (optoin_id) REFERENCES `options` (id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE order_cart_items ADD CONSTRAINT cart_product_rel FOREIGN KEY (product_id) REFERENCES products (id), ADD CONSTRAINT cart_variant_rel FOREIGN KEY (variant_id) REFERENCES product_variants (id); ALTER TABLE product_variants ADD CONSTRAINT variant_product_rel FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ON UPDATE CASCADE;