Serial numbers vs. GUIDs

[wintergreen]

Every database I've ever created relies on key fields. That's nothing unusual, I expect this is standard practice for everyone on these forums.

Filemaker gives us a very easy way to generate these: by serial number. So the first record starts with 1, the next one is 2, and so on. Nothing strange there.

The problem I often have with this is when you want to do complex tasks, such as merge two independent databases together, you have to do all sorts of jiggery-pokery to ensure that every key field is indeed unique.

So today I started wondering whether it might make more sense to store a generated GUID rather than a serial number.

Does anyone have any thoughts on this?

[bcooney]

This topic comes up ever so often. To me, a GUID looks ugly. So, you'd need to give the user an InvoiceID serial anyway. That would also need to be unique, and set properly if any imports are done (such as a structure update).

I can't picture the scenario where you'd merge two independent databases together. Are you talking synchronization? If so, the best method is still SyncDek. And...you'd use their GUID SyncID as your primary keys!

Others are fans of Ray Cologon's UID.

[wintergreen]

I agree on the point of it looking ugly, however I never intend for them to be exposed anywhere... (I'm not doing anything with invoicing) Kind of like how iCal appointments each have their own UID but you never see them (unless you use applescript of course).

I was looking at Ray Cologon's method, there is also a nice variation on Brian Dunning's website .

[bcooney]

What I meant by InvoiceID, is that if you still require a transactionID for display purposes, then you're right back at managing serial ids.

[Ocean West]

You can also use 360 Works Script Master (free) to create a UUID

http://360works.com/scriptmaster/

UUID	This generates a UUID (Universally Unique Identifier). You could use UUIDs instead of auto-increment primary keys to ensure that primary keys are universally unique.



This example requires Java 5 or higher.



Read more about UUIDs at 

http://en.wikipedia.org/wiki/Uuid

Even though you create UID for key fields between tables and that is a good thing there are still interface elements that are serial numbers that users use for their business logic purposes, and thus will still require your thought process to update or reset these should you need to combine disparate tables.

[Fenton]

Ray Cologon has a nice Custom Function for UIDs also (among other goodies)

http://www.nightwing.com.au/FileMaker/demos.html

specifically:

http://www.nightwing.com.au/FileMaker/demos9/demo910.html

[wintergreen]

Thanks for all the links for ways to generates UIDs.

But what I am really interested in is what potential pitfalls there may be when using this approach.

The point about it not generating a number that can/should be displayed is a good one.

I also realised that this approach would not yield a key field that can be meaningfully sorted.

Is there anything else which can cause a problem when using this method in practice?

[Ocean West]

always trying to keep things simple it depends on the solution, in most cases I throw in the Base36 UID for good measure have been doing this for quite a while. And its fast and haven't really had any issues with any implementations.

However when testing theories and techniques or data models sometimes its easier to spot the patterns in large data sets when using a simple serial number vs UID.

When I am taking over a larger project where there is going to be several waves of imports before deployment the UID is a good idea since you can update the sample data by matching the UID - of course you could do this by other means but it just makes it a bit easier.

In some cases UID is the only approach such as implementing SyncDek as a guaranteed UID is required for synchronization.

In one project it was vital since I had 4 different tables with source data being imported to and one central table that was the combined source of the four tables in my calcs that referenced data I could concatenate all field references since only one would reference thru the relationship...

SKU ( text ) =

tableA::modelNumber & tableB::model_number & tableC::mfgNumber &tableD::partNumber

the source fields needed to be consistent with their own field name for import and export purposes. But my combined table of ALL products from the 4 different tables had one field SKU and the UID in the central table was unique in the data set regardless what table it came from.

Hope that makes sense :o

[wintergreen]

a great answer, thanks!

[comment]

I'd be more inclined to consider UID if Filemaker had a better Random function (with a seed parameter), and if I thought it could be trusted (I recall a post describing the installation of a fresh solution on several computers - which then proceeded to generate the same sequence of "random" ID's on all of them).

I believe most issues with serial numbers can be solved by using a prefix where necessary, except where you have no control (e.g. researchers in the field enlisting more researchers there).

[wintergreen]

There's another potential pitfall I've noticed with this method, which is that when using "duplicate record", it will retain the original key number.

Presumably this can be overcome by unchecking the "Do not replace existing value of field (if any)" option in the field definition, but won't doing so then invalidate the integrity of import and other commands?

Edited April 27, 2009 by Guest
clarity

[comment]

I think it may be better to restrict duplication to a scripted process.