CREATE TABLE `category` 
( `category` varchar(50) NOT NULL
, `category #` int(6) NOT NULL
, `category seq` int(6) NOT NULL
, `category id` int(6) NOT NULL AUTO_INCREMENT
, PRIMARY KEY (`category #`)
, UNIQUE KEY `category` (`category`)
, UNIQUE KEY `category seq` (`category seq`)
, UNIQUE KEY `category id` (`category id`) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
;
CREATE TABLE `type` 
( `type` varchar(75) NOT NULL
, `type #` int(6) NOT NULL
, `type seq` int(9) NOT NULL
, `category #` int(6) NOT NULL
, `type id` int(6) NOT NULL AUTO_INCREMENT
, PRIMARY KEY (`type #`)
, UNIQUE KEY `type` (`type`)
, UNIQUE KEY `type seq` (`type seq`)
, UNIQUE KEY `type id` (`type id`)
, KEY `category #` (`category #`) 
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8
;
CREATE TABLE `year_variety` 
( `year/variety` varchar(50) NOT NULL
, `year/variety #` int(9) NOT NULL
, `type #` int(6) NOT NULL
, `year/variety category seq` int(9) NOT NULL
, `year/variety seq` int(9) NOT NULL
, `category #` int(6) NOT NULL
, `year/variety id` int(9) NOT NULL AUTO_INCREMENT
, PRIMARY KEY (`year/variety #`)
, UNIQUE KEY `year/variety seq` (`year/variety seq`)
, UNIQUE KEY `year/variety id` (`year/variety id`)
, KEY `type #` (`type #`)
, KEY `year/variety category seq` (`year/variety category seq`)
, KEY `year/variety` (`year/variety`)
, KEY `category #` (`category #`) 
) ENGINE=InnoDB AUTO_INCREMENT=6307 DEFAULT CHARSET=utf8
;
CREATE TABLE `inventory` 
( `inventory id` int(15) NOT NULL AUTO_INCREMENT 
, `type #` int(6) NOT NULL
, `category #` int(6) NOT NULL
, `country #` int(9) NOT NULL
, `year variety #` int(9) NOT NULL
, `grade #` int(6) NOT NULL
, `grade modifier #` int(6) NOT NULL
, `description` varchar(250) NOT NULL
, `long description` varchar(250) NOT NULL
, `catalog #` varchar(9) NOT NULL
, `special 1` varchar(12) NOT NULL
, `special 2` varchar(12) NOT NULL
, `special 3` varchar(12) NOT NULL
, `certification #` int(3) NOT NULL
, `location #` int(3) NOT NULL
, `remarks` text NOT NULL
, `status code` varchar(4) NOT NULL
, `retail price` decimal(10,2) NOT NULL
, `user #` int(4) NOT NULL
, `entry date` date NOT NULL
, `quantity` int(4) NOT NULL
, `cost code` varchar(12) NOT NULL
, `purchase date` date NOT NULL
, `purchase price` decimal(10,2) NOT NULL
, `purchase location` varchar(12) NOT NULL
, `sold date` date NOT NULL
, `sold price` decimal(10,2) NOT NULL
, `order #` int(15) NOT NULL
, `barcode #` int(12) NOT NULL
, PRIMARY KEY (`inventory id`)
, KEY `country #` (`country #`)
, KEY `grade #` (`grade #`)
, KEY `grade modifier #` (`grade modifier #`)
, KEY `certification #` (`certification #`)
, KEY `location #` (`location #`)
, KEY `status code` (`status code`)
, KEY `user #` (`user #`)
, KEY `order #` (`order #`)
, KEY `sold price` (`sold price`)
, KEY `sold date` (`sold date`)
, KEY `type #_2` (`type #`,`category #`,`year variety #`)
, KEY `category #` (`category #`)
, KEY `year variety #` (`year variety #`)
, CONSTRAINT `inventory_ibfk_17` 
    FOREIGN KEY (`year variety #`) 
      REFERENCES `year_variety` (`year/variety #`) ON UPDATE CASCADE
, CONSTRAINT `inventory_ibfk_14` 
    FOREIGN KEY (`grade #`) 
      REFERENCES `grade` (`grade #`)
, CONSTRAINT `inventory_ibfk_15` 
    FOREIGN KEY (`category #`) 
      REFERENCES `category` (`category #`) ON UPDATE CASCADE
, CONSTRAINT `inventory_ibfk_16` 
    FOREIGN KEY (`type #`) 
      REFERENCES `type` (`type #`) ON UPDATE CASCADE
, CONSTRAINT `inventory_ibfk_2` 
    FOREIGN KEY (`certification #`) 
      REFERENCES `certification` (`certification #`) ON UPDATE CASCADE
, CONSTRAINT `inventory_ibfk_3` 
    FOREIGN KEY (`grade modifier #`) 
      REFERENCES `grade_modifier` (`grade modifier #`) ON UPDATE CASCADE
, CONSTRAINT `inventory_ibfk_4` 
    FOREIGN KEY (`user #`) 
      REFERENCES `users` (`user #`) ON UPDATE CASCADE
, CONSTRAINT `inventory_ibfk_5` 
    FOREIGN KEY (`country #`) 
      REFERENCES `country` (`country #`) ON UPDATE CASCADE
, CONSTRAINT `inventory_ibfk_6` 
    FOREIGN KEY (`location #`) 
      REFERENCES `location` (`location #`) ON UPDATE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;