From Serial Keys to UUIDs... how does it change your schema.

I am embracing the UUID. Ive read many repetitious articles about the major reasons to change. I know I can just replace a serial value with a UUID and do everything else the same but I am trying to leverage the concept in other ways.

I am looking how it might effect other things. I am using a custom function that gets a value from the creation device, a time stamp and a serialized value of the second value of the timestamp. I figure the same device can not create more then one record at any instant so I will assume that every uuid in my DB is unique even across tables. Do you take this approach?

What are some of the ways it has effected your schema?

I realized today that any one to one relationship can just use the PK (uuid) of its parent as its own key. even if those things are connected to multiple tables.

for example, a table for something like barcodes needs 2 fields. id and valueBarcode. I can create a 1 to 1 relationship from a barcode to any table in my file and set its id value to the UUID of its parent. I can use this to check for barcode value uniqueness when it is created and I dont need to create a PK and separate FKs for any table that it connects to because every id is unique.

I can do the same thing for a container table. I can check the MD5 hash of a container for uniqueness to make sure my users are storing the same document in multiple places.

another example I am considering follows:

I currently have a task table that is connected to most other tables through a series of field like this:

id

id_purchaseOrder

id_contactid_billid_equipmentList

and about 10 more.

This allows a user to make themselves a task about anything is a base table that makes sense.

The main task table has a TO for every task type so my TOs are listed as

TASK

task_purchaseOrder

task_contact

task_bill

and so on....

A an auto enter for the calc name looks like

case(

task::id_purchaseOrder ; task_purchaseOrder::displayName ;

task::id_contact ; task_contact::displayName

and im sick of typing this. you get the idea.

A calc on a task table that put all the same Fks in the same field would have to evaluate if the relationship is valid for each of those items to auto calc a display value for the task. does checking isValid on an empty related value take an equal amount of time as checking the fields in the same record for a value and only looking for a related record once that is found? To me, that would be a reason to not store all the FKs in one field in a table that is one to many.

My primary goal is performance over the WAN so I want to leverage any little savings that I can. But on this one, I have a feeling its a wash and the ease of setting the same field in the task table from anywhere might make it worth it.

I still get nervous about trusting the uniqueness and letting go of serial values. Extensive testing as showed me that ive got noting to worry about. I even stopped checking my UUIDs for uniqueness because it doesn't check it across the whole DB so y bother adding the validation overhead.

sorry for all the poetic waxing about a new-to-me technique but its just got me thinking. are they any other ways to leverage this global uniqueness?

I still get nervous about trusting the uniqueness and letting go of serial values. Extensive testing as showed me that ive got noting to worry about. I even stopped checking my UUIDs for uniqueness because it doesn't check it across the whole DB so y bother adding the validation overhead.

Well, the serial relies on being unique, but can be problematic with the import (add or update) or delete of records (no longer serial, for example).

I have also not found this to be the problem with UUID. But I'm also nervous and will: make backup of backup, import or delete, check that UUID really is unique, breathe.

Here are two ways that I've used UUID's that don't work well with serial numbers:

In modifying a legacy system with 100's of files with many tables each, I had an "email table" where each email sent out from the solution had a record with subject, body, time/date sent, etc and I wanted to link the email record to the record that was current at the time the email was sent even though that "current record" could be from any number of tables from any number of files. So I set up the script that generates the email record via a perform script call to:

a) Set a field in the current record to Get ( UUID ).

b) pass this value in the script parameter to the script that creates the email record (from global fields that have body, subject, recipient info).

c) modified this "create the email record and send it" script to parse the UUID out of the script parameter and set a field in the email table to this same UUID value

I now had a way to link a "parent record" from any number of different tables in the solution to records in the same "child table".

In similar fashion, I've linked a general "notes table" to multiple parent tables from which the user needed to record and view notes.

In both cases, this worked because the UUID was unique not only within the table where it was assigned to a record but unique across the entire solution.

One reason to use a custom function that generates a numeric UUID is that you can then store the result in a Number field instead of in a Text field. For large tables (millions of records), that takes less storage space, and indexing is faster and also takes less space. It even seems that searches perform better. Part of the problem is that the Get ( UUID ) function returns a text representation of a hexadecimal number. So, it looks hexadecimal, but because FileMaker doesn't have Hexadecimal Number as a field type, it has to be stored as Text. In other words, even though the UUID can only include the character set 0-9, A-F (and dashes for formatting) essentially representing a number, it is being stored in a data format that supports a MUCH larger character set, adding quite a bit of overhead.

Some of the custom functions available can also produce UUIDs that are still unique, but sort in creation order, which can be a useful feature, if needed.

Here's a great article on the pros/cons of various UUID techniques: Key values - Best Practices - FileMaker Coding Standards - every FileMaker developer should read through this and know what their options are. Make sure to read the discussion at the end of that article, noting that it covers a period of time in which the Get ( UUID ) function became available, and discusses its advantages/disadvantages.

Yes, I am aware of this, but note the method being used to calculate the UUID here is not a case of taking the UUID value and converting it into a number--which is the method that I've seen used for this. Thus, I am asking Eric why he is using what appears to be a more complex method for getting a UUID. I like my primary keys generated in as simple and fool proof a method possible--this is why serial numbers still have their uses as long as the developer respects their limitations.

I think there may still be reasons to use a more involved custom function instead of just making a custom function that converts the "hexadecimal" representation created by Get ( UUID ) into the equivalent decimal number. But, it's a good question for Eric on whether he needs anything other than just using the built-in Get ( UUID ) in Text fields. As you said, simple, straight-forward built-in feature can weigh in favor of using it directly.

Here's what Jeremy Bante says in the thread I linked to above:

Converting Get ( UUID ) to a decimal number is relatively slow, but the result is a couple digits shorter than my own UUID functions generate, and is therefore a little better for find speed and file size.

There are pros/cons to using one of these:

Get ( UUID ), or

Get ( UUID ) but converting to an equivalent decimal number, or

a separate custom function that generates a totally random decimal number UUID, or

a separate custom function that generates a sorts-in-creation-order decimal number UUID.

That thread is a great place to see the thinking that goes into which a developer should pick.

My team is mostly using the UUIDTimeDevice function by Jeremy Bante, which generates a numeric value that sorts in creation order, unless we need a truly random value (so the key doesn't give away any metadata), in which case we use the similar UUIDRandom. We take the small hit at record-creation time to have numeric primary keys for better performance in searching large tables. BUT, when possible we still use serial numbers for primary keys in tables where there will only be a standard method for creation, as that is fastest and still works well for many situations.

BUT, when possible we still use serial numbers for primary keys in tables where there will only be a standard method for creation, as that is fastest and still works well for many situations.

Here here!

I totally agree with that statement. If at all possible, primary keys should be:

Unique within the table that they are created (but universally unique when needed)

Devoid of any "value" encoded for use by the user (abbreviated user names, company or product names etc.)

Never, ever changed once assigned to a new record

Implemented in as simple and in as bullet proof a fashion as possible.

Serial numbers shine on #4 not so much on #1 as they need careful management of the "next value" settings during damaged file recovery and version upgrade driven imports. But I'll put up with the need for that in return for faster performance and simple implementation. Of course there are times when you need some kind of UUID--such as when synching from multiple devices or other cases where you need the value to be unique across multiple tables or multiple copies of the same file.