Thanks for the video. Why you leave unchecked the Do not replace existing value if any? It will not make the uuid change when the record is modified and break connected relations? Specially when the uuid conects to more than 2 tables? Lets say if I create a uuid and a serial number, for invoices number. Then when I sync with a not connected DB, I will still gets same duplicate hassle. Not with the uuid but with the invoices number.
As much as we appreciate the advantages of UUIDs to cater for certain situations such as syncing various copies of a database, there are some disadvantages which ought to be noted... The first you already explained in your video - it's not as easy to visually identify records because of those long strings. But as you explained it's often sufficient to just compare the first or last 4 characters (a workaround which does work). The second disadvantage of using numeric UUIDs is that with a large database with hundreds of thousands of records (especially when there are few fields per table), these long UUID number strings will create appreciable extra file size which affects transmission times especially over the WAN. The third disadvantage is to do with indexing... Such indexes require more space within a file. They also require more time to reindex when records are added or deleted, so performance suffers somewhat with large databases. So files become even more bloated. My view is that UUIDs do offer an advantage - but only if a database needs to be made into multiple copies and such distributed copies need to be synchronised. Otherwise serial numbers are still OK. A point worth emphasising regardless of the type of UUID, is that its field definition must have the option: "Do not replace existing value of field..." UN-TICKED. The reason is to prevent the Duplicate command from making duplicate UUIDs
Thanks Ralph. I appreciate your input! Yes, there is always going to be a file size difference when you store a 58 digit value versus a 1-3 digit value. However, I would argue that if file size is the concern, then one should look at the cost and availability of storage. It shouldn't really be a concern - because storage is cheap. When you consider the cost of transfer over the WAN you're looking at only a few more bytes. It is significant when considered cumulatively? I don't know that it is relative to storage. Of all of your arguments, the only one I might consider would be the indexing time. I personally wouldn't worry about storage or transfer on key values, but possibly the amount of indexing time. However, given that I don't often re-index things, I feel that the cost of time when someone asks you to restructure your primary and foreign keys for a disconnected scenario, simply because you chose to use serial values, is just never worth it. There are SOOOOOO many other areas of a database system that can be optimized, that if you choose to use serial values on a new system over UUIDs you haven't hit that point when they do want to have disconnected systems or split a system up across two servers in order to offload for better performance. And regarding the un-checking of the "replace existing". This is something I consider optional. I can either take advantage of FileMaker's default duplicate option by un-checking it OR I can leave it checked and force myself to create my own duplication routines. Both have their merits in the systems which require the ability to duplicate. Good feedback for people who are concerned about storage to consider! (but it really is minimal in the bigger picture)
One thing I find confusing about FileMaker's UUID is why there is either type number or text as for your 'idNumber' and 'id' respectively, in my mind there should be type UUID, and as a "date field"-like formatting option, there could be either text or numeric formatting of the UUID type.
Are you referring to when a key value contains a date value embedded within it? Like this? github.com/jbante/FileMaker-Techniques/blob/master/CustomFunctions/UUID/UUIDTimestamp.fmfn
One of the hidden downsides of numeric UUID is if you export into excel, excel will truncate the number and express it in scientific notation. If you have to bounce into and out of excel, this is a non-starter.
Ah, that's a good one! I've not had to export to Excel personally, but for 90% of the systems I create I use Get ( UUID ) and not Get ( UUIDNumber ). My suggestion for most systems is to stick with just UUID.
Thanks for the video.
Why you leave unchecked the Do not replace existing value if any?
It will not make the uuid change when the record is modified and break connected relations? Specially when the uuid conects to more than 2 tables?
Lets say if I create a uuid and a serial number, for invoices number. Then when I sync with a not connected DB, I will still gets same duplicate hassle. Not with the uuid but with the invoices number.
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
As much as we appreciate the advantages of UUIDs to cater for certain situations such as syncing various copies of a database, there are some disadvantages which ought to be noted...
The first you already explained in your video - it's not as easy to visually identify records because of those long strings. But as you explained it's often sufficient to just compare the first or last 4 characters (a workaround which does work).
The second disadvantage of using numeric UUIDs is that with a large database with hundreds of thousands of records (especially when there are few fields per table), these long UUID number strings will create appreciable extra file size which affects transmission times especially over the WAN.
The third disadvantage is to do with indexing... Such indexes require more space within a file. They also require more time to reindex when records are added or deleted, so performance suffers somewhat with large databases. So files become even more bloated.
My view is that UUIDs do offer an advantage - but only if a database needs to be made into multiple copies and such distributed copies need to be synchronised. Otherwise serial numbers are still OK.
A point worth emphasising regardless of the type of UUID, is that its field definition must have the option: "Do not replace existing value of field..." UN-TICKED. The reason is to prevent the Duplicate command from making duplicate UUIDs
Thanks Ralph. I appreciate your input! Yes, there is always going to be a file size difference when you store a 58 digit value versus a 1-3 digit value. However, I would argue that if file size is the concern, then one should look at the cost and availability of storage. It shouldn't really be a concern - because storage is cheap.
When you consider the cost of transfer over the WAN you're looking at only a few more bytes. It is significant when considered cumulatively? I don't know that it is relative to storage.
Of all of your arguments, the only one I might consider would be the indexing time. I personally wouldn't worry about storage or transfer on key values, but possibly the amount of indexing time. However, given that I don't often re-index things, I feel that the cost of time when someone asks you to restructure your primary and foreign keys for a disconnected scenario, simply because you chose to use serial values, is just never worth it.
There are SOOOOOO many other areas of a database system that can be optimized, that if you choose to use serial values on a new system over UUIDs you haven't hit that point when they do want to have disconnected systems or split a system up across two servers in order to offload for better performance.
And regarding the un-checking of the "replace existing". This is something I consider optional. I can either take advantage of FileMaker's default duplicate option by un-checking it OR I can leave it checked and force myself to create my own duplication routines. Both have their merits in the systems which require the ability to duplicate.
Good feedback for people who are concerned about storage to consider! (but it really is minimal in the bigger picture)
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
One thing I find confusing about FileMaker's UUID is why there is either type number or text as for your 'idNumber' and 'id' respectively, in my mind there should be type UUID, and as a "date field"-like formatting option, there could be either text or numeric formatting of the UUID type.
Are you referring to when a key value contains a date value embedded within it? Like this? github.com/jbante/FileMaker-Techniques/blob/master/CustomFunctions/UUID/UUIDTimestamp.fmfn
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
I checked it for you, Matt: Merriam-Webster has both duplicable and duplicatable.
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
The wonders of the English language
One of the hidden downsides of numeric UUID is if you export into excel, excel will truncate the number and express it in scientific notation. If you have to bounce into and out of excel, this is a non-starter.
Ah, that's a good one! I've not had to export to Excel personally, but for 90% of the systems I create I use Get ( UUID ) and not Get ( UUIDNumber ). My suggestion for most systems is to stick with just UUID.
th-cam.com/video/YI06S-0tAoc/w-d-xo.html
First comment :)
Miss your streams, Matt
th-cam.com/video/YI06S-0tAoc/w-d-xo.html