Data Model

Data Dictionary

This section would hold information on the tables that will be part of the SCSB middleware database. Information includes, a brief description of the table, the columns involved, a brief description of the columns, data type, the default value (if applicable) and whether the column is mandatory/unique.

SCSB uses Hibernate ORM to persist data.

The following is the list of tables used in SCSB

accession_t

The accession_t table holds the pending records that are inbound to SCSB during ongoing accession. The ongoing accession process might happen at different times of the day in GFA LAS but are processed as a bulk job (usually nightly) in SCSB to optimize performance. The data is used to fetch records from partner's ILS and the data ends up in bibliographic_t, holdings_t, item_t and other related tables. Records are marked 'pending' if they are waiting to be processed or 'completed' post processing. Those that are marked 'completed' are purged regularly through the PurgeAccessionRequests job.

Entity Class: AccessionEntity.java

Use Cases effecting changes in table: Ongoing Accession

Table Type (Master/Transactional): Transactional

bibliographic_holdings_t

The bibliographic_holdings_t table holds both the owning institution bibliographic record id and their respective holdings id. This helps in many-to-many linking between holdings and bibliographic records in case of multiple copies at different locations.

Use Cases effecting changes in table: Initial Accession, Ongoing Accession, Submit Collection

Table Type (Master/Transactional): Transactional

bibliographic_item_t

The bibliographic_item_t table holds both the owning institution bibliographic record id and the respective owning institution item id. This helps in many-to-many linking between items and bibliographic records in case of serials, multiple copies and bound-withs.

Use Cases effecting changes in table: Initial Accession, Ongoing Accession, Submit Collection

Table Type (Master/Transactional): Transactional

bibliographic_t

The bibliographic_t table holds information on bibliographic records. This is a crucial table in SCSB to maintain information about the collections. The table is populated by either the Initial Accession or Ongoing Accession processes. The information from this table is used to display collection details under Search and Collection tabs in SCSB UI. The data in this table can be modified through the Submit Collection API. The bib related XML is stored as a BLOB in the table in the MARCXML format.

Entity Class: BibliographicEntity.java

Use Cases effecting changes in table: Initial Accession, Ongoing Accession, Submit Collection

Table Type (Master/Transactional): Transactional

bulk_customer_code_t

The bulk_customer_code_t table holds the customer codes that are valid for use in bulk request. The information from this table is used to display values in the Delivery Location dropdown in the Bulk Request page of SCSB UI. The values in the delivery location dropdown are also restricted based on the item owning institution value.

Entity Class: BulkCustomerCodeEntity.java

Table Type (Master/Transactional): Master

bulk_request_item_t

The bulk_request_item_t table holds information on the bulk requests that had been initiated in SCSB. It records data relevant to the bulk requests made through the SCSB bulk request UI such as the bulk request name, file name, file data created by, created date, last updated date, patron id, stop code, institution, statuses, email and notes. This is the main table that is extensively used in the circulation functionality catering to the retrieval requests through the bulk request screen in SCSB.

Entity Class: BulkRequestItemEntity.java

Use Cases effecting changes in table: Bulk Request 

Table Type (Master/Transactional): Transactional

bulk_request_t

The bulk_request_t table holds the bulk request id and the request ids of requests that were added through that particular bulk request transaction. This helps in keeping a trail as to where a request inititated.

Use Cases effecting changes in table: Bulk Request 

Table Type (Master/Transactional): Transactional

collection_group_t

The collection_group_t table holds information on the Collection Group Designations (CGD) prevalent among the item records and their description. The values that are currently in use are Open, Shared, Private and Not Available. All partners' items are classified into one of these three CGDs. A partner's 'Private' item is available for request only by patrons of the partner institution, whereas, a partner's 'Open' and 'Shared' items are available for request by patrons of all partner institutions. The 'Not Available' CGD is used in case of dummy records. The CGD is referenced in the item_t tables and is useful in ascertaining validity of the requests.

Entity Class: CollectionGroupEntity.java

Use Cases effecting changes in table: Edit CGD, Submit Collection, Matching Algorithm 

Table Type (Master/Transactional): Master

