ERM - OpenAccess derived table for checklists 'openaccess_checklists'

Content

1 Content
2 Overview
3 Github Issue
4 Attributes for the derived table
5 Linking approach
6 Explanation to special relations between tables
- 6.1 Relation between the derived tables "openaccess_publication_request" and "openaccess_checklists"

Overview

The purpose of this derived table is to show the checklist items to an Open Access publication requests.

Github Issue

https://github.com/folio-org/folio-analytics/issues/794 (ERM - Create derived table 'openaccess_checklists' for Metadb)

Attributes for the derived table

Folio Table	Folio Attribute	Description	Datatype	Sample Output	FK (From table)	PK (To table)	Notes

Folio Table	Folio Attribute	Description	Datatype	Sample Output	FK (From table)	PK (To table)	Notes
folio_oa.checklist_item	cli_id	UUID of the checklist item	UUID	f3f357f4-fdd4-40da-9133-bd2e87ca74ee
folio_oa.checklist_item	cli_version		INTEGER	0
folio_oa.checklist_item	cli_date_created	Timestamp when the checklist item was created	TIMESTAMP	2023-03-10 12:54:19.659
folio_oa.checklist_item	cli_last_updated	Timestamp when the checklist item was updated	TIMESTAMP	2023-03-10 12:54:19.659
folio_oa.checklist_item	cli_definition_fk	UUID reference to folio_oa.checklist_item_definition	UUID	da97441a-a990-4320-b3a5-56782dfa3ed7	Table: folio_oa.checklist_item Attribute: cli_definition_fk	Table: folio_oa.checklist_item_definition Attribute: clid_id
folio_oa.checklist_item_definition	cli_definition_value	Value of the definition for a checklist item	VARCHAR	nachwuchswissenschaftlerin			Original attribute name: clid_name
folio_oa.checklist_item_definition	cli_definition_label	Label for the definition for a checklist item	VARCHAR	NachwuchswissenschaftlerIn
folio_oa.checklist_item_definition	cli_definition_description	Description for the checklist item definition	TEXT	Der/die Antragststeller:in ist Nachwuchswissenschaftler:in.			Original attribute name: clid_description
folio_oa.checklist_item	cli_parent_fk	UUID reference to associated data where the checklists are used	UUID	3e0a383d-b1d9-4a8f-ad39-d35670e7158d	Table: folio_oa.checklist_item Attribute: cli_parent_fk	Example Table: folio_oa.publication_request Attribute: pr_id
folio_oa.checklist_item	cli_outcome_fk	UUID reference to a refdata value	UUID	8aaa80fe859fe8ee0185a0ea5d21000c	Table: folio_oa.checklist_item Attribute: cli_outcome_fk	Table: folio_oa.refdata_value Attribute: rdv_id
folio_oa.refdata_value	cli_outcome_value	Refdata value for checklist item outcome	VARCHAR	yes
folio_oa.refdata_value	cli_outcome_label	Refdata label for checklist item outcome	VARCHAR	Ja
folio_oa.checklist_item	cli_status_fk	UUID reference to a refdata value	UUID	8aaa80fe859fe8ee0185a0ea5e330047	Table: folio_oa.checklist_item Attribute: cli_status_fk	Table: folio_oa.refdata_value Attribute: rdv_id
folio_oa.refdata_value	cli_status_value	Refdata value for checklist item status	VARCHAR	erfolgreich_abgeschlossen
folio_oa.refdata_value	cli_status_label	Refdata label for checklist item status	VARCHAR	Erfolgreich abgeschlossen
folio_oa.publication_request	publication_request_id	UUID reference to a record of the publication request	UUID	2a5945b2-ffda-41f8-bee7-02e5ca52474b	Table: folio_oa.checklist_item Attribute: cli_parent_fk	Table: folio_oa.publication_request Attribute: pr_id	The attribute should enable a better handling. Normally the 'cli_parent_fk' attribute would be sufficient. However, the connection is clearer this way.

Linking approach

folio_oa.checklist_item
LEFT JOIN folio_oa.checklist_item_definition ON checklist_item_definition.clid_id :: UUID = checklist_item.cli_definition_fk :: UUID
LEFT JOIN folio_oa.refdata_value AS cli_outcome ON cli_outcome.rdv_id :: UUID = checklist_item.cli_outcome_fk :: UUID
LEFT JOIN folio_oa.refdata_value AS cli_status ON cli_status.rdv_id :: UUID = checklist_item.cli_status_fk :: UUID
LEFT JOIN folio_oa.publication_request ON publication_request.pr_id :: UUID = checklist_item.cli_parent_fk :: UUID

Explanation to special relations between tables

Relation between the derived tables "openaccess_publication_request" and "openaccess_checklists"

There are 2 ways to connect these two derived tables via UUIDs.

The derived table for the checklist items contains a FK "cli_parent_fk". In this case the "parent" is the publication request and you have to link to the publication request via the UUID "pr_id" in the derived table for the "publication_request".
openaccess_publication_request
LEFT JOIN openaccess_checklists ON openaccess_checklists.cli_parent_fk = openaccess_publication_request.pr_id
There is a attribute "publication_request_id" in the derived table for the checklists that should enable a better handling for the connection to the derived table for the publication requests. Normally the 'cli_parent_fk' attribute would be sufficient. However, the connection is clearer this way.
openaccess_publication_request
LEFT JOIN openaccess_checklists ON openaccess_checklists.publication_request_id = openaccess_publication_request.pr_id