LinkedIn
Twitter
This is where your creativity; the most important aspect of a developer, should kick in 
Outside AppSheet I used to add milliseconds which bring a level of practical uniqueness that Iโve never seen broken in more than 20 years of managing M2M events.
Here, in my app I added userID that cannot do more than one action at a time or cashRegisterID that could not be used by more than one person at a time.
Sorry, I donโt understand. Would you please explain?
I would never show keys to end users. Although user experience is not my domain of expertise I do believe this is bad practice. Keys are for the developer only.
If by โusersโ you mean people who collaborate with you in app development, then while you might have different mother tongues, you must have a common language of communication for your collaboration to be possible.
At the end, it all pours down to being creative. At the same time I would never claim that there would be a solution for anything in life that would be suitable in ALL cases. Personal preference for me has always been a valid case
I do understand why you bring up your points. However, you probably donโt want to try to re-invent this wheel. DATALESS keys are an industry standard arrived at through decades of experience and numerous headaches caused by businessโ that were shut down for days looking for the source of a duplicate key and/or while the datasource was restructured due to the need to support a duplicate DATA value that was used as a key. Itโs not an exaggeration. I have been personally involved in a couple of these projects.
To give you one simple REAL WORLD example (there are many). Letโs say there is a Products table and each product has a Product Code. It is tempting to use this Product Code as the row key in the Products table - its unique right?
Well, the business begins using this Product Code as an easy way to identify, and discuss, the product. It becomes a meaningful data value.
Then one day, the business gets a NEW version of a product that replaces the original one. Its different enough that it needs a whole new entry in the Products table BUT the business wants it to use the SAME PRODUCT CODE because that is how all their literature and web sites are created. Itโs too expensive to change all that.
The problem? There are already Orders and other rows throughout the system that are referring to the OLD Product by this Product Code as they key. Updating the Product row in the table will point those OLD orders to the NEW version of the Product. Not good! There needs to be a way to have two versions of a Product (old and new) using the same Product Code.
The answer? With a dataless key I can enter another Product row with the same Product Code and it will not affect any of the existing system data. They will continue to refer to the OLD dataless key. Any new entries will use the NEW dataless key to point to the NEW version of the product.
Now, having said all that, most of the AppSheet apps are simple and can get by using โmeaningful data valuesโ, probably for many years without any issues. But I am here to tell you, if the app grows and you encounter a situation like I described above or decide you DO need to update how you are using Keys, it can become a major, MAJOR headache. Keys are like the foundation in a building, they are rooted deeply in the system and messing with them can cause major problems for a business. It is best to use them the right way so that you never have to touch them again. Dataless keys are that method.
Here is a quick example of why DateTime should not be used as a key. The image below shows a view of Individual rows, created by separate actions, being assigned the SAME DateTime. The actions are in a Group attached to a Form Saved Behavior.
The point, regardless of how or why it happens, it is possible for different rows to get the same DateTime value. It is not a good key value source.
Grouped Action creating 4 child rows with a DateTime assigned to NOW() - All get same DateTime
Hello John,
Thanks much for taking the time to reply in such detail 
First let me tell you that I agree, in principle, to your point of view. Additionally, there are some points Iโd like to clarify please.
You are right. Country codes are not a good choice. Thank you.
The most important is: it is not about the size of data, rather than making sure record keys are and remain unique throughout the tableโs life time.
Actually, I believe we should, when thereโs a foreseen opportunity for improvement, we should, otherwise we would have still stuck with wooden wheels instead of tires. At the end my post Iโll give a real-world example.
I agree, and with the example you mentioned youโve clearly demonstrated how this is a bad practice and how this was a bad decision.
However, as I had mentioned, you can still use a meaningful key and ensure its uniqueness by combining meaningful data with a pseudorandom code. So, instead of having a key such as 8870ba24, you can have USB-FM-128G-8870ba24.
Then, when you have a new version of the same flash memory that is faster and you want to sell it at a higher price, you can have a new key USB-FM-128G-03d91e47. That is [productCode]:UNIQUEID(), instead of just [productCode] (big mistake) or just UNIQUEID().
One second is a large span, of course you can a lot of records written in the same second. As far as AppSheet is concerned, I said the key would begin with the timestamp, NOT that the time stamp should constitute the key. The other components of the key would ensure its uniqueness and you can still attach a UniqueID() to the key.
Away from AppSheet, below is some account that some might find interesting:
Two cases of writing to local disk
In more than twenty years of handling M2M transactions, using milliseconds in keys have proven to be of practical uniqueness that Iโve not seen broken. Let me begin with an example.
This is a script I ran on my laptop to see how many times I can write to a file during one second.
% open tempFile w
file5
% open /etc/hosts r
file6
% set chunk [read file6]
...
%. set i 0; set j 0; set count 0
0
% while {[expr $i - $j] < 1000} {
if {$j == 0} {set j [clock milliseconds]; puts "\n\nINIT: $j"}
set i [clock milliseconds]
puts file5 $chunk
incr count
}; close file6; close file5; puts "END: $i"; puts "COUNT: $count"
INIT: 1642492698642
END: 1642492699642
COUNT: 97749
This is the resulting file:
-rw-r--r-- 1 kjseddik staff 209867103 Jan 18 08:58 tempFile
So, during one second, I was able to perform 97,7K write operation into a file on my local disk.
I ran the same test again, writing to my local disk, but instead of writing directly to the file, I wrote a simple server and a simple client on the same computer with localhost as server address. Then I ran the script and wrote from the client, through the socket, to the server, which are both the same computer. Here are the results:
INIT: 1642521861842
END: 1642521862856
COUNT: 3968
and here is the output file:
-rw-r--r-- 1 kjseddik staff 8507392 Jan 18 17:04 serverTempFile
This time, instead of 97,7K iterations, I was able to write 3,9K times only during one second; 20 times less!
Although the data didnโt leave the computer, in the second test, the write operations had to be queued at the network interface even when they were not supposed to leave the computer. Queuing is an intrinsic characteristic of network interfaces. Additionally, you need a couple of milliseconds (do a ping) just to reach your immediate local router directly connected to your PC, a single hop, not to mention the many hops your data have to cross to reach a remote database server.
Natural queuing and path delays in networks make it extremely hard to encounter two operations writing to the same server in the exact milliseconds, thatโs what makes Timestamps good for database keys especially that youโll never use them standalone for the key, but combine them with other meaningful data and, why not a pseudorandom number.
Also a bonus part, hereโs an actual case where meaningful keys where immensely helpful.
IPv4 Depletion Rescue Plan
Several years ago I was recruited to manage a network transformation program for a major telecom operator. This operator entered the local market through the acquisition of five smaller local operators, each of them with its own network and systems.
The operator used to assign public IPv4 addresses to end customers, so we were rapidly faced with the problem of IPv4 depletion and needed to recover more than four million IPv4 addresses quickly (replacing them with private addresses) or face a complete business halt. The problem was compounded by another rapidly advancing project to deploy FTTH capturing new customers at a rapid rate and pushed also by planned DSL obsolescence so we needed to move all our DSL customers to Fiber also rapidly. Each new Fiber connection consumed three public IPv4 addresses, while liberation of addresses used by DSL could not be done at the same speed. You have to phase out a complete NAS to recover its subnets.
Additionally, there were numerous small projects running everywhere that were not consuming new addresses but moving them continuously from one place to another. Business requirements made it not possible to impose a network freeze for more than three days, so you always have to be acting fast.
Management brought in one of the big consultancy firms to help. Their findings that we needed to have a huge database tracking the locations of allocated addresses and changes through time and use projections to predict where we need to act first to have first gains and what subnets we can recover, where and when to impose network freezes in a way that do not impact business.
They told us we need to install our own SQL Server, that theyโll be managing for us, and they would be providing the network probes that would feed the SQL database. The problem is their planning was not the best, we needed to act faster, and the cost they asked for was crazily exorbitant! I remember the CTO calling me in his office and telling me: โyou have to make do with what you haveโ.
We had our own in-house-produced network probes, too many of them but unfortunately non redundant. These were not designed to provide input for a database, and across the engineering teams we had little database expertise. We also couldnโt afford waiting for the consultants to develop new probes training them in the process, just for the purpose of performing a network audit; that wouldโve bee reinventing the wheel. We needed to act fast and produce reliable result.
Eventually, we decided to make use of our own many-but-reliable probes and create a custom database to combine their output into meaningful information. This is what we did. We wrote intermediate scripts to feed a database that has to be as simple as possible (due to cost and also scarce db expertise), so that we know how easily extract useful information from it.
We ended having a single SQLite file to control the whole IPv4 space (public and private) across the five networks. Each running project had a table or more in that database.
Our Key format was a MAJOR factor in the simplicity of this database, and the ease with which we can write to and extract data from. We opted for meaningful keys combining:
- IPv4 address
- Current Interface MAC address (indicating current location)
- Routing protocol (BGP) information
- Date of first activation on current interface
Iโm sure that the consultancy firm presented to us the best Industry Standards and Best Practices. But had we not invented the wheel we wouldnโt have been able to deliver within the time and budget constraints we had.
P.S. A couple of years later, our reinvented-wheel SQLite database, was the corner stone in IPv6 Transformation program, that Iโve been privileged to lead.
