CREATE TABLE Manufacturer ( manufacturer_ID INT AUTO_INCREMENT NOT NULL, name VARCHAR(64) NOT NULL, CONSTRAINT manufacturer_PK PRIMARY KEY(manufacturer_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB; CREATE TABLE PhoneType ( phoneType_ID INT AUTO_INCREMENT NOT NULL, name VARCHAR(128) UNIQUE NOT NULL, color VARCHAR(32), CONSTRAINT phoneType_PK PRIMARY KEY(phoneType_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB; CREATE TABLE InvoicingPartner ( invoicingPartner_ID INT AUTO_INCREMENT NOT NULL, name VARCHAR(64), CONSTRAINT invoicingPartner_PK PRIMARY KEY(invoicingPartner_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB; CREATE TABLE Invoice ( invoice_ID INT AUTO_INCREMENT NOT NULL, invoiceNumber INT NOT NULL UNIQUE, invoicingPartner_ID INT NOT NULL, CONSTRAINT Invoice_PK PRIMARY KEY(invoice_ID), CONSTRAINT Invoice_InvoicingPartner_FK FOREIGN KEY(invoicingPartner_ID) REFERENCES InvoicingPartner(invoicingPartner_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB; CREATE TABLE Customer ( customer_ID INT AUTO_INCREMENT NOT NULL, name VARCHAR(64), CONSTRAINT Customer_PK PRIMARY KEY(customer_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB; CREATE TABLE Supplier ( supplier_ID INT AUTO_INCREMENT NOT NULL, name VARCHAR(64), CONSTRAINT Supplier_PK PRIMARY KEY(supplier_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB; CREATE TABLE Location ( location_ID INT AUTO_INCREMENT NOT NULL, name VARCHAR(64), CONSTRAINT Location_PK PRIMARY KEY(location_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB; CREATE TABLE Phone ( phone_ID INT AUTO_INCREMENT NOT NULL, imeiNumber CHAR(14) UNIQUE NOT NULL, phoneType_ID INT NOT NULL, manufacturer_ID INT NOT NULL, invoice_ID INT NOT NULL, customer_ID INT NOT NULL, supplier_ID INT, location_ID INT, date_of_delivery DATE NOT NULL, date_of_credit DATE, date_of_note DATE, note VARCHAR(256), salesPrice INT NOT NULL, creditAmount INT, CONSTRAINT phone_PK PRIMARY KEY(phone_ID), CONSTRAINT Phone_to_PhoneType_FK FOREIGN KEY(phoneType_ID) REFERENCES PhoneType(phoneType_ID), CONSTRAINT Phone_to_Manufacturer_FK FOREIGN KEY(manufacturer_ID) REFERENCES Manufacturer(manufacturer_ID), CONSTRAINT Phone_to_Invoice_FK FOREIGN KEY(invoice_ID) REFERENCES Invoice(invoice_ID), CONSTRAINT Phone_to_Customer_FK FOREIGN KEY(customer_ID) REFERENCES Customer(customer_ID), CONSTRAINT Phone_to_Supplier_FK FOREIGN KEY(supplier_ID) REFERENCES Supplier(supplier_ID), CONSTRAINT Phone_to_Location_FK FOREIGN KEY(location_ID) REFERENCES Location(location_ID) )DEFAULT CHARACTER SET utf8 COLLATE utf8_icelandic_ci ENGINE=InnoDB;
My boss wants to be able to read roughly 10.000 records (rows) with about 14 columns each, from an Excel file, and place it into the database. This is a one time deal and will probably take a lot of time, and that's that. Given that just reading through the Excel file and sorting out the duplicates took my program about 15 minutes, I'm afraid putting it into the database will increase that time significantly.
The route I was thinking of taking was:
1. Checking to see if each field exists in the database. Phone references most of the tables, so I need to see if its fields exist there (and then get the IDs) before I insert.
2. Insert it.
This needs to happen ten thousand times, and after that, anywhere from 100-300 times at once. Is there a better way to go about this? I need to do this for the following fields:
"phoneType_ID INT NOT NULL,
manufacturer_ID INT NOT NULL,
invoice_ID INT NOT NULL,
customer_ID INT NOT NULL,
supplier_ID INT,
location_ID INT,"
That's.. 6 queries - is there something I'm missing here?
An example would be, if the Excel field for the phone type is "Lumia 710", I'd need to check the PhoneType table for the ID of Lumia 710, so that I could insert the ID into the Phone table. I'd need to do this for the other 5 fields as well, and then make sure to display an error if it doesn't exist in there at all, e.g. if the excel document had Lumia 710 with two spaces. Then finally I'd have to insert it. I wouldn't see a problem with this if I was only inserting a few records at a time, but he needs to insert those first 10.000, and then 200-300 more at once in the future. And well, waiting is not an option.