A design question with files, aggregators, data bases, etc.

[simplex-software]

Greetings,

I have the following case: different inter-related files come by FTP. These files are related in the sense that one may contain a record, say a Product, and another file may contain records concerning the Product movements. The relation is based on a product id. Basically, the data come from a database having a one to many relationshipm but, for some reason, it arrives as separate files, one file with the product definitions and any number of files that may contain movements of the defined products. I need to consolidate all that in a database having this one to many relationship.

What would be the recommended design of that, knowing that the files may be huge and their number unknown ?

Many thanks in advance,

Nicolas

[davsclaus]

Can you process the files out of order? eg can you process a product movement, before the product "master" file? If you can then its most likely much easier. As I would then assume you can insert the data directly into the database.

Also is there some indication when there is no more movements? Or can there be product movement files in the future, eg lets say in 3 days a new product movement files is downloaded?

[simplex-software]

Hi Claus,

Yes, normally this files are independent and could come out of order, even if this is probably not what will happen most of times. And yes, at one point in time we know there won't be more movement and we then we may drop a file named "done" to trigger that there are no more files. But yes, this may happen 3 days later.

I thought to have one route by file type, one for the "master" file and one for the "derivated" ones, which parse the XML conetnt, validates ut against the schema, unmarshall the DOM and store every thing a in temporary database table. Once that we know there are no more files, we use 2 temporary tables to consolidate our real datamodel.

How does it sound ? Obviously, in this case, the aggregator EIP is not of a big help, is that correct ?

Many thanks in advance,

Nicolas

[davsclaus]

Yes if you can store directly in your database, whether its a temporary table, or the real table, that would be the best.

For example you may have a states column in the product master table, to indicate if its done or not. So when its done, the applications can read the product data. If you only is allowed to read the data when its done.

The Camel aggregator should then not be needed.

[simplex-software]

Thanks Claus.

Kind regards,

Nicolas