new structure for resolving site (and plot) duplicates

[teixeirak]

The duplicates issue is obviously not something we can resolve overnight, or as part of the current project. I think the most helpful thing we could do is adjust the structure of ForC and how we enter data so that it would be much easier to resolve duplicates. Defining supersites was one thing that I feel helped this, as it allows easy grouping of duplicate sites by editing one file.

For duplicate sites that can be resolved based on the publications, It's often quite easy to tell when a site is a duplicate, but resolving it requires editing so many files... It would be nice if we could come up with a system where a single edit in the sites file could effectively merge two sites. Perhaps if we move away from using the site name to link across tables, and rather use site.ID? And then allow site.ID to link to more than one row in the sites table, where one record would be given precedence over the other?

Originally posted by @teixeirak in #105 (comment)

[teixeirak]

@ValentineHerr , starting a new issue on this.

Here's an example of the kind of duplicate sites that I'd love to be able to merge automatically:

Note that I already put a note that these are confirmed duplicates that just require a merge. The time required is the reason that hasn't been done.

[ValentineHerr]

I am not a huge fan of having several site.ID in the site table. I think that it will generate quite a few issues.
But I could tackle a script that would merge those two lines (and any other that say "require merge"), keeping as much info we can, averaging when two numerical fields are entered (like mat here) and keeping both site.refs info.
Then the script would overwrite the site name in all other tables. One issue is: what do we do with plot.names then? because resolving the site name is one thing but it won't change anything with the record duplicates if the plot.names stay different.

[ValentineHerr]

I am worried about automating too many things here... I wonder if a better approach would be:

When you want to merge 2 sites, you could:

1. create/fill in a file with two columns like this:

   site.ID	site.ID_merged
   998	998
   998	2692

Left column should repeat the site that you keet in the site table as many times as site duplicates. (probably no need to repeat the on to keep like I did)
Right column lists all the site.IDs that are merged into the site in left column.

2. merge all the info from all sites into the site you keep in SITES table
3. delete all extra sites in SITES table

Then I can have a script that will replace the site.ID and sites.sitename in all other tables based on what is in this new table.
That way we will have a record of what was merged.

[teixeirak]

Hmmm.... you're right about the plots. We need to figure out how to handle that before we make any move on SITES.

It seems that duplicate sites need to be identified before we can even start on plots. However, we wouldn't want to throw out the old site names, as retaining these would help the process of sorting out plots.

Let me think about this for a bit.... (but at the moment I have to focus on a couple deadlines.)