On Sun, Aug 6, 2017 at 7:47 AM, steve simpson via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> On  Fri, 4 Aug 2017 12:52:28 -0700
> ​
> David Adams <dpad...@gmail.com>
> ​ wrote:​
> >
> > ​[snip] ​
> > 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.
> > ​ [snip]​
Sure, but only briefly (collective sigh of relief, I'm sure.)

When you design a relational database, you sort out the "keys" by finding
the field or combination of fields in a table that make the row unique. The
next part of that design is that each field in the row should be about the
row and nothing but the row. What does a UUID or longint ID have to do with
the data in the row? Nothing at all. This kind of "synthetic key" is an
implementation-level accommodation. It has nothing to do with the data,
it's just something bolted on for practical, real-world reasons. For
example, you might have a unique customer name (just saying) and they might
want to change it later. Ugh! With a unique-but-meaningless ID, that's no

I use synthetic keys all of the time and have forever. So, no quarrel with
using them for links and so on. But they do bring on another problem: These
sorts of IDs not only do not maintain the uniqueness of your rows, they
very easily obscure duplicate rows.

Imagine that you've got an asset management system and it stores the
location of each desk in a facility. There's a table for every room in the
facility. So, you've got rooms and desks. Both are finite, both are real
and tangible. So, over in your room table youve got something like

Building     Keller
Room        123

Back in the real world, the building names are unique and, within a
building, room names or numbers are unique. So, you've got a unique row
here. Imagine using that data as a key:


That's a sound key and the row design is also solid. But this kind of key
is a real pain down the row when things change. It may be that a new
numbering scheme is introduced or a building is renamed for some reason. (A
big donation, etc.) The actual collection of real-world buildings and rooms
hasn't changed (in this example), but you need to update the records to
reflect the new real-world scheme. So you have to go back and change the
[Room] record, and then any linked [Desk] records, or any other links. Ugh.

I'll be most, if not all of us, got burned by using a "real" key like this
early on. I did. So, longints and now UUIDs. Easier! So, now I'll use a

1  Keller 123

I link on 1, I can rename the building or room, no drama.

So what was I talking about? So far I'm a fan of using longints/UUIds (I
am.) The problem is this:

1  Keller 123
2  Keller 123
3  Keller 124

You've got unique *rows* but duplicated *data*. There are two Keller 123
records - but they've got different unique longints bolted on so they
aren't treated as duplicates. The "unique" IDs are masking the duplicates.
Notice that the "unique ID" has nothing at all to do with the rooms in the
world. It's just a unique number. Unique rows, duplicate data.

A UUID, longint, etc. is a practical way to link records (and the one I
use), but it then leaves the task of doing integrity checks and so on to

As I understand it, UUIDs were added to provide globally unique serial
numbers to records for the sake of journaled backups. That's a good goal,
and 4D Backups seems to work really well now.  I never upgraded my main
source from V13 because, for quite some time, it was a bit of a mess to
sort out the UUIDs and I couldn't be blethered to pour the time into
figuring out. After a few years, I found a reliable set of SQL instructions
to do a retrofit. In any case, I'm working on something now that I believe
began in V15 so it's already been sorted out there from the start.
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:4d_tech-unsubscr...@lists.4d.com

Reply via email to