Import Related - Long Description - Easy Answer (Hopefully)

[roopurt18]

So I've got a situation where I'm not sure what the best way to proceed is.

 

Firstly, the web system I maintain imports data from an external source.  Each time it does this import it wipes out all existing data and replaces it with the new incoming data.  For example, if the `projects` table is importing, all existing projects are wiped out and replaced with the incoming data.  This has the negative side effect that the web system is entirely data dependent on the other application and can not stand on its own as a product.  I'm doing some redesign so that it can become a stand-alone system.

 

Following are sample descriptions of the tables.

 

Here is my incoming data format:

projects

pro_code VARCHAR(6) # project code

name VARCHAR(32) # project name

 

Here is my project table on the web:

x2_projects

id PRIMARY KEY AUTO_INCREMENT etc.

name VARCHAR(32) # project name

dsrc TINYINT # data source, 2 indicates origin is from importation

created DATETIME

modified TIMESTAMP

 

Here is the table I use to keep track of imported projects and their assigned web ID

x2_impids_projects

wv_id INT # web id, i.e. x2_projects.id

pro_code VARCHAR() # project code, i.e. projects.pro_code

 

Here is what I'm currently doing:

• 
   
• Creating a temp table from `projects`, adding a `wv_id` column (default 0), and SELECT'ing all data from `projects`
   
• Updating `temp`.`wv_id` using `x2_impids_projects` so that all projects in the temp table will have their corresponding wv_id; this way I know if the wv_id is zero it's a new project, if it's non-zero it's an existing project
   
• Updating `x2_projects` from the temp table
  

 

I need to insert the projects from the temp table into the web table, which is easy. I then need to update the `x2_impids_projects` table with the auto_incrementing id and the pro_code that it was attached to, but by now I've lost the ability to connect that information.

 

So here's the question.  Would it be better to:

1) Perform an alter table on `x2_projects` and add a `pro_code` column.  I could then update `x2_impids_projects` very easily and then drop the `pro_code` column.  This is the easier option IMO.

2) Alternatively, when creating the temp table I can add an auto_incrementing field and set it's value to the next value that would occur in `x2_projects`.  I would have to lock `x2_projects`, create the temp table, populate the temp table, update `x2_impids_projects`, insert and update on `x2_projects`, and finally release the lock.  This is the safer option as all of the work occurs in the temp area and if it chokes there's less chance of affecting the live table.  However, I'm not entirely sure how to go about getting the next auto_increment id out of the existing table.

 

So I'm looking for opinions and suggestions.  I'm also concerned about performance (who isn't); most of these tables are pretty small, less than 1k records.  However, some of them are quite large, but nothing more than 100k records AFAIK.

 

Thanks in advance.

[fenway]

I would go with option (1).. with some care, it shouldn't affect live data.