cross_partner_mapping_t

The cross_partner_mapping_t table holds mapping information between the customer codes and the delivery locations that are to be used in case of cross partner borrowings. Both CUSTOMER_CODE_ID and DELIVERY_RESTRICTION_CROSS_PARTNER_ID are foreign keys that link the customer codes with the delivery location list for cross partner borrowings. Structuring the tables this way allows for a more scalable model.

Table Type (Master/Transactional): Master

customer_code_t

The customer_code_t table holds information on the customer codes prevalent among the item records. Customer codes are two lettered codes from the ReCAP's GFA LAS which identifies the institution and where the records can be delivered with the LAS in ReCAP. SCSB maintains this data to ascertain the validity of the delivery codes entered by various users, patrons and ReCAP staff for both retrieval and deaccession purposes. It holds information such as their description, the owning institution they belong to and the Delivery Restrictions, if any. It also has PWD Delivery Restrictions, ReCAP Delivery Restrictions and Circulation Desk Location. Delivery Restrictions defines the locations where a particular item with a customer code can be delivered as part of retrieval. Similarly, PWD Delivery Restrictions and ReCAP Delivery Restrictions defines locations where a particular item with a customer code can be delivered as part of deaccession (PWD - Permanent Withdrawal Direct) and retrieval request for ReCAP staff, respectively. The Circulation Desk Location is the desk location information needed for communication with the ILS.

Entity Class: CustomerCodeEntity.java

Table Type (Master/Transactional): Master

deleted_records_t

The deleted_records_t table holds information on records that are being deleted in the request_item_t, bibliographic_t, holdings_t and item_t tables.

Entity Class: DeletedRecordsEntity.java

Table Type (Master/Transactional): Transactional

delivery_restriction_cross_partner_t             

The delivery_restriction_cross_partner_t table holds mapping information between the delivery locations and the partners for use during a cross partner borrowing request. The table holds different sets of delivery restrictions valid for different partners for cross partner borrowings as records and these records are linked to customer codes through the cross_partner_mapping_t table.

Entity Class: DeliveryRestrictionEntity.java

Table Type (Master/Transactional): Master

etl_gfa_temp_t

The etl_gfa_temp_t is a table that holds the list of item barcodes and their corresponding statuses maintained in GFA among other information. The information contained in this table is used to arrive at item availability statuses (ITEM_AVAIL_STATUS_ID) in the item_t table. The list of GFA LAS maintained statuses and their SCSB equivalent is documented and available here. Based on this information the SCSB statuses of the items are updated as part of the initial loading process.

Entity Class: EtlGfaEntity.java

Table Type (Master/Transactional): Transactional

holdings_t

The holdings_t table holds information on the holdings record. Holdings record are part of the bibliographic record and typically hold item locations and call numbers. Library of Congress (LOC) defines holdings data as those held in the 85x, 86x and 87x tags of the MARC data. A bibliographic record can have one or more holdings record which in turn can hold one or more item records. The table is populated by either the Initial Accession or Ongoing Accession processes. The data in this table can be modified through the Submit Collection API. The holding related XML tags are stored as a BLOB in the table.

Entity Class: HoldingsEntity.java

Use Cases effecting changes in table: Initial Accession, Ongoing Accession, Submit Collection

Table Type (Master/Transactional): Transactional

institution_t

The institution_t table holds information on the institutions. This table is referenced in many tables to identify the institution. It is referenced in tables maintaining collection data such as bibliographic_t to identify institution to which the item belongs to and also in circulation related tables such as request_item_t to identify institutions from which the requests are initiated.

Entity Class: InstitutionEntity.java

Table Type (Master/Transactional): Master

item_barcode_history_t

The item_barcode_history_t table holds barcodes that are being deleted and replaced with the same owning institution item ids. The table holds both the old and new item barcodes for reference and the date on which it was deleted.

Entity Class: ItemBarcodeHistoryEntity.java

Table Type (Master/Transactional): Transactional

item_change_log_t

