Ok, so here's the scenario, and I'll dumb it down a bit to make it easier to understand. I already have a solution for this, but I was looking for a better way to do it if there is one and would like some input.
I have two tables, one is a parent table ( records ) and the other is the child table (items).
For each record, there may be multiple items.
The records table has an auto_number in the records.id field that is set up as a 1>M relationship in the child table (items) on the items.record_id field.
Ok, so that's all good, and works fine.
However, here's the problem. Since I'm using the auto_number as the Foreign Key in the child table, being a multi-user environment, person 2 can come along and add a record while person 1 is still creating theirs. Person 2 has just stolen Person 1's auto_number.. effectively corrupting both records as Person 2's record will have their items as well as Person 1's, and Person 1 will have none.
Person 1 chooses to create a new record, and grabs the next auto_num from the database, say 500.
Person 1 starts adding items to their record, using 500 as the FK in the items table.
Person 2 chooses to create new record shortly after and grabs the next auto_num from the database. Since Person 1 is still editing their record, the next auto_num is still 500.
Person 2 starts adding items using the FK 500.
Person 2 finishes and submits their record commiting their data to the parent records table with the id 500.
Person 1 finishes and submits their record expecting the auto_num 500. Since 500 is now already taken by Person 2, 501 is used.
If person 1 tries to view their record after creation, they will have no items listed on their record.
See the issue? Basically what I'm wondering, is if there's a way in MySQL to put a claim or reservation on the next auto_number without actually adding a record.
I could add a NULL row to effectively "claim" the next auto_number, however this would end in a lot of orphaned records if someone backs out of adding a record.
There are long ways around this, but I'm looking for suggestions.
Thanks for your help in advance.