UNI/CARE Pro-Filer™ Reporting: The Enigma of the OIDs – What they are, what they do, how to create them and why there is no need to fear them.

It doesn't take long after you begin your Pro-Filer™ implementation that you become aware of the word "OID".  OID is an acronym for Object IDentifier.  In the Pro-Filer™ database OIDs are used as a means of linking data through primary and foreign key relationships.  On both the primary key and foreign key exists a matching OID.  This is how Pro-Filer™ knows that one record is related to another.  It is critical then that every OID within a system be unique.  The first column of every table in Pro-Filer™ is the OID column.  Every row in every table of a Pro-Filer™ database has its unique OID existing in that first column of the table.

An OID is an identifier standard that UNI/CARE uses within Pro-Filer™. The intent of using OIDs is to enable distributed systems to uniquely identify information without any central coordination. Thus, each UNI/CARE Pro-Filer™ installation can create an OID and use it to identify a record within the database with reasonable confidence that the identifier will never be unintentionally used by anyone for anything else. Therefore, information labeled with OIDs can be later combined into a single database without needing to resolve name conflicts.

An OID is a 128 bit number displayed as a 32 character hex-decimal:

    18F55E86905C413280551BE264FB8D0B

128-bits is big enough and the generation algorithm is unique enough that if 1,000,000,000 OIDs per second were generated for 1 year the probability of a duplicate would be only 50%. Or if every human on Earth generated 600,000,000 OIDs there would only be a 50% probability of a duplicate.

We can generate an OID within Microsoft SQL by using the following code:

     select newid()

Which will return the result:

    18F55E86-905C-4132-8055-1BE264FB8D0B

As you can see this is in the form of a standard UUID (Universally Unique Identifier) or GUID (Globally Unique Identifier) with the dashes in place.  UNI/CARE strips the dashes from the UUID format in Pro-Filer™ to create an OID.  The code to create an OID without the dashes is shown below:

     select cast(replace(newid(),'-','')as varchar(32))

Which will return the result:

    18F55E86905C413280551BE264FB8D0B

Now, when a user asks you "What the heck is an OID?"; you have the answer.

Til next time...

GW