Possible tip for meaningful keys

[Joseph_Seddik]

Dear community,

As I see it, keys do not and should not only serve the purpose of identifying individual records of your database for the sake of the machine, rather they should also serve the human data master in identifying and navigating through his database.

While individual data has no inherent significance for the machine; only their interrelationships matter, the same individual data is meaningful to the human and he can turn it into information.

Speaking about UNIQUEID(), I think it should not be used as the preferred option by default for key column values, rather it should only be used in the cases where no meaningful key can be crafted or to complement a meaningful key, as an additional security measure.

This is a huge topic with a lot to be said about it, so I’ll just write down two AppSheet examples, just to give an idea, but the possibilities are numerous.

1. Finding the most recent row in a table:

You’ll be better off not using for example MAX() or MAXROW() coupled with a SELECT() or equivalent to find the last row and extract a value form it, which “could impact performance” and in some cases you depend on the table’s row number which is unreliable.

Instead, when your keys begin with for example “yymmddHHMMSS” all you have to do is a simple first value extraction from a list and you have the key to the last row handy.

2. Dereferencing

• When you have a table with rarely updated information, like a table of Services, never use random keys. Use your own meaningful keys. For example: Serv-CAR-01-tireFix, Serv-CAR02-tireReplace, Serv-CAR-11-oilChange and Serv-CAR-12-oilFilter.

• When you have a Clients list, use one of his unique PIIs as a key, like his ID number or his email.

• When your Client sign a service order, you can use such a key for this SO: "yymmddHHMMSS:[serviceID]:[clientID]

• You can construct the key of a Payment for a SO like: [serviceOrderID]:[cashRegisterID]

• and so on.

Can you see the amount of information you have just by looking at the keys? Now imagine the possibilities you have to make your expressions simpler and your app much faster.