Is it really that bad?

Hi,
I've just spent the last 2 months working like a dog (yep, just like everyone else), implementing a database and a few associated applications. Unfortunately, my employers main designers were busy with other things, so the implementation was left to me. Given that there was a fairly tight deadline, I bashed out what I thought was a good solution to the problem, and which could reasonably be extended or replaced simply when revisiting the application for stage 2. However they have now decided that this solution wasn't good enough and are getting antsy on paying...

My problem is:
In Java, we mapped every (relational) database table to a class, gave every row in each table a unique id, all fairly straight-forward, until the came time to save the objects. There didn't seem to be any transaction management in the database, which was something they wanted, so what I did was: When an object (i.e. a row) - changed or new - was to be saved the object was added to a list. Every object (well, class) had to know it's associations, so all associated objects could be added to this list. I then 'started a transaction', iterated over the list assigning a unique identifier to each object that needed one, iterated again filling in associations, then reversed the list and saved it _thinking_ I was safe in the knowledge that any associated instances must have been saved before an instance was saved, so that all my lovely database constraints would be met - and then committed the whole transaction, or rolled it all back on a failure. i.e.:

list:                      o_1 : o_2 : o_3
assign unique identifiers  o_1.assign_UID : o_2.assign_UID


                                        : o_3.assign_UID


assign foreign keys        o_1.assign_FK(o_2) : o1.assign_FK(o_3)


                                        : o_2.assign_FK(o_3)


                                        : o_3.(no assoc.'s)


save                       o_3.save : o_2.save  : o_1.save
                              (can save o1 as o_2 and o_3 now
                                            exist)

This was reasonably robust, as _most_ of the database was related to one table - which was where 'save' was called - and there were no many to many or one to one relationships. Unfortunately, I didn't handle the one case where this main table (t1) had a 1 to 0 or many association (well several, but not the issue) to another table (t4). Of course meaning that the instance of t1 had to be saved before the t4 instance ( so that the t4 constraint t4.fk_1 references t1.uid_1 was met) and based on my reversing the list method wasn't happening.

The simple fix as I see it, is: rather than adding all the associated objects to the list, just pass the list to each class and let the class add all associated objects and itself, in the correct order, but I appreciate that this is just carrying on down the same track.

My question then, is: what is the correct (or any better) method of saving the object and its associated instances and assigning uid's and foreign keys, in one hit?
(And given I was explicitly told that they didn't want to implement an association class.)

I appreciate that this is mainly an application question, however the issue really is with the my handling of the database, which is why I posted here. Also I have been lurking for a while and seen other posts that are similar to these issues, so thought this was a better place to ask than anywhere else (c.d.o seems pretty quiet lately). If however I am too far off-topic, please ignore the post, and I hope you enjoyed reading ;-)

Thanks for your time.
Kurt. Received on Tue Mar 06 2001 - 14:56:50 CET