ERM - OpenAccess derived table for works 'openaccess_works'
Content
Overview
- The purpose of this derived table is to show informations about the records in the table "folio_oa.works" and their linked tables and reference data.
Github Issue
- https://github.com/folio-org/folio-analytics/issues/787 (ERM - Create derived table 'openaccess_works' for Metadb)
Attributes for the derived table
| Folio Table | Folio Attribute | Description | Datatype | Sample Output | FK (From table) | PK (To table) | Notes |
|---|---|---|---|---|---|---|---|
| folio_oa.work | w_id | ID of the record in the table work | UUID | c9e32868-217d-4adc-9fb1-abc3596586d9 | Table: folio_oa.work Attribute: w_id | Table: folio_oa.publication_request Attribute: pr_work_fk --------- Table: folio_oa.title_instance Attribute: ti_work_fk | Link to table folio_oa.publication_request |
| folio_oa.work | w_version | Version of the record in the table work | INTEGER | 1 | |||
| folio_oa.work | w_title | Title of the publication | TEXT | Frontiers in Analytical Science | |||
| folio_oa.work | w_indexed_in_doaj_fk | ID of reference data value for the status in the directory DOAJ | UUID | 8aaa80fe-859f-e8ee-0185-a0ea5d8e0021 | Table: folio_oa.work Attribute: w_indexed_in_doaj_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | w_indexed_in_doaj_value | Reference data value for the status in the directory DOAJ | TEXT | yes | |||
| folio_oa.refdata_value | w_indexed_in_doaj_label | Label of the reference data value for the status in the directory DOAJ | TEXT | Yes | |||
| folio_oa.work | w_oa_status_fk | ID of reference data value for the kind of Open Access | UUID | 8aaa80fe-859f-e8ee-0185-a0ea5d9b0024 | Table: folio_oa.work Attribute: w_oa_status_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | w_oa_status_value | Reference data value for the kind of Open Access | TEXT | gold | |||
| folio_oa.refdata_value | w_oa_status_label | Label of the reference data value for the kind of Open Access | TEXT | Gold | |||
| folio_oa.title_instance | ti_id | ID of the record in the table title_instance | UUID | 4334961d-0bdc-4db5-8c66-3c08c4f04af5 | Table: folio_oa.title_instance Attribute: ti_id | Table: folio_oa.identifier_occurrence Attribute: io_ti_fk | |
| folio_oa.title_instance | ti_version | Version of the record in the table title_instance | INTEGER | 1 | |||
| folio_oa.title_instance | ti_work_fk | ID to the related record in the table work | UUID | 0d7918e5-c76a-46e0-a707-83555f6788b3 | Table: folio_oa.title_instance Attribute: ti_work_fk | Table: folio_oa.work Attribute: w_id | |
| folio_oa.title_instance | ti_type_fk | ID of reference data value for type of the title instance | UUID | 8aaa80fe859fe8ee0185a0ea5de70035 | Table: folio_oa.title_instance Attribute: ti_type_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | ti_type_value | Reference data value for the type of the title instance | TEXT | serial | |||
| folio_oa.refdata_value | ti_type_label | Label of the reference data value for the type of the title instance | TEXT | Serial | |||
| folio_oa.title_instance | ti_subtype_fk | ID of reference data value for subtype of the title instance | UUID | 8aaa80fe859fe8ee0185a0ea5ddb0032 | Table: folio_oa.title_instance Attribute: ti_subtype_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | ti_subtype_value | Reference data value for the subtype of the title instance | TEXT | electronic | |||
| folio_oa.refdata_value | ti_subtype_label | Label of the reference data value for the subtype of the title instance | TEXT | Electronic | |||
| folio_oa.title_instance | ti_publication_type_fk | ID of reference data value for publication type of the title instance | UUID | 8aaa80fe859fe8ee0185a0ea5df20038 | Table: folio_oa.title_instance Attribute: ti_publication_type_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | ti_publication_type_value | Reference data value for the publication type of the title instance | TEXT | journal | |||
| folio_oa.refdata_value | ti_publication_type_label | Label of the reference data value for the publication type of the title instance | TEXT | Journal | |||
| folio_oa.title_instance | ti_title | Title of the title instance | TEXT | Frontiers in Big Data | |||
| folio_oa.identifier_occurrence | io_id | ID of the identifier occurrence that is related to the title instance | UUID | 746cefa3-427e-4387-a481-4ff66999b924 | |||
| folio_oa.identifier_occurrence | io_version | Version of the record for identifier occurrence | INTEGER | 1 | |||
| folio_oa.identifier_occurrence | io_ti_fk | ID of the record in the table title_instance | UUID | bc0551f1-ffc8-409c-b75e-695e84019f2f | Table: folio_oa.identifier_occurrence Attribute: io_ti_fk | Table: folio_oa.title_instance Attribute: ti_id | |
| folio_oa.identifier_occurrence | io_status_fk | Status of the record for identifier occurrence | UUID | 8aaa809385efeeed0185f2acf9d10000 | Table: folio_oa.identifier_occurrence Attribute: io_status_fk | Table: folio_oa.refdata_value Attribute: rdv_id | |
| folio_oa.refdata_value | io_status_value | Reference data value for the status of the record for identifier occurrence | TEXT | ||||
| folio_oa.refdata_value | io_status_label | Label of the reference data value for the status of the record for identifier occurrence | TEXT | ||||
| folio_oa.identifier_occurrence | io_identifier_fk | ID to the record for the identifier that is related to the title instance | UUID | ed99abdb-65fd-4305-a5d4-1455705f074d | Table: folio_oa.identifier_occurrence Attribute: io_identifier_fk | Table: folio_oa.identifier Attribute: id_id | |
| folio_oa.identifier_occurrence | io_selected | BOOLEAN | |||||
| folio_oa.identifier | id_id | ID for the record for the identifier | UUID | ed99abdb-65fd-4305-a5d4-1455705f074d | Table: folio_oa.identifier Attribute: id_id | Table: folio_oa.identifier_occurrence Attribute: io_identifier_fk | |
| folio_oa.identifier | id_version | Version of the record for the identifier | INTEGER | 1 | |||
| folio_oa.identifier | id_ns_fk | ID to the record for the type of identifier | UUID | 8a6cf7ff-c01c-4674-9e4a-47f0b3343b37 | Table: folio_oa.identifier Attribute: id_ns_fk | Table: folio_oa.identifier_namespace Attribute: idns_id | |
| folio_oa.identifier | id_value | Value of the identifier | TEXT | 2813-2831 | |||
| folio_oa.identifier_namespace | idns_id | ID of the record for the type of identifier | UUID | 8a6cf7ff-c01c-4674-9e4a-47f0b3343b37 | Table: folio_oa.identifier_namespace Attribute: idns_id | Table: folio_oa.identifier Attribute: id_ns_fk | |
| folio_oa.identifier_namespace | idns_version | Version of the record for the type of identifier | INTEGER | 0 | |||
| folio_oa.identifier_namespace | idns_value | Value of the type of identifier | TEXT | issn |
Linking approach
folio_oa.work
LEFT JOIN folio_oa.title_instance ON title_instance.ti_work_fk = work.w_id
LEFT JOIN folio_oa.identifier_occurrence ON identifier_occurrence.io_ti_fk = title_instance.ti_id
LEFT JOIN folio_oa.identifier ON identifier.id_id = identifier_occurrence.io_identifier_fk
LEFT JOIN folio_oa.identifier_namespace ON identifier_namespace.idns_id = identifier.id_ns_fk
LEFT JOIN folio_oa.refdata_value AS journal_doaj_status ON journal_doaj_status.rdv_id = work.w_indexed_in_doaj_fk
LEFT JOIN folio_oa.refdata_value AS jounal_oa_status ON jounal_oa_status.rdv_id = work.w_oa_status_fk
LEFT JOIN folio_oa.refdata_value AS ti_type ON ti_type.rdv_id = title_instance.ti_type_fk
LEFT JOIN folio_oa.refdata_value AS ti_subtype ON ti_subtype.rdv_id = title_instance.ti_subtype_fk
LEFT JOIN folio_oa.refdata_value AS ti_publication_type ON ti_publication_type.rdv_id = title_instance.ti_publication_type_fk
LEFT JOIN folio_oa.refdata_value AS io_status ON io_status.rdv_id = identifier_occurrence.io_status_fk
Specification for the creation of the derived table
The output should be designed in such a way that each title instance has its own line with its identifiers. Anyone who later uses the derived table can then filter the data accordingly in the where clause.
Explanation to special relations between tables
Relation "work" to "title instances"
A "work" can have several "title instances".
The link between the tables "work" and "title_instance" has to be an left join. There is no direct connection in the ER diagram, but there is a FK in the table "title_instance" to the table "work".
folio_oa.work
LEFT JOIN folio_oa.title_instance ON title_instance.ti_work_fk = work.w_id
Relation "title_instance" to "identifier"
The link between the tables "title_instance" and "identifier" has to be an left join. There is no direct connection in the ER diagram, but you can use the FK "io_identifier_fk" in the table "identifier_occurrence" to link to table "identifier" with the attribute "id_id ".
LEFT JOIN folio_oa.identifier_occurrence ON identifier_occurrence.io_ti_fk = title_instance.ti_id
LEFT JOIN folio_oa.identifier ON identifier.id_id = identifier_occurrence.io_identifier_fk
LEFT JOIN folio_oa.identifier_namespace ON identifier_namespace.idns_id = identifier.id_ns_fk
Data for types that are in the “publication_request” and “works” tables
It appears that some data about types is duplicated in the "publication_request" and "works" tables. However, that is not entirely true. What is the difference?
- "publication_request" contains the types for a publication when the request was created in the system.
- "works" contains the types for a "work" with the current types (The data needs to be refreshed to get the latest.). These may change over time and differ from the types at the time the publication request was made.