The item_change_log_t table holds information on the changes that have been carried out on an item. SCSB allows users to change the Collection Group Designation (CGD) and deaccession records. The table records such changes made to the CGD by users and also the matching algorithm process. Apart from CGD, the table also maintains details on accession, deaccession, deaccession rollbacks, ongoing matching algorithm process, and request items, even if there are exceptions, or the hold request failed at ILS, GFA LAS request fails or if the availability status changes. It captures data such as user who initiated change, the date and time stamp of the operation, operation type and any notes added by the user.

Entity Class: ItemChangeLogEntity.java

Use Cases effecting changes in table: Accession, Deaccession, Request, Edit CGD

Table Type (Master/Transactional): Transactional

item_holdings_t

The item_holdings_t table holds both the owning institution holdings id and the respective owning institution item id. This helps in many-to-many linking between items and holding records. 

Use Cases effecting changes in table: Initial Accession, Ongoing Accession, Submit Collection

Table Type (Master/Transactional): Transactional

item_status_t

The item_status_t table holds the item statuses maintained in SCSB. Currently the statuses are either Available or Not Available. Available when the item is in the ReCAP facility and Not Available when the item has left the ReCAP facility to service a request. The item statuses reflect only the item's availability post a request and not processes such as deaccession.

Entity Class: ItemStatusEntity.java

Table Type (Master/Transactional): Master

item_t

The Item_t table holds information on the items. The table is populated by either the Initial Accession or Ongoing Accession processes. The data in this table can be modified through the Submit Collection API. The item availability status changes depending upon requests against the item and the is deleted column varies depending upon the deaccession process. The item is the smallest building block of a bibliographic information and is the most important, affected by all processes across functionalities such as Collection and Circulation. A single item referenced by multiple bibliographic ids would represent a bound-with record.

Entity Class: ItemEntity.java

Use Cases effecting changes in table: Accession, Deaccession, Submit Collection, Matching Algorithm, Request, Edit CGD

Table Type (Master/Transactional): Transactional

job_param_data_t

The job_param_data_t table holds parameters that are required for the functioning of the Jobs in the job_t table. Parameters are used to enable the configuration of file names, transmission types, report types, etc used by the jobs.

Entity Class: JobParamDataEntity.java

Table Type (Master/Transactional): Master

job_param_t

The job_param_t table holds the record number and the job name from the job_t table. The record number is in turn referenced by the job_param_data_t table.

Entity Class: JobParamEntity.java

Table Type (Master/Transactional): Master

job_t

The job_t table holds information on the different jobs that can be run or scheduled to run in SCSB, such as Purging Emails, Matching Algorithm, Ongoing Accession. The scheduling can be done as a cron job using cron expressions. It also holds information on the last executed time, next run time (if it is a scheduled job) and the status (Scheduled or Unscheduled).

Entity Class: JobEntity.java

Use Cases effecting changes in table: Job tab under SCSB UI

Table Type (Master/Transactional): Master

matching_bib_info_detail_t

The matching_bib_info_detail_t table serves as a table to identify records that go through the matching algorithm and find duplicate records. This information will be used by partner institutions (NYPL, primarily) to consolidate holdings information in their discovery systems. It holds the bibliographic id from SCSB's database (bibliographic_t table), owning institution bibliographic id, the owning institution and the latest record number. The latest record number is the primary key in the report_t table.

Entity Class: MatchingBibInfoDetail.java

Use Cases effecting changes in table: Matching algorithm process

Table Type (Master/Transactional): Transactional

matching_bib_t

The matching_bib_t table serves as a table to identify records that go through the matching algorithm and are found to be duplicates. The content of this table is used to generate reports on the matching records and further process their collection group designation (CGD) depending on their use restrictions. The CGD processing is covered in much detail in the matching algorithm use case document here and here. 

Entity Class: MatchingBibEntity.java

Use Cases effecting changes in table: Matching algorithm process

Table Type (Master/Transactional): Transactional

matching_matchpoints_t

The matching_matchpoints_t table serves as a table to identify records' matchpoints during matching algorithm process. The data stored include the match criteria, the criteria value and the criteria value count. The match criteria includes ISBN, ISSN, LCCN and OCLC number. These are the match points in which the data is checked whether they match. The criteria value holds the values and the criteria value count contains the number of times these values occur.

