Suggest better design
Posted: Mon Dec 20, 2010 2:13 am
I am trying to design a job which does following.
1) Get source flat file(havingng approx. 1-2M records) and separate them into 2 files (file1,file2) based on category.
2) key columns are same in these files.Load file 1 records to table1.
File 2 is having all comma delimited fields(variable number of fields- max upto 20) and key columns.
Now i have to convert each column to be a row(other than key columns) and
then load the data to table 2.
table1 to table2 relationship is 1 to 0..*(0-many).
what am i trying do?
Since i have large volume of data in db i don't want to look up either in the db at runtime or load huge data to hashed file.
so that i can reduce the loading and look up time.
Try load the data in table 1 first and if there are any failures write to a hashed file FILE-A(may be duplicates -i.e load all master records first)
Now read each file2 record and lookup in FILE-A and if not found, insert record to table2(detail records).
Is this right way of loading the data? Am i suppose to load master and corresponding details at once?
1) Get source flat file(havingng approx. 1-2M records) and separate them into 2 files (file1,file2) based on category.
2) key columns are same in these files.Load file 1 records to table1.
File 2 is having all comma delimited fields(variable number of fields- max upto 20) and key columns.
Now i have to convert each column to be a row(other than key columns) and
then load the data to table 2.
table1 to table2 relationship is 1 to 0..*(0-many).
what am i trying do?
Since i have large volume of data in db i don't want to look up either in the db at runtime or load huge data to hashed file.
so that i can reduce the loading and look up time.
Try load the data in table 1 first and if there are any failures write to a hashed file FILE-A(may be duplicates -i.e load all master records first)
Now read each file2 record and lookup in FILE-A and if not found, insert record to table2(detail records).
Is this right way of loading the data? Am i suppose to load master and corresponding details at once?