I'm trying to insert data from a flat file into multiple tables. The data has a one to many relationship and, unfortunately, one column has several values squeezed into it that need to be extracted. I would like to maintain referential integrity with the data. Below is a sample of the data.
doc_number pin_number
2005123413214 20-16-105-034- & 20-16-104-035-
0146546487831 15-14-230-155-0000
2465454878646 15-14-569-233-0000
2456545478979 14-24-553-123- & 14-24-553-123, 23-17-105-026-
Obviously, I need to use regex and then something like explode() to format the file and then insert the data into my tables. Fortunately, the regrex part insn't my problem yet. How on earth can I create an array to load this stuff into this database? fgetcsv()? Is this a good database design for this task? Here's what the dataset looks like:
$query1 = "CREATE TABLE mtg_doc (
doc_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
doc_number INT NOT NULL
) ENGINE = INNODB";
$query2 = "CREATE TABLE mtg_pin (
pin_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
pin_number INT NOT NULL
) ENGINE = INNODB";
$query3 = "CREATE TABLE mtg_pin_identifier (
doc_id INT NOT NULL,
pin_id INT NOT NULL,
PRIMARY KEY(doc_id, pin_id),
INDEX (doc_id),
FOREIGN KEY (doc_id) REFERENCES mtg_doc (doc_id) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX (pin_id),
FOREIGN KEY (pin_id) REFERENCES mtg_pin (pin_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB";
If I use something like $doc[$i] --> $pin[$i][$n], will the auto increment indexes hold up? Is there a better way than auto increment? Eventually, I need to make another array dependent on the pin_number. Can anybody send me a link to some good tutorials or book references?
