Campfire #8 : Application Design Using Universally Unique Identifiers [Webinar 53min]

In the upcoming release of Flex2SQL v12.1, Mertech has added native support for UUIDs and GUIDs (Universally/Globally Unique Identifiers), opening up a whole new way of thinking about data models and application design.

On October 17, 2013, Mertech continued the Campfire initiative with a session on UUIDs, led by Mertech representative Oliver Nelson (Director of Engineering).

The session began by defining UUIDs (algorithmically-generated 128-bit integers) and talking about the mathematical chance of collision (after generating 70 Billion UUIDs it is MUCH less than the odds of getting struck by lightning!). Advantages of UUIDs include the fact that they are easy to set up and are very effective when used for replication. In fact, replication of databases across different servers requires the use of UUIDs to avoid collision.

They also can be generated anywhere, even offline or on the client side. With Flex2SQL v12.1, UUIDs are treated as a new row type, thus enabling UUIDs to be used as a primary key. Disadvantages include slow speed for insert-heavy users, and problems with do-it-yourself generation: if the UUIDs generated are not random enough, you may increase your chance of collision. This problem can be addressed with some specific Windows API calls. Bottom line: don’t do it yourself.

The presentation moved on to talk about index fragmentation, and specifically how this problem is more common when using UUIDs because they are inserted randomly all over an index. To alleviate this problem, use of sequential UUIDs is recommended. Oliver then talked about how to restructure an existing table using UUIDs in Flex2SQL, and demonstrated how easy it is to create a new table.
In Mertech’s product (Flex2SQL v12.1), use of UUIDs as primary keys is supported for use with MS-SQL, Oracle and PostgreSQL. MYSQL is not included in this list because the database product does not support what is needed to make dynamic primary keys function properly, i.e., UUID’s can’t be dynamically generated by the server and inserted into the table automatically as the primary key.

During the Q&A session, some important topics were discussed, including the ability, through FLEX2SQL, to generate UUIDs from the back-end using a function and to support mixed use cases (UUIDS are generated both by the server and on the client side in one database).

Overall, it was an informative session, and we look forward to the next topic.

If you want to participate in the beta, please contact Mertech Support.