I understand how this could be a complex job and the first insert may only contain 30% of the total rows known at this time.
I would consider making sprocs for inserts into each unique table that returns when necessary the PKey of that insert.
jobInsert itemInsert detailsInsert offshootsInsert
Also make: jobSelect itemSelect detailsSelect offshootsSelect
In some of my databases there are hundreds of sprocs, 400-500 in number.
jobAllAspects could have all of the joins needed to pull the entire beast into one dataset or all of the tables in independent returned datasets. We do a lot of the latter here at Ring.
On Thu, Mar 10, 2016 at 11:26 AM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2016-03-10 10:55, Stephen Russell wrote:
"until I was absolutely sure I wanted to save the entire dataset."
That is exactly what we are talking about. When user clicks save, submit, ok, button they are in save mode. Then you commit header row(s) retaining the fkey(s) necessary for your transactional details.
Yes but until the user does the Save, I have to keep the relationship hierarchy for primary keys and related foreign keys.
Example (where cID is the table's primary key):
- Create Job (cID in Jobs cursor)
- Create 1:M items (cID in Items cursor, with cJobID foreign key pointing
back to Jobs table) 3) Create 1:M details about each item (cID in Details cursor, with cItemID foreign key pointing back to Items table) 4) Create some 1:M offshoots perhaps for each Detail (...you see the trend...)
Rather than add all those records immediately to the database and later abandon because the dude hits "Cancel", I prefer to create my own keys rather than rely on AutoIncrement to have full control like this.
[excessive quoting removed by server]