SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `mydb`; -- ----------------------------------------------------- -- Table `mydb`.`trans_res_category` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`trans_res_category` ( `trans_res_category_id` VARCHAR(45) NOT NULL , `name` VARCHAR(100) NOT NULL , `description` TEXT NOT NULL DEFAULT ' ' , `lft` INT NOT NULL , `rht` INT NOT NULL , PRIMARY KEY (`trans_res_category_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`vehicle_identifier_type` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`vehicle_identifier_type` ( `type_id` VARCHAR(45) NOT NULL , `name` VARCHAR(60) NULL , `description` TEXT NULL , PRIMARY KEY (`type_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`gis_feature` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`gis_feature` ( `feature_uuid` VARCHAR(60) NOT NULL , `poc_uuid` VARCHAR(60) NULL , `feature_coords` GEOMETRY NULL , `entry_time` TIMESTAMP NOT NULL , PRIMARY KEY (`feature_uuid`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`party` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`party` ( `party_id` VARCHAR(45) NOT NULL , `type` VARCHAR(30) NOT NULL COMMENT 'person or organization\n' , `record_number` VARCHAR(45) NOT NULL COMMENT 'if Person, person record number\nif organization, organization record number' , PRIMARY KEY (`party_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`site_category` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`site_category` ( `site_category_id` VARCHAR(45) NOT NULL , `name` VARCHAR(60) NOT NULL , `description` TEXT NULL , `lft` INT NOT NULL , `rht` INT NOT NULL , PRIMARY KEY (`site_category_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`site` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`site` ( `site_id` VARCHAR(45) NOT NULL , `type` VARCHAR(45) NOT NULL , `name` VARCHAR(60) NOT NULL , `description` TEXT NULL , `address` TEXT NULL , `owner/operator` VARCHAR(45) NULL , `contact` VARCHAR(45) NULL , `comments` TEXT NULL , `attachments` BLOB NULL , `gis_id` VARCHAR(60) NULL , PRIMARY KEY (`site_id`) , INDEX `fk_site_site_category` (`type` ASC) , INDEX `fk_site_gis_feature` (`gis_id` ASC) , INDEX `fk_site_party` (`owner/operator` ASC) , INDEX `fk_site_party1` (`contact` ASC) , CONSTRAINT `fk_site_site_category` FOREIGN KEY (`type` ) REFERENCES `mydb`.`site_category` (`site_category_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_site_gis_feature` FOREIGN KEY (`gis_id` ) REFERENCES `mydb`.`gis_feature` (`feature_uuid` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_site_party` FOREIGN KEY (`owner/operator` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_site_party1` FOREIGN KEY (`contact` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`transport_resources` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`transport_resources` ( `transport_resource_id` VARCHAR(45) NOT NULL , `type` VARCHAR(45) NOT NULL DEFAULT default , `identifier_type` VARCHAR(45) NOT NULL , `identifier` VARCHAR(40) NOT NULL , `owner/operator` VARCHAR(45) NULL , `contact` VARCHAR(45) NULL , `capacity` INT NULL , `range` INT NULL , `base` VARCHAR(45) NULL , `availability` VARCHAR(0) NULL , `comments` TEXT NULL , `attachments` VARCHAR(0) NULL , `gis_id` VARCHAR(60) NULL , PRIMARY KEY (`transport_resource_id`) , INDEX `fk_transport_resources_trans_res_category` (`type` ASC) , INDEX `fk_transport_resources_carrier` (`identifier_type` ASC) , INDEX `fk_transport_resources_gis_feature` (`gis_id` ASC) , INDEX `fk_transport_resources_party` (`contact` ASC) , INDEX `fk_transport_resources_site` (`base` ASC) , INDEX `fk_transport_resources_party1` (`owner/operator` ASC) , CONSTRAINT `fk_transport_resources_trans_res_category` FOREIGN KEY (`type` ) REFERENCES `mydb`.`trans_res_category` (`trans_res_category_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_transport_resources_carrier` FOREIGN KEY (`identifier_type` ) REFERENCES `mydb`.`vehicle_identifier_type` (`type_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_transport_resources_gis_feature` FOREIGN KEY (`gis_id` ) REFERENCES `mydb`.`gis_feature` (`feature_uuid` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_transport_resources_party` FOREIGN KEY (`contact` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_transport_resources_site` FOREIGN KEY (`base` ) REFERENCES `mydb`.`site` (`site_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_transport_resources_party1` FOREIGN KEY (`owner/operator` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_category` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_category` ( `item_cate_id` VARCHAR(45) NOT NULL , `name` VARCHAR(45) NOT NULL , `description` TEXT NULL , `cat_id` INT NOT NULL COMMENT 'used for nested set' , `lft` INT NOT NULL COMMENT 'used for nested set' , `rht` INT NOT NULL COMMENT 'used for nested set' , PRIMARY KEY (`item_cate_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`unit_of_measure` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`unit_of_measure` ( `unit_id` VARCHAR(5) NOT NULL , `abbreviation` VARCHAR(5) NOT NULL , `name` VARCHAR(20) NOT NULL , `measure_type` VARCHAR(20) NULL COMMENT 'Volume, weight, length' , `size` DOUBLE NULL COMMENT 'The multiplicity of the standard unit of measure. 10^-6' , PRIMARY KEY (`unit_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_catelog` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_catelog` ( `item_catelog_id` VARCHAR(45) NOT NULL , `item_category` VARCHAR(45) NOT NULL , `name` VARCHAR(100) NOT NULL , `description` TEXT NOT NULL DEFAULT ' ' , `model` VARCHAR(60) NULL , `brand` VARCHAR(60) NULL , `unit_size` INT NULL DEFAULT 0 , `unit_size_measure` VARCHAR(45) NULL , `unit_weight` INT NULL DEFAULT 0 , `unit_weight_measure` VARCHAR(45) NULL , `unit_price` INT NULL , `unit_price_currency` VARCHAR(10) NULL , `validated` VARCHAR(1) NOT NULL DEFAULT 'n' COMMENT 'y for validated by admin, n for not validated' , `comments` TEXT NULL , `attachments` BLOB NULL , `number_of_items_in_one_parent_item` INT NULL , `hierarchy_path` VARCHAR(100) NOT NULL DEFAULT ' ' , `parent_item` VARCHAR(45) NULL , INDEX `fk_item_type_item_category` (`item_category` ASC) , INDEX `fk_item_type_unit_of_measure` (`unit_size_measure` ASC) , INDEX `fk_item_catelog_unit_of_measure` (`unit_weight_measure` ASC) , INDEX `fk_item_catelog_item_catelog` (`parent_item` ASC) , PRIMARY KEY (`item_catelog_id`) , CONSTRAINT `fk_item_type_item_category` FOREIGN KEY (`item_category` ) REFERENCES `mydb`.`item_category` (`item_cate_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_type_unit_of_measure` FOREIGN KEY (`unit_size_measure` ) REFERENCES `mydb`.`unit_of_measure` (`unit_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_catelog_unit_of_measure` FOREIGN KEY (`unit_weight_measure` ) REFERENCES `mydb`.`unit_of_measure` (`unit_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_catelog_item_catelog` FOREIGN KEY (`parent_item` ) REFERENCES `mydb`.`item_catelog` (`item_catelog_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_state_child` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_state_child` ( `state` VARCHAR(10) NOT NULL COMMENT 'split / composite / aggregate / dekitting / kitting' , `description` TEXT NULL , PRIMARY KEY (`state`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_state_parent` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_state_parent` ( `state` VARCHAR(10) NOT NULL COMMENT 'split / composite / aggregate / dekitting / kitting' , `description` TEXT NULL , PRIMARY KEY (`state`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_state_current` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_state_current` ( `state` VARCHAR(10) NOT NULL COMMENT 'split item / item obtained from a composite item / lost item / damadged / expired\n' , `description` TEXT NULL , PRIMARY KEY (`state`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item` ( `item_id` VARCHAR(45) NOT NULL , `tracking_no` VARCHAR(60) NULL COMMENT 'Air Way Bill (AWB)/Bill of Lading (BOL)/other tracking number for the shipment' , `financial_code` VARCHAR(40) NULL , `item_catelog_id` VARCHAR(45) NOT NULL , `date` TIMESTAMP NOT NULL , `sender` VARCHAR(45) NULL , `designated_for` VARCHAR(45) NULL COMMENT 'Specific project, population, village or other earmarking of the donation' , `designated_location` TEXT NULL , `quantity` INT NOT NULL DEFAULT 1 , `shortage` INT NULL DEFAULT 0 , `net_quantity_received` INT NOT NULL COMMENT 'quantity - shortage' , `unit_price` VARCHAR(45) NULL , `unit_price_currency` VARCHAR(10) NULL , `comments` TEXT NULL , `state_current` VARCHAR(10) NULL , `state_parent` VARCHAR(10) NULL , `state_child` VARCHAR(10) NULL , `hierarchy_path` VARCHAR(100) NULL , INDEX `fk_item_party` (`sender` ASC) , INDEX `fk_item_item_type` (`item_catelog_id` ASC) , INDEX `fk_item_item_state_child` (`state_child` ASC) , INDEX `fk_item_item_state_parent` (`state_parent` ASC) , INDEX `fk_item_item_state_current` (`state_current` ASC) , PRIMARY KEY (`item_id`) , CONSTRAINT `fk_item_party` FOREIGN KEY (`sender` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_item_type` FOREIGN KEY (`item_catelog_id` ) REFERENCES `mydb`.`item_catelog` (`item_catelog_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_item_state_child` FOREIGN KEY (`state_child` ) REFERENCES `mydb`.`item_state_child` (`state` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_item_state_parent` FOREIGN KEY (`state_parent` ) REFERENCES `mydb`.`item_state_parent` (`state` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_item_state_current` FOREIGN KEY (`state_current` ) REFERENCES `mydb`.`item_state_current` (`state` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`person` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`person` ( `p_uuid` VARCHAR(60) NOT NULL , PRIMARY KEY (`p_uuid`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`organization` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`organization` ( `o_uuid` VARCHAR(60) NOT NULL , PRIMARY KEY (`o_uuid`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_site_status` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_site_status` ( `item_site_status_id` VARCHAR(5) NOT NULL , `status` VARCHAR(60) NOT NULL COMMENT 'InTake, Transit, Temporary Store, ' , PRIMARY KEY (`item_site_status_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`storage_location` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`storage_location` ( `storage_location_id` VARCHAR(45) NOT NULL , `site` VARCHAR(45) NOT NULL , `location_id` VARCHAR(20) NOT NULL , `dimension` VARCHAR(20) NULL , `area` INT NULL COMMENT 'in square meters' , `height` INT NULL COMMENT 'in meters\n' , PRIMARY KEY (`storage_location_id`) , INDEX `fk_storage_location_site` (`site` ASC) , CONSTRAINT `fk_storage_location_site` FOREIGN KEY (`site` ) REFERENCES `mydb`.`site` (`site_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_location` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_location` ( `item_location_id` VARCHAR(45) NOT NULL , `item_id` VARCHAR(45) NOT NULL , `storage_location_id` VARCHAR(45) NOT NULL , `date_in` DATETIME NOT NULL , `date_out` DATETIME NULL , `sent_by` VARCHAR(45) NULL , `received_by` VARCHAR(45) NULL , `comments` TEXT NULL , `attachments` BLOB NULL , `status` VARCHAR(5) NOT NULL , PRIMARY KEY (`date_in`, `storage_location_id`, `item_id`) , INDEX `fk_item_site_item` (`item_id` ASC) , INDEX `fk_item_site_item_site_status` (`status` ASC) , INDEX `fk_item_site_storage_location` (`storage_location_id` ASC) , INDEX `fk_item_location_party` (`sent_by` ASC) , INDEX `fk_item_location_party1` (`received_by` ASC) , CONSTRAINT `fk_item_site_item` FOREIGN KEY (`item_id` ) REFERENCES `mydb`.`item` (`item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_site_item_site_status` FOREIGN KEY (`status` ) REFERENCES `mydb`.`item_site_status` (`item_site_status_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_site_storage_location` FOREIGN KEY (`storage_location_id` ) REFERENCES `mydb`.`storage_location` (`storage_location_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_location_party` FOREIGN KEY (`sent_by` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_location_party1` FOREIGN KEY (`received_by` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`shipment` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`shipment` ( `shipment_id` VARCHAR(45) NOT NULL , `tracking_no` VARCHAR(60) NULL , `sender` VARCHAR(45) NULL , `recepient` VARCHAR(45) NULL , `sent_by` VARCHAR(45) NULL , `received_by` VARCHAR(45) NULL , `send_processed_by` VARCHAR(45) NULL , `receive_processed_by` VARCHAR(45) NULL , `destination` VARCHAR(60) NULL COMMENT 'if the item is being shipped such that it should no longer be tracked by the system\n' , `destination_site` VARCHAR(45) NULL , `origin` VARCHAR(60) NULL , `origin_site` VARCHAR(45) NULL , `vehicle` VARCHAR(45) NULL , `date` TIMESTAMP NULL , `cost` INT NULL , `cost_currency` VARCHAR(10) NULL , `comments` TEXT NULL , `attachments` BLOB NULL , INDEX `fk_shipment_site` (`destination_site` ASC) , INDEX `fk_shipment_site1` (`origin_site` ASC) , INDEX `fk_shipment_transport_resources` (`vehicle` ASC) , PRIMARY KEY (`shipment_id`) , CONSTRAINT `fk_shipment_site` FOREIGN KEY (`destination_site` ) REFERENCES `mydb`.`site` (`site_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_shipment_site1` FOREIGN KEY (`origin_site` ) REFERENCES `mydb`.`site` (`site_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_shipment_transport_resources` FOREIGN KEY (`vehicle` ) REFERENCES `mydb`.`transport_resources` (`transport_resource_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_shipment_status` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_shipment_status` ( `item_shipment_status_id` VARCHAR(5) NOT NULL , `status` VARCHAR(60) NOT NULL COMMENT 'InTake, Transit, Temporary Store, to be shipped, being shipped , shipment done\n' , PRIMARY KEY (`item_shipment_status_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_shipment` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_shipment` ( `item_shipment_id` VARCHAR(60) NOT NULL , `item_id` VARCHAR(45) NOT NULL , `shipment_id` VARCHAR(45) NOT NULL , `date_entered` TIMESTAMP NULL , `shipment_state` VARCHAR(5) NOT NULL , INDEX `fk_item_shipment_item` (`item_id` ASC) , INDEX `fk_item_shipment_shipment` (`shipment_id` ASC) , PRIMARY KEY (`item_id`, `shipment_id`) , INDEX `fk_item_shipment_item_shipment_status` (`shipment_state` ASC) , CONSTRAINT `fk_item_shipment_item` FOREIGN KEY (`item_id` ) REFERENCES `mydb`.`item` (`item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_shipment_shipment` FOREIGN KEY (`shipment_id` ) REFERENCES `mydb`.`shipment` (`shipment_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_shipment_item_shipment_status` FOREIGN KEY (`shipment_state` ) REFERENCES `mydb`.`item_shipment_status` (`item_shipment_status_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`disbursement` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`disbursement` ( `disbursement_id` VARCHAR(45) NOT NULL , `item_id` VARCHAR(45) NOT NULL , `recipient` VARCHAR(45) NOT NULL , `date` TIMESTAMP NOT NULL , `comments` TEXT NULL , `attachments` BLOB NULL , PRIMARY KEY (`disbursement_id`) , INDEX `fk_disbursement_party` (`recipient` ASC) , INDEX `fk_disbursement_item` (`item_id` ASC) , CONSTRAINT `fk_disbursement_party` FOREIGN KEY (`recipient` ) REFERENCES `mydb`.`party` (`party_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_disbursement_item` FOREIGN KEY (`item_id` ) REFERENCES `mydb`.`item` (`item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`person_site` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`person_site` ( `person_id` VARCHAR(60) NOT NULL , `site_id` VARCHAR(45) NOT NULL , `date_from` TIMESTAMP NULL , `date_till` TIMESTAMP NULL , PRIMARY KEY (`person_id`) , INDEX `fk_person_site_site` (`site_id` ASC) , CONSTRAINT `fk_person_site_site` FOREIGN KEY (`site_id` ) REFERENCES `mydb`.`site` (`site_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`field_data_type` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`field_data_type` ( `field_data_type_id` VARCHAR(5) NOT NULL , PRIMARY KEY (`field_data_type_id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_category_attribute_meta` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_category_attribute_meta` ( `attribute_name` VARCHAR(45) NOT NULL , `description` TEXT NULL , `attribute_data_type` VARCHAR(5) NOT NULL , INDEX `fk_item_category_fields_meta_field_data_type` (`attribute_data_type` ASC) , PRIMARY KEY (`attribute_name`) , CONSTRAINT `fk_item_category_fields_meta_field_data_type` FOREIGN KEY (`attribute_data_type` ) REFERENCES `mydb`.`field_data_type` (`field_data_type_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_catelog_attribute` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_catelog_attribute` ( `attribute_name` VARCHAR(45) NOT NULL , `item_catelog_id` VARCHAR(45) NOT NULL , `value` VARCHAR(100) NOT NULL , PRIMARY KEY (`item_catelog_id`, `attribute_name`) , INDEX `fk_item_catelog_field_item_catelog` (`item_catelog_id` ASC) , INDEX `fk_item_catelog_attribute_item_category_attribute_meta` (`attribute_name` ASC) , CONSTRAINT `fk_item_catelog_field_item_catelog` FOREIGN KEY (`item_catelog_id` ) REFERENCES `mydb`.`item_catelog` (`item_catelog_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_catelog_attribute_item_category_attribute_meta` FOREIGN KEY (`attribute_name` ) REFERENCES `mydb`.`item_category_attribute_meta` (`attribute_name` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_category_attribute` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_category_attribute` ( `item_category` VARCHAR(45) NOT NULL , `attribute_name` VARCHAR(45) NOT NULL , PRIMARY KEY (`item_category`, `attribute_name`) , INDEX `fk_item_category_attribute_item_category` (`item_category` ASC) , INDEX `fk_item_category_attribute_item_category_attribute_meta` (`attribute_name` ASC) , CONSTRAINT `fk_item_category_attribute_item_category` FOREIGN KEY (`item_category` ) REFERENCES `mydb`.`item_category` (`item_cate_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_category_attribute_item_category_attribute_meta` FOREIGN KEY (`attribute_name` ) REFERENCES `mydb`.`item_category_attribute_meta` (`attribute_name` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_attribute` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_attribute` ( `attribute_name` VARCHAR(45) NOT NULL , `item_id` VARCHAR(45) NOT NULL , `value` VARCHAR(100) NOT NULL , PRIMARY KEY (`item_id`, `attribute_name`) , INDEX `fk_item_attribute_item` (`item_id` ASC) , INDEX `fk_item_attribute_item_category_attribute_meta` (`attribute_name` ASC) , CONSTRAINT `fk_item_attribute_item` FOREIGN KEY (`item_id` ) REFERENCES `mydb`.`item` (`item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_attribute_item_category_attribute_meta` FOREIGN KEY (`attribute_name` ) REFERENCES `mydb`.`item_category_attribute_meta` (`attribute_name` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`si_item_catelog` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`si_item_catelog` ( `item_catelog_id` VARCHAR(45) NOT NULL , `name` VARCHAR(100) NOT NULL , `description` TEXT NOT NULL DEFAULT ' ' , PRIMARY KEY (`item_catelog_id`) , INDEX `fk_si_item_catelog_item_catelog` (`item_catelog_id` ASC) , CONSTRAINT `fk_si_item_catelog_item_catelog` FOREIGN KEY (`item_catelog_id` ) REFERENCES `mydb`.`item_catelog` (`item_catelog_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`fr_item_catelog` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`fr_item_catelog` ( `item_catelog_id` VARCHAR(45) NOT NULL , `name` VARCHAR(100) NOT NULL , `description` TEXT NOT NULL DEFAULT ' ' , INDEX `fk_fr_item_catelog_item_catelog` (`item_catelog_id` ASC) , PRIMARY KEY (`item_catelog_id`) , CONSTRAINT `fk_fr_item_catelog_item_catelog` FOREIGN KEY (`item_catelog_id` ) REFERENCES `mydb`.`item_catelog` (`item_catelog_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_group(kitting)` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_group(kitting)` ( `parent_item` VARCHAR(45) NOT NULL , `grouped_item` VARCHAR(45) NOT NULL , `grouped_date` TIMESTAMP NULL , PRIMARY KEY (`parent_item`, `grouped_item`) , INDEX `fk_item_group(kitting)_item` (`parent_item` ASC) , INDEX `fk_item_group(kitting)_item1` (`grouped_item` ASC) , CONSTRAINT `fk_item_group(kitting)_item` FOREIGN KEY (`parent_item` ) REFERENCES `mydb`.`item` (`item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_group(kitting)_item1` FOREIGN KEY (`grouped_item` ) REFERENCES `mydb`.`item` (`item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`item_dispose` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`item_dispose` ( `item_dispose_id` VARCHAR(45) NOT NULL , `item` VARCHAR(45) NOT NULL , `location` VARCHAR(45) NULL COMMENT 'location id of where the loss happened' , `shipment` VARCHAR(45) NULL COMMENT 'Shipment in which the item got lost' , `vehicle` VARCHAR(45) NULL , `date` TIMESTAMP NOT NULL , `reported_by` VARCHAR(45) NULL , `dispose_type` VARCHAR(45) NULL , INDEX `fk_item_dispose_item` (`item` ASC) , INDEX `fk_item_dispose_storage_location` (`location` ASC) , INDEX `fk_item_dispose_shipment` (`shipment` ASC) , INDEX `fk_item_dispose_transport_resources` (`vehicle` ASC) , PRIMARY KEY (`item_dispose_id`) , CONSTRAINT `fk_item_dispose_item` FOREIGN KEY (`item` ) REFERENCES `mydb`.`item` (`item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_dispose_storage_location` FOREIGN KEY (`location` ) REFERENCES `mydb`.`storage_location` (`storage_location_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_dispose_shipment` FOREIGN KEY (`shipment` ) REFERENCES `mydb`.`shipment` (`shipment_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_item_dispose_transport_resources` FOREIGN KEY (`vehicle` ) REFERENCES `mydb`.`transport_resources` (`transport_resource_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;