Entity Class: MatchingMatchPointsEntity.java

Use Cases effecting changes in table: Matching algorithm process

Table Type (Master/Transactional): Transactional

owning_inst_id_seq

The owning_inst_id_seq table holds the sequence ids generated for owning institution id for dummy records so that the ids are not reused.

Entity Class: OwningInstitutionIDSequence.java

Table Type (Master/Transactional): Transactional

permissions_t

The permissions_t table holds information on permissions. Permissions are used along with roles to authorize users for accessing certain functionalities in the SCSB UI. Permissions are the smallest unit for authorization and multiple permissions can be attached to a single role and the role assigned to an user.

Entity Class: PermissionEntity.java

Table Type (Master/Transactional): Master

report_data_t

The report_data_t table works in conjunction with report_t table. It stores relevant statistical data that has been calculated by processing input files. Information about the files reside on the report_t table whereas detailed reports on each input file is stored under different heads under the report_data_t table.

Entity Class: ReportDataEntity.java

Use Cases effecting changes in table: Matching Algorithm

Table Type (Master/Transactional): Transactional

report_t

The report_t table holds information about the files that were used to upload data. The information in report_t table is further used by report_data_t table to store statistical information collected on processing of these input files.

Entity Class: ReportEntity.java

Use Cases effecting changes in table: Matching Algorithm

Table Type (Master/Transactional): Transactional

request_item_status_t

The request_item_status_t table holds the various statuses that a request goes through during its life-cycle. This status is used to process recall requests after retrievals and for identifying request processes that are still ongoing or pending. The status also tracks refiles that are done to the items.

Entity Class: RequestStatusEntity.java

Use Cases effecting changes in table: Create Request

Table Type (Master/Transactional): Master

request_item_t

The request_item_t table holds information on the requests made against items for patrons. It records data relevant to the request made such as the request type, created by, created date, last updated date and notes. This is the main table that is extensively used in the circulation functionality catering to the retrieval request, recall request, electronic document delivery, cancel request and refile. The email id stored in this table is purged periodically by the PurgeEmailAddress job.

Entity Class: RequestItemEntity.java

Use Cases effecting changes in table: Create Request, Cancel Request

Table Type (Master/Transactional): Transactional

request_type_t

The request_type_t table holds information on the types of requests allowed to be made against items by patrons. It is referenced by the request_item_t table. Currently, the request types that are allowed are Retrieve, Recall and EDD (Electronic Document Delivery).

Entity Class: RequestTypeEntity.java

Table Type (Master/Transactional): Master

role_permission_t

The role_permission_t table helps in linking permissions to roles. A role can take in multiple permissions and the table maintains both the roles id and permission id and records their relationships.

Use Cases effecting changes in table: Create/Edit Role

Table Type (Master/Transactional): Master

roles_t

The roles_t table holds information about the roles. The roles are the top level unit for authorization which are assigned to users. A single role can contain one or more permissions. This relationship is maintained in the role_permission_t.

Entity Name: RoleEntity.java

Use Cases effecting changes in table: Create/Edit Role

Table Type (Master/Transactional): Master

user_roles_t

The user_roles_t table helps in linking roles to users. One or more roles can be assigned to a single user. Each roles would in turn have one or more permissions assigned to them. The user and role relationship is maintained in the user_roles_t table.

Use Cases effecting changes in table: Create/Edit User

Table Type (Master/Transactional): Master

user_t

The user_t table holds information about the users. It maintains information such as the login id using which the user logs in to the application and other data such as the user institution, a description, email id, created and updated dates and created and updated by information.

Entity Name: UsersEntity.java

Use Cases effecting changes in table: Create/Edit User

Table Type (Master/Transactional): Master

xml_records_t

The xml_records_t table is used to hold the whole XML content of a record for further processing and eventual loading of the data (ETL) into the respective tables. The table is used during the Initial Accession process.

Entity Name: XmlRecordEntity.java

Use Cases effecting changes in table: ETL process

Table Type (Master/Transactional): Transactional