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.
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.
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.