39 lines
1.2 KiB
MySQL
39 lines
1.2 KiB
MySQL
|
CREATE TABLE `item` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`name` LONGTEXT DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
-- Reselling item price, not including production item price, service item price, and subscription item price
|
||
|
|
CREATE TABLE `item_price` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`item` INT(11) NOT NULL,
|
||
|
|
`type` VARCHAR(4) NOT NULL, -- buy, sell
|
||
|
|
`currency` VARCHAR(3) NOT NULL,
|
||
|
|
`value` INT(11) NOT NULL,
|
||
|
|
`periods` DATETIME NOT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `item` (`item`),
|
||
|
|
CONSTRAINT `item_price_fk_item`
|
||
|
|
FOREIGN KEY (`item`)
|
||
|
|
REFERENCES `item`(`id`)
|
||
|
|
ON UPDATE CASCADE
|
||
|
|
ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
-- Not including assets inventory, get from buying/production, and shipping/expedition
|
||
|
|
CREATE TABLE `inventory` (
|
||
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`item` INT(11) NOT NULL,
|
||
|
|
`flow` VARCHAR(20) NOT NULL, -- in, out, buy, sell, buy return, sell return
|
||
|
|
`when` DATETIME NOT NULL,
|
||
|
|
`qty` INT(11) NOT NULL,
|
||
|
|
`note` LONGTEXT DEFAULT NULL,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
KEY `item` (`item`),
|
||
|
|
CONSTRAINT `inventory_fk_item`
|
||
|
|
FOREIGN KEY (`item`)
|
||
|
|
REFERENCES `item`(`id`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
-- get from: buying/production
|