UUID vs Longint primary key

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

UUID vs Longint primary key

4D Tech mailing list
Hi All,

Someone can explain when is better use UUID and when Longint field in
primary key?

Thanks
Ferdinando
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
> Someone can explain when is better use UUID and when Longint field in primary key?

If you can even remotely think of a scenario where you might ever be challenged to merge databases, go with a UUID!!!
It will save you incredible headaches!
Switched to UUIDs long ago, never looked back, best decision ever!

Of course if such a scenario is unthinkable, you can use longints

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
In reply to this post by 4D Tech mailing list
I can’t speak to any performance differences when using them as indexed key fields for searches … they likely have similar performance, but that’s just a guess.

But here’s one situation where UUID has an advantage.  We historically used the Sequence Number (a longint) as the primary key, until we discovered a shortcoming:  Merging data from multiple data files leads to duplicated primary keys.  With a sequence number, unless you somehow modify it (modifying the automatically-generated Sequence Number with some sort of instance prefix for the datafile) you will run into situations where different datafiles will have the same sequence numbers for various records.  That’s not a problem if those files will forever remain separate, but it requires a lot of re-sequencing of those tables and related tables if the files are ever merged or need to communicate with one another while referencing primary keys.

With UUID, it is highly unlikely (nearly impossible) that two data files will generate the same UUID for any records;  the chances are nearly 100% that all of the UUID’s will be unique, across every instance of your database.  So, if you later set up some kind of inter-database communication referencing records by primary key, or have to merge data files, you’re much less likely to run into issues.
__

Ron Rosell
President
StreamLMS


> On Aug 4, 2017, at 1:27 AM, stardata.info via 4D_Tech <[hidden email]> wrote:
>
> Hi All,
>
> Someone can explain when is better use UUID and when Longint field in primary key?
>
> Thanks
> Ferdinando
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
In reply to this post by 4D Tech mailing list
+1


MacStrass - Marcus Straßmann
Softwareentwicklung und Beratung
Auf der Markscheide 35
D-44807 Bochum

Mobil: +49 (173) 374 39 92
eMail: [hidden email]

Am 04.08.2017 um 10:39 schrieb Herr Alexander Heintz via 4D_Tech <[hidden email]>:

>> Someone can explain when is better use UUID and when Longint field in primary key?
>
> If you can even remotely think of a scenario where you might ever be challenged to merge databases, go with a UUID!!!
> It will save you incredible headaches!
> Switched to UUIDs long ago, never looked back, best decision ever!
>
> Of course if such a scenario is unthinkable, you can use longints
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
In reply to this post by 4D Tech mailing list
The UUID has been the easiest to work with.  When a customer needs a recognizable ID, an auto-incremented Longint can be added to the table.  UUIDs are perfect for avoiding conflicts when updating or exchanging records between databases.  They can also be used as Process IDs or SVG IDs when one of those is linked to a record.  All this can be accomplished with a Longint, but it's more straightforward with UUIDs.  

Keith - CDI

> On Aug 4, 2017, at 3:27 AM, stardata.info via 4D_Tech <[hidden email]> wrote:
>
> Hi All,
>
> Someone can explain when is better use UUID and when Longint field in primary key?
>
> Thanks
> Ferdinando


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
In reply to this post by 4D Tech mailing list
> I can’t speak to any performance differences when using them as indexed key fields for searches … they likely have similar performance, but that’s just a guess.

I can speak to the performance, they are not the same. UUID is faster.

4D uses BTrees (binary trees or sometimes called red/black trees) to store index structures. A sequential number is the slowest possible way to create or add to a BTree. The treee needs to remain balanced, so there is a lot of rebalancing the needs to occur when numbers are added sequentially. UUID’s are much more random and require less tree balancing and are therefore faster for delete, create operations.

I started using UUID since version 11 and love them.

--
Neil Dennis
4D Developer since 1990


