service_category table: Linked Taxonomy Terms and IDs

[klambacher]

Regarding the service_taxonomy table, the taxonomy_details field describes allowing linked Terms (two terms used together to make a compound term) but still implies a single Taxonomy ID from the service_taxonomy table. This isn't realistic. I understand the desire not to add another depth to this, especially for those not using this type of linked structure...but as it stands this structure isn't workable for those doing linking. It's not even workable to use the ID of one of the Terms to establish a relationship and imply the others through the detail, since the same Term might be linked in multiple ways in the same record. It is also possible for people to vary the detail but want to match on ID (Code) for integrity purposes, so all IDs must be present. At minimum, it seems crucial to allow linked IDs by permitting delimited IDs here. Unfortunately, this makes it hard to describe relationships and enforce relational integrity.

[timgdavies]

So - I think the design principle here was that:

• A system that does not understand the taxonomy_detail field, and the dependencies, can simply return results whenever a term is present;
• A system that does understand taxonomy_detail can provide a more nuanced response;

So, if you have a system that understands the compound term 'ABC*DEF', this would be represented in the table as:

id	service_id	taxonomy_id	taxonomy_detail
1	1234	ABC	ABC*DEF
2	1234	DEF	ABC*DEF

So - from the perspective of the system that understands taxonomy_detail there is some redundancy here, but for the other system that ignores that there is reasonable content.

Was this your understanding as set out above?

The main problem I can see is around updating records, as if you have a system that internally holds 'ABC*DEF' then needs to split that out into two rows for 'ABC' and 'DEF', keeping track of the ids etc. given to these extra rows would involve some overhead.

But I think you might have been getting at a different problem as well?

Any ideas on a good solution to this one?

[klambacher]

No, this is not how linked Terms work. Linked Terms allow the combination two terms to create a new Term with its own meaning, a compound Term. I'm grabbing some examples of categories out of a single service record in a client database (links separated by ~):

Clothing Donation Programs ; Disaster Recovery Services ~ Refugees ~ Syrian Community ; Specialized Information and Referral ~ Refugees ~ Syrian Community ; Sponsorship Programs ~ Refugees ~ Syrian Community

So here you see the Syrian Community and Refugees Terms appears multiple times for this service, but it is linked in different ways. Ping me in the OR slack if you'd like to understand how this works better, I can walk you through it. But what you propose above is not an accurate way to capture this information.

It's also worth noting that IDs are highly meaningful in the AIRS Taxonomy, and are better and more accurate for data exchange purposes than using Term names, since Term names can change subtly over time but the Code indicates the same underlying Term is being used. Most systems using this Taxonomy will ignore the Term Names and depend entirely on the Codes.