David and Chuck, point taken about "rows" being unique because of and
created with the containing data of each row -- up to a ... well "a point".
Like you said, a real pain when real world reality steps in. I believe the
only truism involved here is your statement that "...
t then leaves the task of doing integrity checks and so on to you.
" Exactly! Regardless of the way you decide to handle 4D record
uniqueness, you will surely encounter the ongoing need to continually
manage integrity "on your own". Hoping that your "unique key" will do that
for you is likely not realistic, regardless of your method.
I'm sure that many of us have had to eventually abandon the 4D long int
"auto increment" in favor of some sort of crafted version of it when we
expanded to mixing data sources. For instance, you build and distribute an
application that starts up out the gate with recordID #1, then #2, then #3,
etc with each install. But 5 years later the client wants everyone - all 4
thousand installs - to start sending data to a central accounting
processing point -- retaining uniqueness across the enterprise. Oooops.
Suddenly, you have to make sure that each and every installation has some
sort of retrofitted unique number, something not envisioned when it was
first developed. Whoa. Big problem.
I must say that the only "real" solution to this so far in the 4D world -
for me at least - has been the auto UUID key field. Finally a way to create
a record (and its children) that can be moved around willy-nilly without
angst. OK, granted it does not solve the issue of "integrity" from row to
row. But neither does your own example of finding uniqueness within each
row's actual data (i.e.
Keller123, etc); that is a task you have to deal with regardless of how you
handle your own unique key - logical ways - important realistic vertical
market biz logic ways -- issues that may be unique to each project -- logic
that may change over time. It has NOTHING to do with the record key in of
of itself. Chuck Miller said in response, "...
I am not David but I agree with his assessment. Relational model databases
by definition are not supposed to use keys that have no meaning. They are
supposed to create relations that have meaning....". All I can say is that
"meaning" per row, and meaning contained within children rows -- especially
meaning to "humans" -- may not really exist and may have little to cling to
or may even change as projects expand and morph.
I'm really just reacting to what I experience as "theory" mismatch with
real world reality. We need to separate "record uniqueness" as an idea from
"meaning" of the data within a row, since no matter how you handle record
uniqueness you will still need to craft your own unique project needs to
insure "data integrity", which can take many forms and have many challenges
beyond some simple "row" logic. And realize that these needs will likely
change over time as the project morphs. So the first need is to handle "4D
record ID uniqueness". (and in my experience so far, ONLY UUIDs do that
satisfactorily.) Then address your project specific "data integrity" needs
and understand that you'll have to deal with that on an ongoing basis
regardless of how you handle record uniqueness as your app grows and morphs
and expands and begins to interact with other outside data sources.
And BTW, we all have users who get a lot of info from the visual feedback
of incremental "record numbers". OK, no problem. Just another field:
"record number", far different from "unique internal record KEYID". And
likely understandable and fixable for human needs if dupes are introduced
with data mergers. Just another "user comprehension data integrity" task.
On Sun, Aug 6, 2017 at 3:00 PM, <4d_tech-requ...@lists.4d.com> wrote:
> Message: 3
> Date: Sun, 6 Aug 2017 11:03:57 -0400
> From: Chuck Miller <cjmil...@informed-solutions.com>
> To: 4D iNug Technical <email@example.com>
> Subject: Re:
> Re: UUID vs Longint primary key
> I am not David but I agree with his assessment. Relational model databases
> by definition are not supposed to use keys that have no meaning. They are
> supposed to create relations that have meaning. Even the use of numbers
> breaks the theoretical rule. The problem is that we have pushed all to use
> relational model rather that what he logical model proposes. Who out there
> remembers hierarchical, tree, or inverted structure models.
> Message: 4
> Date: Sun, 6 Aug 2017 08:16:07 -0700
> From: David Adams <dpad...@gmail.com>
> To: 4D iNug Technical <firstname.lastname@example.org>
> Subject: Re: Re: UUID vs Longint primary key
> 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.
> 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
> but it then leaves the task of doing integrity checks and so on to
4D Internet Users Group (4D iNUG)