Below are hopefully the fixed tables. I have not entered the foreign keys from the type, category, and year_variety tables into the inventory table yet. I want to make sure that I am headed in the correct direction. I am going to add the table fields labeled (*)_seq to the inventory table from the above mentioned tables and add them as foriegn keys.
Thank You
Code:
CREATE TABLE
`inventory` ( `inventory_id` int(15) NOT NULL AUTO_INCREMENT,
`country_#` 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`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8
Code:
CREATE TABLE
`type` ( `type` varchar(75) NOT NULL,
`type_seq` int(9) NOT NULL, PRIMARY KEY (`type_seq`), UNIQUE KEY `type` (`type`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8
Code:
CREATE TABLE
`category` ( `category` varchar(50) NOT NULL, `category_seq` int(6) NOT NULL, PRIMARY KEY (`category_seq`), UNIQUE KEY `category` (`category`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8
Code:
CREATE TABLE `year_variety` ( `year/variety` varchar(50) NOT NULL,
`year/variety_seq` int(9) NOT NULL, PRIMARY KEY (`year/variety_seq`), KEY `year/variety` (`year/variety`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8