Persistent IDs and autoincrement

[petermr]

It's critical that every record has a uniqueID. For example when we submit to Pubchem the records may come back in a different order. So they must have a key. Currently we have an autoincrement ID in every table.

Although Autoincrement IDs are simple they can cause problems:
https://medium.com/@Mareks_082/auto-increment-keys-vs-uuid-a74d81f7476a

https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/

Not all these criticisms are relevant to us (at least not yet).
The main reason that I'me worried about autoincrement is the likelihood of pure numbers being confused between tables especially while we are refactoring.

Pubchem uses bare numbers. Wikidata manages with Qddddd and Pdddd ids and I think we can do the same. We can still autoincrement BUT we must have discipline.

We can NEVER NEVER NEVER reuse an id. If we delete a record we simply have an obsoleted record.

Most identifiers WILL be used outside the database. I think we should keep them short, distinct from Wikidata and Pubchem.

Possible:
PLdd* for plants
Cdd* for chemicals
Bdd* for bibliography
Edd* for experiments
PRdd* for profiles

BUT we have to have a system for checking uniqueness when new data is entered.

One of our concerns is change of personnel so the system MUST be documented.