database - best strategy for multiple insertions in db from one file -

let me clarify title of question.

i'm implementing web application's back-end.

this create in 1 single .html file

• user inserts point,line or polygon on map.
• user fills form text data. data relative geometry inserted on map.
• user uploads photos , videos via html5 websockets/drag'n'drop. relative geometry inserted on map.

there 1 button @ bottom of page, named "save all" saves data @ once on database (insert).

the problem? have 2 seperate tables in database. 1 named "pins" , geometry data , form data.

the other named "multimedia" , images/videos.

now "multimedia" has column named "multipin" contains "pins" id, can relate pins , multimedia.

all columns containig ids "serial" (with each insertion, id automatically grows +1).

i thinking if both insertions @ same time there no way can new "pins" id , insert "multipin" column of "multimedia". have wait new "pins" id created , insert "multipin".

so, 2 insertions (?) solution came creating 3 html files. on first, user inserts geometry , form data. hits button named "click here add multimedia". button sends data file inserts in "pins" , gets new id. redirects third file , sends new "pins". user can insert photo/video , code knows "pins" id , can put in "multipin".

does sounds fine? there better way or strategy?

for "save @ once" version thinking websockets...for "3 files version" ? should use "traditional" ajax , websockets multimedia?

i apologize big question. , english (not native language)

thank time

slevin

save @ once better strategy user.

assuming you're using transactional relational database, can save following way.

• insert pins table data.

• commit

• get pins sequential id

• insert multimedia

• commit

if there's problem multimedia, rollback multimedia inserts , delete pins table data row keep database consistency.