Hi Charles

I’ve managed to have a very quick look at this.

As I understand it, I would expect there to be more than one recommended_taxon_list_item_key here – one for every list this species occurs on.

There also appears to be some aggregation occurring too. For example, within the list of recommended_taxon_list_keys for Pipistrellus pipistrellus there is a key for Chiroptera.

Run the following query to see what I mean:

select TLI.taxon_list_item_key, TLI.Taxon_version_key, TL.Item_name, TLV.Taxon_list_version_key, T.Item_name, t.authority
from taxon_list_item TLI
inner join Taxon_version TV
on TLI.Taxon_Version_key = TV.Taxon_version_key
inner join Taxon T
on TV.Taxon_key = T.taxon_key
inner join taxon_list_version TLV
on TLI.taxon_list_version_key = TLV.taxon_list_version_key
inner join Taxon_List TL
on TLV.Taxon_list_key = TL.taxon_list_key
where TLI.taxon_list_item_key in
(
select distinct recommended_taxon_list_item_key as taxon_list_item_key
from index_taxon_name
where preferred_name = 'Pipistrellus pipistrellus'
)

Having said that, there does appear to be some duplication, for example there are three recommended_taxon_list_item_keys for the ‘JNCC Interpretation of Higher Level Designations’ list which look identical.

However, I am at the edge of my knowledge on the species dictionary here so I’ll need speak to Steve and get back to you.

It is also worth bearing in mind that the new version of Recorder 6 (for example when producing reports in the report wizard) with use the Nameserver table in the first instance for synonymy, and then the Index_Taxon_Name table if an entry does not yet exist in this table.

Hope this makes some sense!

Kind regards,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Yes, that's right, as I understand it, the whole point of recommended_taxon_list_item_key is that there should be only one for each taxon.

Charles
Looks odd (and wrong). I have run the same query here and while I get some diiferent entries it is nothing like the number that you have.
Can I check what version of Recorder you are using and what state your database is in? It may be that we need to run a rebuild of your dictiionary.
Steve

Thanks Brian.
ANy chance one of you could send Sarah a copy of your NameServer table and Taxon_list table?
Many thanks
Steve

I have just sent the two tables also.

Brian

Hi Charles and Brian

Just to round things off, this problem was caused by the JNCC Higher Designations List.

The Preferred_Name field for this list has been incorrectly populated in the past and has been used to point a name (e.g. Chiroptera) to all the constituent species (including Pipistrellus pipistrellus).

The Preferred_Name field in the Index_Taxon_Name table is populated from the list the original Taxon_List_Item_key is on. The Recommended_Taxon_List_Item_Key is populated separately using Nameserver primarily.

Therefore, it is quite possible that the query that Charles originally ran will return multiple rows IF a list is badly synonymised.

It also means that the Preferred_Name and the Recommended_Taxon_List_Item_key in the Index_Taxon_Name table will not always marry up.

This is how this table was intended to work and there’s little scope for changing this without creating problems with Recorder.

The most likely solution will be to remove the JNCC Higher Designations list as part of the next dictionary update. The next update will also include improvements to the Nameserver table which should also improve synonymy generally.

Hope this makes (some) sense!

Kind regards,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Hi Brian

My apologies - for some reason I'd missed this post.

I think it is unlikely that many people will have recorded against the Higher Designations List (with it being a status list), however, if they have, the records will be repointed to exactly the same name on another list (a preferred list if the same name occurs on one, if not another non-status list). Not something we do lightly but the only solution here.

Best wishes,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC