In which we see that not everyone understands what it is to be unique.
I am currently wrapped up in an EDI implementation, and while I take issue with a great many things in the EDI world, this post will be about just one of the many flaws in a piece of software used to help manage an EDI workflow.
Setting the stage.
Let us talk in somewhat generic, higher level terms so that we do not get bogged down in the complexities of EDI TSETS (transmissions).
Suppose in our database we have a simple parent/child relationship. For a given parent, you may have one or more children. Now let us also suppose each child has a unique identifier used to link the child record to records related to it so that we have something like this:
On the EDI side, suppose the TSET has some ‘header’ data that represents the Parent, that there is a group of segments we can loop over that represent the children, and that some of the data for a child is optional. When the optional child data exists, we would like to insert it into the ChildData table.
Thus far everything seems pretty straight forward and easy.
Enter the EDI software.
As it turns out, the EDI software does not allow you to insert a record and retrieve the Id assigned by the database (at least there is neither an obvious way nor one that does not involve the occult). Not to worry, the EDI software has a way generate unique ids that we can use when we insert child records as well as child data records. This is accomplished by an operator called “Replace With Unique String”.
So we wire everything up, load a test TSET, and tell the software to import it. Tada! Several of the children have the same Id. At this point, frustrated with how soul crushingly difficult everything thing is with this software, you sigh because you know you have been let down again.
Back in the interface for building the map, you dig up the Help Info for the “Replace With Unique String” operator and find the following:
Oh lovely you think; It generates a unique 12 character strings. Twelve seems a bit short to be unique in space and time, but hey we paid good money for this software. We are sure they know what they are doing. And it is guaranteed to be unique even if called from separate instances in the same millisecond!
Yes. Yes they did. The developers who implemented this software used a timestamp encoded in some special string format as a unique identifier.
And you sigh again because you know the poor developers must have been forced to work on ancient computers where millisecond accuracy is good enough. Their hardware was not fast enough to loop over items any quicker. Alas, our server is. It would seem our server can process several records in a millisecond as is evident by the fact that many records share the same id. Even more tragic, sometimes if you click the import button at just the right time with the right load on the server, they all get unique ids.
For anyone out there thinking they will solve their unique Id requirement with a timestamp, please stop. Processors are fast, your computer is fast, many things may happen during the time span measurable by the resolution of your typical Date.getTime() call (or your language’s equivalent).