what to do when we may need to save slave data first

Question

In a one-to-many relationship what's the best way to handle data so it's flexible enough for the user to save the slave data before he saves the master table data.

• reserving the row ID of the master so the I can save de slave data with the reserved master id
• save slave data in a temporary table so that when we save the master data we can "import" the data in the temporary table
• other??

Example in a ticket/upload multiple files form where the users has the possibility to upload the files before sendind the ticket information:

Master table PK ticket description

Slave table PK Master_FK File

Answer 1

Why can't you create the master row and flag it as incomplete?

Answer 2

Are your id's autogenerated?

You have several choices all with possible problems.

First don't define a FK relationship. Now how do you account for records in a partial state and those who never get married up to the real record? And how do you intend to marry up the records when the main record is inserted?

Insert a record into the master table first that where everything is blank except the id. This makes enforcing all required fields default to the user application, which I'm not wild about from a data integrity standpoint.

Third and most complex but probably safest - use 3 tables. Create the master record in a table that only contains the master recordid and return that to your application on opening the form to create a new record. Create a pk/fk relationship to both the orginal master table and the foreign key table. Remove the autogeneration of the id from the orginal master table and insert the id from the new master table instead when you insert the record. Insert the new master table id when you insert records to the orginal FK table as well. At least this way, you can continue to have all the required fields marked as required in the database but the relationship is between the new table and the other table not the original table and the other table. This won't affect querying (as long as you have proper indexing), but will make things more complicated if you delete records as you could leave some hanging out if you aren't careful. Also you would have to consider if there are other processes (such as data imports from another source) which might be inserting records to the main table which would have to be adjusted as the id would no longer be autogenerated..

Answer 3

In case of upload you will have to create temporary storage for not committed upload. So that once upload started you save all new files in a separate table. Once user ready to submit ticket you save ticket and append files from temp table.

Also you can create fake record if it possible with some fixed id in master table. You then have to make sure that fake record does not appear in queries in other places.

Third, you can create stored procedure which would generate id for primary table and increment identity counter. If user aborts operation reserved id will not affect anything. It is just like if you create master record and then delete it. You can create temporary records in master table as well.

Answer 4

In Oracle (maybe others?) you can defer a constraint's validation until COMMIT time.

So you could insert the child rows first. (You'd need the parent key, obviously.)