What to do when we might need to save slave data first
In a one-to-many relationship, the best way to handle data is to be flexible enough that the user can save slave data before he saves the master table data.
- reservation of the master row id so that I can store slave data with the reserved master id
- save the slave data in a temporary table so that when saving the master data we can "import" the data into the temporary table
- other??
Example in ticket / multiple file upload form where users have the option to upload files before submitting ticket information:
PK master table ticket description
Slave Table PK Master_FK File
Has your ID been generated?
You have several options for solving possible problems.
Don't define the FK relationship first. Now, how do you account for partial state records and those who never get married before the actual record? And how do you intend to marry records when the master record is inserted?
First insert a record into the main table where everything is empty except id. This enforces all required fields by default for a custom application, which I am not alone in terms of data integrity.
The third and most difficult, but probably the safest, is to use 3 tables. Create a master record in a table that contains only the master record and returns it to your application when you open a form to create a new record. Create a pk / fk relationship on both the original main table and the foreign key table. Remove the auto-generating id from the original main table and insert the id from the new main table instead of inserting a record. Insert the new ID of the main table when you insert records into the original FK table. At least in this way, you can continue to have all the required fields marked as needed in the database, but the relationship between the new table and the other table is not the original table and the other table.This will not affect queries (if you have the correct indexing), but will complicate things if you delete the entries as you might leave some dangling if you're not careful. Also you will need to consider if there are other processes (like importing data from another source) that might be inserting records into the main table that would need to be adjusted as the ID will no longer be autogenerated.
source to share
In case of download, you need to create temporary storage for floating download. So after uploading, you saved all new files in a separate table. After the user is ready to submit a ticket, you save the ticket and add files from the temp table.
Also you can create a fake record if possible with some fixed ID in the main table Then you have to make sure that the fake entry does not appear in requests elsewhere.
Third, you can create a stored procedure that will generate an id for the primary table and increment the id counter. If the user aborts the operation, the reserved ID will have no effect. It's like creating a master record and then deleting it. You can also create temporary records in the main table.
source to share