How to mimic the concept of "migrations" script in opinions.pm

[aazaff]

SCENARIO 1

I create a new taxon named Virgiana brandnewus as a species of genus Virgiana. The new species was then reflected in the Authorities table and its relationship to Virgiana in the Opinions table.

SELECT taxon_no, taxon_name FROM authorities WHERE taxon_name = 'Virgiana brandnewus';

taxon_no	taxon_name
497316	Virgiana brandnewus

SELECT child_no, child_spelling_no, status, parent_no, parent_spelling_no FROM opinions WHERE parent_no = '497316' OR child_no = '497316';

child_no	child_spelling_no	status	parent_no	parent_spelling_no
497316	497316	belongs to	28329	28329

The above states that taxon_no 497316 (Virgiana brandnewus) belongs to taxon_no 28329 (Virgiana).

I next added an additional opinion that Virgiana brandnewus is an objective synonym of Virgiana falseii (taxon_no 497315), which added a new line to the Opinions table.

SELECT child_no, child_spelling_no, status, parent_no, parent_spelling_no FROM opinions WHERE parent_no = '497316' OR child_no = '497316';

child_no	child_spelling_no	status	parent_no	parent_spelling_no
497316	497316	belongs to	28329	28329
497316	497316	subjective synonym of	497315	497315

All of this follows logically from our understanding of Opinions, but there seems to be a completely different behavior for status = Misspellings. In this case, it seems they overwrite the old child_no, a process that they refer to as migration. I tested this by adding a new opinion that Virgiana brandnewus is a misspelling of Virgiana falseii.

SELECT child_no, child_spelling_no, status, parent_no, parent_spelling_no FROM opinions WHERE parent_spelling_no = '497316' OR child_spelling_no = '497316';

child_no	child_spelling_no	status	parent_no	parent_spelling_no
497315	497316	belongs to	28329	28329
497315	497316	subjective synonym of	497315	497315
497315	497316	misspelling of	497315	497315

Notice that the child_no of ALL opinions for taxon 497316 was retroactively changed to 497315. The only record that these opinions had a different taxon_no originally is the child_spelling_no field.

[aazaff]

When I have time later, I will see if there is any kind of rollback option? For example, what if there is a later opinion that it was not a misspelling?

[NoisyFlowers]

One would think the implementation should be that once an opinion is recorded, it is never changed; that subsequent opinion records simply supersede it.

All you would then need is the child and parent fk to authorities and the status column to discern the type of parent/child relationship. There might also be a need for a supersedes key into the same table, but maybe the created column (date) would be enough to trace opinion lineage.

(I know this isn't relevant to our efforts right now. Just mentioning it here for reference later.)

[aazaff]

It seems to be impossible to undo an opinion that something is a mispelling.

[aazaff]

SCENARIO II

TBD -- what are the protections when attempting to synonymize two taxa with different parents (this shouldn't be allowed because it doesn't make sense).