Primary Key Problem



  • Hi smartest people of the world! lol

    It's not an immediate problem, but it's something I face from time to time. Let's say that I have a situation in that records referencing to one 'main' record in another table may need to be added before the user actually confirms the creation (and, therefore, the transaction is commited) of the 'main' record. A simple example of that could be an image gallery:

    Let's say that I have a table called 'pictures' and a table called 'galleries'. How would I make it possible to the user to (supposing in this example that it's a web app) enter the Gallery Creation Page (GCP) and add images to this gallery before submitting the form and actually create the record in the galleries table?

    When this happens to me, I usually make the database create a blank record the moment the user enters the GCP. The downsides of this is that, this way, I can't use the database's built-in data consistency and that it leaves blank records in the database if the user abandons the GCP, as when s/he clicks the GCP link for mistake.

    The other solution I've also done is not using auto-increment in the primary key at all. To create new records, I usually create a small function that gets the MAX() of the ID field and sums one. This is, at least for me, niftier than the others, but it still has downsides: if the user adds any relative records (pictures) but cancels the main operation (gallery creation), the relative records (pictures) will become orphan, and worse, they will relate to the next main record (gallery) created, as MAX(ID) + 1 will not change as no records have been added because the operation has been cancelled. Also there is a data concurrency problem: if Alice and Bob (A and B) enter the page at the same time, MAX(ID) + 1 will have the same value for both until one of them confirms the operation. Worse, if A adds relative records (pictures), but B confirms the main record (gallery) creation before, A's relative records will relate to B's main record, and no relative records that A added will relate to her own main record.

    Well, this is the (long) description of the problem. How do you guys deal with this? Any suggestions welcome. =)

    PS: sorry for making such a prolix explanation, but I simply couldn't reduce it to be simpler without making it clear. Hope you don't get lost in the texts!
     



  • @Rafael Almeida said:

    How do you guys deal with this?

    STOP USING DATABASES FOR TEMPORARY STORAGE.

    (Where's the damn blink tag when you need it? This is almost the worst forum software I have ever vomited on)



  • @asuffield said:

    STOP USING DATABASES FOR TEMPORARY STORAGE.

    I <3 you. 



  • @Rafael Almeida said:

    Well, this is the (long) description of the problem. How do you guys deal with this? Any suggestions welcome. =)

    One of the things you could do is set up another database that's a temporary holding place.  When the user starts a new gallery, you make an entry there, then attach pictures as they're uploaded.  You can associate it with a user account so if the browser crashes or the user loses connectivity, the user account can pick up where the browser session left off.  Once you're completely done, copy it over, and you're done.  Just keep a created date on each temporary entry, so you know if something looks old enough to delete.



  • Thank you, I'll try this one!



  • STOP USING DATABASES FOR TEMPORARY STORAGE.

    What do you use as temporary storage when you face a problem that leaves you no other choice? 



  • Using max(id)+1 is not a good idea - you ask for race conditions that way.  IMO, the easiest way to solve your problem is to create the galery record, including some creation timestamp and a status field that says "temporary".

    (No need for another database, except maybe for performance reasons...)

    Once the users adds images, the status goes to "permanent". Then use a "cleanup" job that deletes galery records that are older than e.g. 24 hours and still have a the status "temporary".



  • I don't understand the problem. Maybe I just don't getyour "data consistency."

    The user wants to create a new gallery and then add pictures to that, right? I would _think_ that the server would be given the following actions:

    1) Create new gallery (with name "Holiday Pictures")

    2) Add picture "sunset.jpg" from local disk

    3) Add picture "family.jpg" from local disk (or can pictures already be in the database?)

    Now what I don't understand is why you create an "blank records" and what you understand by submitting the form.

    How does the user add pictures? They can't magically float into the database, so presumably they must be *submitted* via a POST form. How can the user *SUBMIT* images without also submitting the 'create gallery' form? That's an insane design :)

    But even IF you wanted the user to upload images before making a new gallery, that's easily done as well. Just throw the pictures into the pictures table, it only needs to have an owner column. When the user is done uploading, you can query for all of his pictures without a gallery and let the user select which should go into the new gallery. iPhoto does something like that with the "most recently imported pictures" folder.

     

    As far as consistency goes:

    1) You can have empty galleries.

    2) You can have pictures in more than one gallery (maybe not in a real photo album, but on most modern systems)

     You could easily make both n-to-n relations.

    If it's an absolute requirement, however, that you enforce 1-1 / 1-n relations, then give each user a "Temporary Gallery" / "Uploaded Pictures" thing and throw the images in there. When creating a new gallery, you just need to move the images from the holding gallery to the new one.
     


     



  • @ammoQ said:

    Using max(id)+1 is not a good idea - you ask for race conditions that way.  IMO, the easiest way to solve your problem is to create the galery record, including some creation timestamp and a status field that says "temporary".

    (No need for another database, except maybe for performance reasons...)

    Once the users adds images, the status goes to "permanent". Then use a "cleanup" job that deletes galery records that are older than e.g. 24 hours and still have a the status "temporary".

    max(id)+1 has correct, race-free counterparts in the form of @@identity, SCOPE_IDENTITY and the other one which' name escapes me. (I just read this stuff somewhere and I'm not really down with DB's etc.)
     



  • Those 2 are something completely different.

     

    Both Functions will return you the Value of the last Primary key that was generated. Scope_Identity limits this to the current statement, and @@Identity should NOT be used, since it will also consider IDs that where generated by a trigger... So if you insert a record into your main table, and then a trigger inserts something into an audit train, you will get the ID of the audit train back. Scope_Identity will give you the ID of the main record...

     

    I hope this makes sense...

     

    But both functions only work AFTER you inserted the record;)



  • @Rafael Almeida said:

    STOP USING DATABASES FOR TEMPORARY STORAGE.

    What do you use as temporary storage when you face a problem that leaves you no other choice? 

    That would depend on the nature of what I was implementing. For most web apps, the framework provides a perfectly functional system for storing session data, which would likely be appropriate for something like this. 



  • @ammoQ said:

    Using max(id)+1 is not a good idea - you ask for race conditions that way.  IMO, the easiest way to solve your problem is to create the galery record, including some creation timestamp and a status field that says "temporary".

    (No need for another database, except maybe for performance reasons...)

    Once the users adds images, the status goes to "permanent". Then use a "cleanup" job that deletes galery records that are older than e.g. 24 hours and still have a the status "temporary".

     I like ammoQ's solution better than mine, especially for the first implementation.  Make sure to use stored procedures or some other abstraction layer so you can distinguish between temporary and permanent types of records.  That way you can (in theory) break them apart later if the spirit moves you.


Log in to reply