remote
     synthesis

Handling Unique IDs

Posted on Oct 21, 2005

Recently, in my spare time I have been working with Doug Hughes' Reactor framework. It is still in an early beta release, but so far I am very excited by the project. Something about the way it works suits my programming style and feels comfortable for me. I have been having alot of discussion with Doug via his blog about handling certain issues that have arisen, the most recent over how to handle calling insert or update for the record. The question seems simple but in part comes down to differing ways of handling creating unique IDs for records, and, perhaps, how my means of handling this may differ from common practice (I am not sure to be honest if this is the case or not - which is the reason for my post)Essentially, Reactor's current setup assumes that a new record will not have the primary key provided and an existing record will. This is how it determines whether or not to call insert or update. In my case, I always provide the the primary key except where I use an auto-incrementing integer which I do rarely for reason I will explain (having to do with experience that may be outdated - not sure). In most cases I either use a CF UUID or a SQL unique identifier.

In all those cases, I generate the ID in ColdFusion and do not set a newid() default to the field. The reason I do this is because I can assign the id from the outset and not have to wait for an insert to complete before knowing the id associated with the data I am working with. This has many applications in my mind, but the original cause for this was the common practice of users to submit a new record and then use the "back" button to make a modification. When the id was generated by the database, this would actually cause a new record to be created because my application was not aware that you were actually resubmitting the same form/data. This has also come in handy for other reasons, like (in a recent one that pops into my head) where related data was edited in a seperate window...

I have always felt that this practice served me well, but perhaps that has caused me to overloook a better/more effective solution that would make generating the unique id by the database preferable. Anyone have opinions?

Comments

Brian,

At the risk of bragging on my own product, you might check out my DataMgr component (http://www.bryantwebconsulting.com/cfcs/). While it doesn't attempt to do what Reactor does exactly, it might work more inline with your practices.

It bases its decision for insert or update only in part based on the primary key. You can also have it update a record if all of the queryable data matches an existing record for an insert. In any event it will return the primary key of the record.

It will work with SQL Server GUIDs and Identities. It should actually work with any type of primary key, but those are the only ones that I have tested explicitly.

Posted By Steve Bryant / Posted on 10/21/2005 at 2:32 PM