Import (or any kind of "syncing") would need to have UNIQUE key fields. Auto-enter serial numbers must be handled with greater care if used and any kind of import or sync is required.
Text UUIDs as keys come with a greater indexing overhead than numeric keys
Can you quantify the overhead of using a text vs numeric indexed key?
Is this a noticeable effect?
Hmm.. i don't think so.
please give an example!
Filemaker stores everything as text at the end..
It's been well-documented that FileMaker indexes number fields faster than text. See related links here:
I generally prefer UUIDs, since serial numbers require careful management when importing records during upgrades. I've also never figured out a way to get them to work for a syncing solution where records can be created at both ends. (Maybe beverly has an example.)
As Kris mentions, there is additional overhead associated with the text indexes. It's generally not an issue unless you're creating / importing / deleting large numbers of records on a regular basis.
Thank you for the link, i will check this.
We develop since 2 years with UUID and for simple tables serial numbers.
With UUID we never had troubles..
ok, and as i understand you, the speed of the relations is not affected. Ok, thats perfect for me.
large deletings run over night .
It slows down any operation involving indexing. The reason is text fields have both a value index and a word index. Number fields have only the value index.
But as I said, the difference is usually a non-issue. I use UUIDs all the time, like you, and don’t generally have a problem.
If your Serial numbers only need to be unique and the records not in order enter just, use a serial number with a step of how many locations they are made from. i.e. Location1 100, 105, 110. Location2 101, 106, 111. Location3 102, 107, 112. Location4 103, 108, 114. Location5 104, 109, 114. Step of 5. Now a little math with a some location info could be done to keep from having to make a unique copy for each location. May not be the best way but it can work.
The record ID should not be visible and used as search string, at least that's what theory preaches. Makes sense.
But when you have tables with millions of records and users do need to search for a case number, it might be a good idea to forget about theory and keep in mind the downtime caused by FMS verifying a database after a crash while 40 users can't work, just because you have 2 indexed keys instead of one and there's a couple of GB data more to inspect and verify for consistency . If you know you're gonna hit such magnitude, just use a numeric key and forget about UUID. And last but not least, users won't have to enter a UUID in their search field - just a long digit.
My 2 cents, as usual.
Agree with beverly . UUIDs are "guaranteed" unique. If you're sharing data or importing other data where imported serial numbers could cause "collisions" (be the same as existing serial numbers), I'd opt for UUIDs. If they're a bit slower to generate, index, or import, who cares? You need to have data integrity first and foremost.
As an aside.....I would be careful using "Natural Keys" (combination of fields from a record) as a unique key. Although natural keys can be OK, the business could change, fields could change, or any number of other things to make the current "natural key" no longer useful/correct or even ... unique. This caution might not apply as much to FM since I don't think you have control over creating a named multi-field index as with other database environments.
HOPE THIS HELPS.
"The reason is text fields have both a value index and a word index."
Only if that is how you actively choose to set them up. They can be set up with only value indexes.
Yes, this is true. Of course, FileMaker will create each of those indexes when needed unless you explicitly tell it otherwise
Firstly, many thanks for your replies, they are as always extremely helpful. My reason for asking is I am working on an a database that I believe will have a number of versions, (clients cannot wait till it's the done deal can they!).
It will unfortunately mean a lot of data exports and imports from existing version to new version(s).
It's multi-file, multi table and will grow in complexity. Although the numbers of records will not be large - tens of thousands, not hundreds of thousands, the thought of trying to keep track of serial numbers on data imports is pretty daunting.
Also, as much of the data itself is coming in from external sources (Suppliers' Product ids, other apps etc.) that can range from 20 digit alpha-numeric codes to, brilliant ids like '01', then the chances of id clashes is pretty large.
Therefore, I am intending on using UUIDs on the internal FM relationships without displaying them and 'external' supplied ids for Users to search on as that's what they are used to seeing.
Thank you once again. I am very proud that the big-hitters here have chosen to respond to what may have been a pretty obvious question.