Hi everyone,
I¹m trying to figure out the best sequence to enter data into my database
(the SQL code for the structure is included below). If I have a number of
tab delimited .txt files containing the data for the different tables, I
thought as a first step I could use the following code to populate the
garments table:
LOAD DATA INFILE 'garments.txt'
INTO TABLE garments
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r'
I also thought I could use the same code to insert the data into the
colours, sizes and categories tables. My problem starts when I have to try
to enter the correct garment_id from the garments table into the correct
foreign key fields in the garments_to_colour, garments_to_sizes and
garments_to_categories look up tables. Do I need to do this manually or can
I use the last_insert_id() function? But not sure if this would work since
I¹m not populating the garments table one row at a time I¹m inserting all
info in one go with LOAD DATA INFILE, as mentioned above.
Just wondered if someone could help solve this problem?
CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
`garment_type` varchar(30) NOT NULL,
`title` varchar(60) NOT NULL,
`code` varchar(20) NOT NULL,
`description` varchar(400) NOT NULL,
`extra_info` varchar(50) default NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
PRIMARY KEY (`garment_id`),
UNIQUE KEY `supplier` (`supplier`,`garment_type`, `description`,
`title`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
