mysql - Importing new data to master database versus temporary database? -

mysql - Importing new data to master database versus temporary database? -

i designing mysql database new project. importing 50-60 mb of info on daily basis.

there main table primary key. there kid tables own primary key , foreign key pointing main table.

new info has parsed giant text file , minor manipulations made prior importing master database. parsing , import operation may involve important amount of troubleshooting want import new info temporary database , ensure integrity before adding master.

for reason, thought parse , import new info separate, temporary database each day. in way, able inspect info prior adding master , @ same time have each day's info stored separate database should ever need rebuild master later on individual temporary databases.

i considering utilize of primary keys / foreign keys innodb engine in order maintain relational integrity across tables. means have worry auto-increment ids (primary key) not having duplicates when go import new info each day.

so, given situation, best?

make re-create of master , import straight re-create of master each day. replace existing master new copy.

import new info temporary database each day alter auto-increment start value of primary keys greater maximum in master. alter auto-increment values primary keys tables (main table , children)?

import new info temporary database each day, not worrying primary key values. find other way merge temporary database master without collisions of primary keys? if using strategy, how can update primary key in main table new info while making sure relationships kid tables remain correct?

i'm not sure complicated making it?

why not this:

import raw info temporary table (why have separate database?) run transformations/integrity checks on temporary table. when info good, insert straight master table. use auto incrementing ids on master table not dependent on info beingness imported. allows have unique id and original ids might have existed in import. add field master table(s) gives record of import records came from. in add-on copying info master table, create log ties info merged. helps out info if find it's wrong/bad , gives audit trail.

in end set sandbox database, write bunch of stored procedures , test crap out of it. =)

mysql database database-design