Primary keys on webforms (load initially or on save) ?

Question

This is just a general question irrespective of database architecture. I am maintaining an ASP.NET web application. The structure is such that,

Say on 'Add a new employee' webform

• The primary key (or the record id to be saved with) is initially loaded on form load event & displayed as a label
  • So when the form loads, the record id to save with is shown to the user

Positives:

• End user already knows what the id/serial of the form is (even before he saves the form)
• So on form save when he is directed to gridview screen (with all entries) he can search recrods easily (although the most recent one is at the top anyway)

Negatives:

• If he does not save the form, say he just cancels after loading the data entry form, the id/key initially fetched is wasted (in my case it is a sequence field fetched on form load from database)

What do you guys do in these scenarios ? Which approach would you recommend for 'web applications'? And how to facilitate the user with a different approach ? Is our current approach recommended (To me,it wastes the ids/sequence from database)

Answer 1

I'd always recommend not presenting the identity field value for the record being created until the record has been created. The "create a temporary placeholder record first to obtain the identity field value ahead of time" approach can, as you mention, result in wasted IDs, unless you have a process in place to reclaim them.

You can always pop-up a message box when the user presses save that tells them the identity field value of the newly created record.

Answer 2

In this situation you could use a GUID created by the application itself. The database would then only have the PK set to be a Unique Identifier (GUID) and that it must not be null. In this situation you are not wasting any unique keys as each call to get a new GUID should be definition produce a (mathmatically) unique identifier. It is worth noting that if you use this method, it is best to make sure your PK is not set up to be clustered. The resulting index reorganisation upon insert could quickly result in an application that suffers performance hits.

Answer 3

For one: I wouldn't care so much about wasted id values. When you are in danger of running out of int32 values (and when has that happened to you last?), use int64. The user experience is way much more important than wasting a few id values.

Having said that, I would not want the primary key to be anything the user would want to type in. If you are having a primary key that users need to type in, chances are it then is (or will be requested to be) more than just an int32/64 value and carries (will carry) meaning in its composition and/or formatting. Primary keys should not have that. (Tons of reasons google for meaningless primary keys or other such terms).

If you need a meaningful key, make it a secondary index that is in no way related to the primary key. If a part of that is still a sequential number taken from some counter value in your database. Decide whether functionally it is a problem for gaps to appear in the sequence. (The tax people generally don't want gaps in invoice numbers). If functionally it is no problem, then certainly don't start worrying about it technically. If functionally it is a problem, then yes, you have no option but to wait for the save in order to show it to the user. But, please, when you do, don't do it in a popup. They are horribly intrusive as they have to be dismissed. Just put up an informative message on the screen where the user is sent after (s)he saves the new employee. Much like gmail is telling you about actions you have performed just above the list of messages.