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

Attributes for the derived table

Folio TableFolio AttributeDescriptionDatatypeSample OutputFK (From table)PK (To table)Notes
folio_oa.workw_idID of the record in the table workUUIDc9e32868-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.workw_versionVersion of the record in the table workINTEGER1


folio_oa.workw_titleTitle of the publicationTEXTFrontiers in Analytical Science


folio_oa.workw_indexed_in_doaj_fkID of reference data value for the status in the directory DOAJUUID8aaa80fe-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_valuew_indexed_in_doaj_valueReference data value for the status in the directory DOAJTEXTyes


folio_oa.refdata_valuew_indexed_in_doaj_labelLabel of the reference data value for the status in the directory DOAJTEXTYes


folio_oa.workw_oa_status_fkID of reference data value for the kind of Open AccessUUID8aaa80fe-859f-e8ee-0185-a0ea5d9b0024

Table: folio_oa.work

Attribute: w_oa_status_fk

Table: folio_oa.refdata_value

Attribute: rdv_id


folio_oa.refdata_valuew_oa_status_valueReference data value for the kind of Open AccessTEXTgold


folio_oa.refdata_valuew_oa_status_labelLabel of the reference data value for the kind of Open AccessTEXTGold


folio_oa.title_instanceti_idID of the record in the table title_instanceUUID4334961d-0bdc-4db5-8c66-3c08c4f04af5

Table: folio_oa.title_instance

Attribute: ti_id

Table: folio_oa.identifier_occurrence

Attribute: io_ti_fk


folio_oa.title_instanceti_versionVersion of the record in the table title_instanceINTEGER1


folio_oa.title_instanceti_work_fkID to the related record in the table workUUID0d7918e5-c76a-46e0-a707-83555f6788b3

Table: folio_oa.title_instance

Attribute: ti_work_fk

Table: folio_oa.work

Attribute: w_id


folio_oa.title_instanceti_type_fkID of reference data value for type of the title instanceUUID8aaa80fe859fe8ee0185a0ea5de70035

Table: folio_oa.title_instance

Attribute: ti_type_fk

Table: folio_oa.refdata_value

Attribute: rdv_id


folio_oa.refdata_valueti_type_valueReference data value for the type of the title instanceTEXTserial


folio_oa.refdata_valueti_type_labelLabel of the reference data value for the type of the title instanceTEXTSerial


folio_oa.title_instanceti_subtype_fkID of reference data value for subtype of the title instanceUUID8aaa80fe859fe8ee0185a0ea5ddb0032

Table: folio_oa.title_instance

Attribute: ti_subtype_fk

Table: folio_oa.refdata_value

Attribute: rdv_id


folio_oa.refdata_valueti_subtype_valueReference data value for the subtype of the title instanceTEXTelectronic


folio_oa.refdata_valueti_subtype_labelLabel of the reference data value for the subtype of the title instanceTEXTElectronic


folio_oa.title_instanceti_publication_type_fkID of reference data value for publication type of the title instanceUUID8aaa80fe859fe8ee0185a0ea5df20038

Table: folio_oa.title_instance

Attribute: ti_publication_type_fk

Table: folio_oa.refdata_value

Attribute: rdv_id


folio_oa.refdata_valueti_publication_type_valueReference data value for the publication type of the title instanceTEXTjournal


folio_oa.refdata_valueti_publication_type_labelLabel of the reference data value for the publication type of the title instanceTEXTJournal


folio_oa.title_instanceti_titleTitle of the title instanceTEXT

Frontiers in Big Data




folio_oa.identifier_occurrenceio_idID of the identifier occurrence that is related to the title instanceUUID746cefa3-427e-4387-a481-4ff66999b924


folio_oa.identifier_occurrenceio_versionVersion of the record for identifier occurrenceINTEGER1


folio_oa.identifier_occurrenceio_ti_fkID of the record in the table title_instanceUUIDbc0551f1-ffc8-409c-b75e-695e84019f2f

Table: folio_oa.identifier_occurrence

Attribute: io_ti_fk

Table: folio_oa.title_instance

Attribute: ti_id


folio_oa.identifier_occurrenceio_status_fkStatus of the record for identifier occurrenceUUID8aaa809385efeeed0185f2acf9d10000

Table: folio_oa.identifier_occurrence

Attribute: io_status_fk

Table: folio_oa.refdata_value

Attribute: rdv_id


folio_oa.refdata_valueio_status_valueReference data value for the status of the record for identifier occurrenceTEXT



folio_oa.refdata_valueio_status_labelLabel of the reference data value for the status of the record for identifier occurrenceTEXT



folio_oa.identifier_occurrenceio_identifier_fkID to the record for the identifier that is related to the title instanceUUID

ed99abdb-65fd-4305-a5d4-1455705f074d

Table: folio_oa.identifier_occurrence

Attribute: 

io_identifier_fk

Table: folio_oa.identifier

Attribute: id_id


folio_oa.identifier_occurrenceio_selected
BOOLEAN



folio_oa.identifierid_idID for the record for the identifierUUIDed99abdb-65fd-4305-a5d4-1455705f074d

Table: folio_oa.identifier

Attribute: id_id

Table: folio_oa.identifier_occurrence

Attribute: 

io_identifier_fk


folio_oa.identifierid_versionVersion of the record for the identifierINTEGER1


folio_oa.identifierid_ns_fkID to the record for the type of identifierUUID

8a6cf7ff-c01c-4674-9e4a-47f0b3343b37

Table: folio_oa.identifier

Attribute: id_ns_fk

Table: folio_oa.identifier_namespace

Attribute: 

idns_id


folio_oa.identifierid_valueValue of the identifierTEXT

2813-2831




folio_oa.identifier_namespaceidns_idID of the record for the type of identifierUUID

8a6cf7ff-c01c-4674-9e4a-47f0b3343b37

Table: folio_oa.identifier_namespace

Attribute: 

idns_id

Table: folio_oa.identifier

Attribute: id_ns_fk


folio_oa.identifier_namespaceidns_versionVersion of the record for the type of identifierINTEGER0


folio_oa.identifier_namespaceidns_valueValue of the type of identifierTEXT

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.