Need help with relational database

[El Heso]

Hi folks!

I need help how to code or links to tutorials and examples.

What i want to do:

 

I have a temporary table with the following columns:

 

  `company_id` int(11) NOT NULL AUTO_INCREMENT,

  `company_name` varchar(30) NOT NULL,

  `company_address` varchar(50) NOT NULL,

  `company_zipcode` int(6) NOT NULL,

  `company_city` varchar(30) NOT NULL,

  `company_telephone` varchar(20) NOT NULL,

  `company_addressid` int(9) NOT NULL,

  `person_firstname` varchar(20) NOT NULL,

  `person_lastname` varchar(20) NOT NULL,

  `person_email` varchar(30) NOT NULL,

  `person_emailstatus` varchar(12) NOT NULL,

  `person_position` varchar(50) NOT NULL,

  `answer_1` varchar(50) NOT NULL,

  `answer_2` varchar(50) NOT NULL,

  `answer_3` varchar(50) NOT NULL

 

 

Then i have 3 tables:

company

  `company_id` int(11) NOT NULL AUTO_INCREMENT,

  `company_name` varchar(30) NOT NULL,

  `company_address` varchar(50) NOT NULL,

  `company_zipcode` int(6) NOT NULL,

  `company_city` varchar(30) NOT NULL,

  `company_telephone` varchar(20) NOT NULL,

  `company_addressid` int(9) NOT NULL

 

person

  `person_id` int(11) NOT NULL AUTO_INCREMENT,

  `person_firstname` varchar(20) NOT NULL,

  `person_lastname` varchar(20) NOT NULL,

  `person_email` varchar(30) NOT NULL,

  `person_emailstatus` varchar(12) NOT NULL,

  `person_position` varchar(50) NOT NULL

 

answer

  `answer_id` int(11) NOT NULL AUTO_INCREMENT,

  `answer_1` varchar(50) NOT NULL,

  `answer_2` varchar(50) NOT NULL,

  `answer_3` varchar(50) NOT NULL,

 

 

The problem:

I want to insert from the temporary table to this 3 tables were i need it to be relational so in the table company one company only can be one time ( the company_addressid is a unique number for that company ) so when i insert to that table it should match this company_addressid anf if exists it only should update the data

 

when insert to person it should be the same as company but in this case the person_email is the unique data to match from so if exists only update the record

 

when insert to answer it should be the same as person but in this case if the person has already one answer attached to him it should only update the record

 

Solution:

I think i have to have the following extra columns:

In person:

company_id

 

In answer:

person_id

 

 

Hope someone can help me in the right direction how to select from the temporary table and split it to this 3 tables and still avoid duplicates in the company, person, answer

 

If you have some example please post it

[fenway]

Sorry, I don't follow -- why is there a temporary table?

[El Heso]

i have a import function for the csv files and the files is different everytime so i only upload and make a temporary table with columnsname of the columns in the csv file then i want to distribute the content to the right table

 

Hope you understand what i´m talking about :-)

 

Sorry, I don't follow -- why is there a temporary table?

[fenway]

Yes, I see.  Well, for each record in your temp table, you'll need to query the other tables to see if there are any matches.  If so, use that UID.  If not, INSERT, then grab the new UID.

[El Heso]

Yes i know but can you show me some code how to manage that or links to example or tutorials i´m a novice on this :-)

 

Yes, I see.  Well, for each record in your temp table, you'll need to query the other tables to see if there are any matches.  If so, use that UID.  If not, INSERT, then grab the new UID.

[fenway]

I'm not good with tutorials -- maybe someone else has a suggestion.

 

But that's the logic of pseudo-code.