Transform 1 table into 3 tables using foreign keys and associative entity

[Anonymous]

Hello,

I'm quite beginner with talend and i don't know how to load data from one table into three new tables (associative entity), see picture below.

Both ID's from person and coordinate are generated automatically (auto_incremented ID's) and I need to get them in order to fulfill my last table person_coordinate. I have already find and use solution about one similar case when there is only one "parent". I had to disable "extend insert" then add a rule with the expression "LAST_INSERT_ID()" but that can't be applied in my current case because I need two ID's...

Thanks,

Guillaume

[dgreenx · Accepted solution]

You are asking tMap to do something both before (batch records for output) and after the database has done something (assign unique ids). That is probably not going to work.

You could follow this tMap (just the first two files, Person and Coordiates), with another tMap reading the same input and doing a lookup of Person and a lookup of Coordinates from the data, but you will probably have duplicate names and duplicate coordinates in your file over time.

Maybe a better solution would be to assign the IDs yourself by using a sequential number function. You would need to find the last id entered into the Person table and the last id entered into the Coordinates table. Then you could use a formula in the tMap output field "Numeric.sequence("s1",1,1)" where S1 is the sequence identifier and the first number parameter is the starting value (from your last table id plus 1) and the last number parameter is the increment value. You would need four sequence formulas:

For ID field in Person table: Numeric.sequence("person1", (lastPersonId+1),1)

For ID field in Coordinates table: Numeric.sequence("coordinate1",(lastCoordId+1),1)

For PeronID field in third table: Numeric.sequence("person2", (lastPersonId+1),1)

For CoordinateID field in third table: Numeric.sequence("coordinate2",(lastCoordId+1),1)

If your database will let you override the ID in an AutoID field, this would be the way to go, but you could only do this if no one else was updating the file at the same time.

Hope this gives you some ideas,

dg

[Anonymous]

Thank you very much for all your clear explanations, that seems great ! I may probably use the first solution with some modifications in order to avoid duplicate names and/or coordinates.

[dgreenx]

Sounds good. I figured you have more data that might make the records more specific. Good luck.