GreaText - Designing Software for the Way You Work
716 East 1850 N
North Ogden, UT 84414

mailto:[hidden email]
http://www.greatext.com/


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
In reply to this post by 4D Tech mailing list
So what might be not so good with UUIDs?

After watching how people work with our systems for 26 years (most of it without UUID capability) I noticed that they often referred to the record by the Unique LongInt we created for each record. For the first year we hid our unique ID - why would they want that. Then we exposed it to the admins for a year. Then through popular request we exposed it to everyone. Then we not only exposed it but changed the name of the display of the field to JonokeMed #. Referring to record # 150, or 275,983 is much easier than referring to a long UUID.

A UUID just is not a workable solution for that - at least in my mind. Therefore our solution today is that we do use UUIDs for all records for the reason of any possibility of merging records. We have had clients move across the Canada to join up with another client, and they need the records merged. Arrh a lot of work to write that the first time, and then continue to support it with each new table.

We use the UUID as the Primary Key record, but still have a Key field. This number is exposed to the end users to use as they wish. On another note, we assign a counter ourselves (not auto generated). There are times where we reset counters and being able to do that is great.

Jody Bevan
ARGUS Productions Inc.
Developer

Argus Productions Inc. <https://www.facebook.com/ArgusProductions/>




> On Aug 4, 2017, at 7:40 AM, Keith Culotta via 4D_Tech <[hidden email]> wrote:
>
> The UUID has been the easiest to work with.  When a customer needs a recognizable ID, an auto-incremented Longint can be added to the table.  UUIDs are perfect for avoiding conflicts when updating or exchanging records between databases.  They can also be used as Process IDs or SVG IDs when one of those is linked to a record.  All this can be accomplished with a Longint, but it's more straightforward with UUIDs.  
>
> Keith - CDI
>
>> On Aug 4, 2017, at 3:27 AM, stardata.info via 4D_Tech <[hidden email]> wrote:
>>
>> Hi All,
>>
>> Someone can explain when is better use UUID and when Longint field in primary key?
>>
>> Thanks
>> Ferdinando

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
I'll add my vote to Jody's approach.

I prefer UUIDs for primary keys for all the reasons mentioned.
Having a longint index field is useful as well but for different reasons as
Jody explains. My most common use is to be able to quickly sort a table in
the order records were created. And it is superior for referencing records
- attempting to type a UUID is not something one wants to do.

On Fri, Aug 4, 2017 at 6:51 AM, Jody Bevan via 4D_Tech <[hidden email]
> wrote:

> On another note, we assign a counter ourselves (not auto generated). There
> are times where we reset counters and being able to do that is great.
>

Resetting the counter can be done with ​SET DATABASE PARAMETER ( [Table] ;
Table sequence number ; value ). I haven't had any issues with bad sequence
numbers since about vers 6.

--
Kirk Brooks
San Francisco, CA
=======================

*The only thing necessary for the triumph of evil is for good men to do
nothing.*

*- Edmund Burke*
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
In reply to this post by 4D Tech mailing list
+2

Nigel Greenlee


> On 4 Aug 2017, at 12:09, Marcus Straßmann via 4D_Tech <[hidden email]> wrote:
>
> +1
>
>
> MacStrass - Marcus Straßmann
> Softwareentwicklung und Beratung
> Auf der Markscheide 35
> D-44807 Bochum
>
> Mobil: +49 (173) 374 39 92
> eMail: [hidden email]
>
> Am 04.08.2017 um 10:39 schrieb Herr Alexander Heintz via 4D_Tech <[hidden email]>:
>
>>> Someone can explain when is better use UUID and when Longint field in primary key?
>>
>> If you can even remotely think of a scenario where you might ever be challenged to merge databases, go with a UUID!!!
>> It will save you incredible headaches!
>> Switched to UUIDs long ago, never looked back, best decision ever!
>>
>> Of course if such a scenario is unthinkable, you can use longints
>>
>> **********************************************************************
>> 4D Internet Users Group (4D iNUG)
>> FAQ:  http://lists.4d.com/faqnug.html
>> Archive:  http://lists.4d.com/archives.html
>> Options: http://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:[hidden email]
>> **********************************************************************
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
4D's UUIDs function as globally unique row *serial numbers*. That's great
for backups and convenient for physical relations, but it has exactly zero
to do with a real "primary key" or relational integrity. I would have
preferred to see UUIDs implemented as a virtual field:

