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 problem. 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: Keller123 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 longint: 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 you. 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 **********************************************************************