[Customer].UUID()

or

Get record UUID ([Customer])

...and let 4D manage them internally, automatically, and invisibly. But
that's not how it worked out.

Yeah, UUIDs are impossible to type and I'm still not 100% clear on how you
search for empty ones. I've seen the tech tips etc., and still get some
really eerie weird results at times.

In my own code, I tend to run verification methods that check for real
(logical) keys. Namely, the field or combination of fields that uniquely
identifies the *data* in the row. A UUID-as-physical-key very easily masks
duplicate rows because it is, by definition, unique. You row might be
unique, but your data might not be, if you know what I'm saying.

Anyway, I tend to scan for duplicate data, orphan records (a child record
that *requires* a parent and doesn't have one is an orphan. Not all child
records require parents, but 4D's diagrams, etc. don't distinguish between
the two.) Oh, an widows. Mostly, I find lurking duplicates and orphans.
Widows? Not so often. Not sure if that's just me. In theory, none of these
things should ever happen. But then again, a whole lot of things work out
better in theory...
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
David,

in short, you are answering the OP's question as:

"Yes, use UUIDs in a 4D context rather than SeqNos. And, by the way, the way 4D implemented these UUIDs - I don't approve with it."

Fine :-)

Marcus


MacStrass - Marcus Straßmann
Softwareentwicklung und Beratung
Auf der Markscheide 35
D-44807 Bochum

Mobil: +49 (173) 374 39 92
eMail: [hidden email]

> Am 04.08.2017 um 21:52 schrieb David Adams via 4D_Tech <[hidden email]>:
>
> 4D's UUIDs function as globally unique row *serial numbers*. That's great
> for backups and convenient for physical relations, but it has exactly zero
> to do with a real "primary key" or relational integrity. I would have
> preferred to see UUIDs implemented as a virtual field:
>
> [Customer].UUID()
>
> or
>
> Get record UUID ([Customer])
>
> ...and let 4D manage them internally, automatically, and invisibly. But
> that's not how it worked out.
>
> Yeah, UUIDs are impossible to type and I'm still not 100% clear on how you
> search for empty ones. I've seen the tech tips etc., and still get some
> really eerie weird results at times.
>
> In my own code, I tend to run verification methods that check for real
> (logical) keys. Namely, the field or combination of fields that uniquely
> identifies the *data* in the row. A UUID-as-physical-key very easily masks
> duplicate rows because it is, by definition, unique. You row might be
> unique, but your data might not be, if you know what I'm saying.
>
> Anyway, I tend to scan for duplicate data, orphan records (a child record
> that *requires* a parent and doesn't have one is an orphan. Not all child
> records require parents, but 4D's diagrams, etc. don't distinguish between
> the two.) Oh, an widows. Mostly, I find lurking duplicates and orphans.
> Widows? Not so often. Not sure if that's just me. In theory, none of these
> things should ever happen. But then again, a whole lot of things work out
> better in theory...
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: UUID vs Longint primary key

4D Tech mailing list
> "Yes, use UUIDs in a 4D context rather than SeqNos. And, by the way, the
way 4D implemented these
> UUIDs - I don't approve with it."

Not really.

The difference between sequence numbers (your own or 4D's) and UUIDs isn't
something I addressed. Don't care. Whatever is better in your system -
conceptually they're more alike than different.

I've been saying pretty plainly that I'm happy to have UUIDs natively in 4D
and that I use them for links all the time.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Loading...