This section will evolve as the project progresses and will be updated regularly to reflect changes.

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ACCESSION_ID	Primary key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the accession record within the table in SCSB database
ACCESSION_REQUEST	The request content in JSON format is stored as a BLOB.	LONGBLOB	NULL
CREATED_DATE	The time-stamp at which the accession was received in SCSB.	DATETIME	NULL
ACCESSION_STATUS	The status of the record in the accession process.	VARCHAR(45)	NULL			'Pending' and 'Completed' are the two valid statuses.

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

Column Name	Description	Data Type	Mandatory	Comments
OWNING_INST_BIB_ID	Composite Primary and Foreign Key. The data is the unique identifier used internally in the institution's ILS for the Bibliographic data.	VARCHAR(45)	Y	Foreign Key Reference: OWNING_INST_BIB_ID column of bibliographic_t table.
BIB_INST_ID	Composite Primary and Foreign Key. The data is the id representing the institution that owns the title/holdings/item in SCSB.	INT(11)	Y	Foreign Key Reference: OWNING_INST_ID column of bibliographic_t table.
HOLDINGS_INST_ID	Composite Primary and Foreign Key. The data is the id representing the institution that owns the title/holdings/item in SCSB.	INT(11)	Y	Foreign Key Reference: OWNING_INST_ID column of holdings_t table.
OWNING_INST_HOLDINGS_ID	Composite Primary and Foreign Key. The data is the unique identifier used internally in the institution's ILS for the holdings data.	VARCHAR(100)		Foreign Key Reference: OWNING_INST_HOLDINGS_ID column of holdings_t table.

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

Column Name	Description	Data Type	Mandatory	Comments
OWNING_INST_BIB_ID	Composite Primary and Foreign Key. The data is the unique identifier used internally in the institution's ILS for the Bibliographic data.	VARCHAR(45)	Y	Foreign Key Reference: OWNING_INST_BIB_ID column of bibliographic_t table.
BIB_INST_ID	Composite Primary and Foreign Key. The data is the id representing the institution that owns the title/holdings/item in SCSB.	INT(11)	Y	Foreign Key Reference: OWNING_INST_ID column of bibliographic_t table.
ITEM_INST_ID	Composite Primary and Foreign Key. The data is the id representing the institution that owns the title/holdings/item in SCSB.	INT(11)	Y	Foreign Key Reference: OWNING_INST_ID column of item_t table.
OWNING_INST_ITEM_ID	Composite Primary and Foreign Key. The data is the unique identifier used internally in the institution's ILS for the item data.	VARCHAR(45)	Y	Foreign Key Reference: OWNING_INST_ITEM_ID column of item_t table

bibliographic_holdings_t vs bibliographic_item_t

Though bibliographic_holdings_t and bibliographic_item_t may look similar in form and function they differ in that the former maintains relationship between the bibliographic data and the holdings data while the latter maintains the relationship between bibliographic data and item data. A single bibliographic data might have multiple items in case of serials and multiple holdings in case of multiple copies in different locations. Multiple bibliographic data might be attached to a single item in case of bound-withs.

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
BIBLIOGRAPHIC_ID		INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the bib within the table in SCSB database
CONTENT	Bibliographic content. Usually in the MARCXML format.	LONGBLOB		Y
OWNING_INST_ID	Foreign Key. The ID representing the institution that owns the title/holdings/item.	INT(11)		Y		Foreign Key Reference: INSTITUTION_ID column of institution_t table.
CREATED_DATE	The time-stamp when title was first accessioned.	DATETIME		Y
CREATED_BY	The creator of the bibiliographic record is persisted here.	VARCHAR(45)		Y
LAST_UPDATED_DATE	The time-stamp when information was last updated.	DATETIME
LAST_UPDATED_BY	Records information on who last updated the record.	VARCHAR(45)		Y
OWNING_INST_BIB_ID	Composite Primary Key. The unique identifier used internally in the institution's ILS for Bib.	VARCHAR(45)			Y
IS_DELETED	This column will function as a flag to denote if the item record has been deleted (deaccessioned).	TINYINT(1)				It takes in either 0 or 1 as values. 0 = not deleted and 1 = deleted
CATALOGING_STATUS	This will hold information on complete and incomplete records.	VARCHAR(20)	NULL			It takes in either 'Complete' or 'Incomplete' as values.

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
BULK_CUSTOMER_CODE_ID	Primary Key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the bulk customer code within the table in SCSB database
CUSTOMER_CODE	A code that defines the bulk customer code	VARCHAR(45)		Y
DESCRIPTION	A description about the bulk customer code	VARCHAR(2000)		Y
OWNING_INST_ID	The Institution id to which the customer code belongs to	INT(11)				Foreign Key Reference: INSTITUTION_ID column of institution_t table.

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
BULK_REQUEST_ID	Primary Key	INT(11)		Y	This is an incremental system-generated value which would help to uniquely identify the bulk request data within the table in SCSB database.
BULK_REQUEST_NAME	A name that can be used by the user to differentiate bulk requests. This can be used to search from the bulk request search page.	VARCHAR(255)		Y
BULK_REQUEST_FILE_NAME	The name of the file that is being uploaded by the user.	VARCHAR(255)		Y
BULK_REQUEST_FILE_DATA	The contents of the file is stored as a BLOB. This will be used to prepare the report once the process is complete.	LONGBLOB		Y
CREATED_BY	The user who initiates the bulk request process	VARCHAR(45)		Y
CREATED_DATE	The time-stamp when the bulk request is initiated	DATETIME		Y
LAST_UPDATED_DATE	The time-stamp when the bulk request was last updated.	DATETIME	NULL
PATRON_ID	The id of the patron to whom the requested items are delivered.	VARCHAR(45)		Y
STOP_CODE	The stop code for delivery purpose	VARCHAR(45)		Y
REQUESTING_INST_ID	The institution to which the items belong/are delivered.	INT(11)		Y	Foreign Key Reference: INSTITUTION_ID column of institution_t table.
REQUEST_STATUS	The status of the bulk request	VARCHAR(45)		Y
NOTES	Any notes that are added by the user from the SCSB bulk request screen.	VARCHAR(2000)	NULL
EMAIL_ID	The email id of the patron who had requested the items	VARCHAR(100)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
BULK_REQUEST_ID	Foreign Key	INT(11)		Y		Foreign Key Reference: BULK_REQUEST_ID column of bulk_request_item_t table.
REQUEST_ID	Foreign Key	INT(11)		Y		Foreign Key Reference: REQUEST_ID column of request_item_t table.

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
COLLECTION_GROUP_ID	Primary Key	INT(11)		Y	This is an incremental system-generated value which would help to uniquely identify the collection group designation within the table in SCSB database.
COLLECTION_GROUP_CODE	A code that defines the collection group	VARCHAR(45)		Y
COLLECTION_GROUP_DESC	A description about the collection group designation	VARCHAR(45)	NULL
CREATED_DATE	The time-stamp when the collection group designation was first created.	DATETIME		Y
LAST_UPDATED_DATE	The time-stamp when the collection group designation was last updated.	DATETIME	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
CUSTOMER_CODE_ID	Foreign Key.	INT(11)		Y		Foreign Key Reference: CUSTOMER_CODE_ID column of customer_code_t table.
DELIVERY_RESTRICTION_CROSS_PARTNER_ID	Foreign Key.	INT(11)		Y		Foreign Key Reference: DELIVERY_RESTRICTION_CROSS_PARTNER_ID column of delivery_restriction_cross_partner_t table.

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
CUSTOMER_CODE_ID	Primary Key	INT(11)		Y		This is an incremental system-generated value which would help to uniquely identify the customer code within the table in SCSB database.
CUSTOMER_CODE	A code that defines the Customer Code	VARCHAR(45)		Y	Y
DESCRIPTION	A description about the Customer Code	VARCHAR(2000)		Y
OWNING_INST_ID	Foreign Key. The ID representing the institution.	INT(11)	NULL			Foreign Key Reference: INSTITUTION_ID column of institution_t table.
DELIVERY_RESTRICTIONS	Customer codes to which deliveries are to be restricted.	VARCHAR(2000)	NULL
PWD_DELIVERY_RESTRICTIONS	Customer codes to which deliveries are to be restricted during a Permanent Withdrawal - Direct (PWD) Deaccession call.	VARCHAR(2000)	NULL
RECAP_DELIVERY_RESTRICTIONS	Customer codes to which ReCAP staff has special access	VARCHAR(2000)	NULL
CIRC_DESK_LOCATION	The circulation desk location equivalent in the partner's ILS.	VARCHAR(20)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
DELETED_RECORDS_ID	Primary Key	INT(11)		Y	This is an incremental system-generated value which would help to uniquely identlfy the deleted record within the table in SCSB database.
RECORDS_TABLE	The table to which the deleted record belonged.	VARCHAR(50)		Y
RECORDS_PRIMARY_KEY	The deleted record's primary key.	VARCHAR(100)		Y
DELETED_REPORTED_STATUS	The column denotes whether this has been reported to registered users through email.	VARCHAR(30)		Y
DELETED_DATE	The datetime stamp at which the record was deleted.	DATETIME		Y
DELETED_BY	The user who initiated the delete operation	VARCHAR(50)	NULL
RECORDS_LOG	The column holds the entire data of the deleted row as comma separated values.	LONGTEXT	NULL

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

Column Name	Description	Data Type	Mandatory	Comments
DELIVERY_RESTRICTION_CROSS_PARTNER_ID	Primary Key	INT(11)	Y	This is an incremental system-generated value which would help to uniquely identify the delivery restriction cross parnter within the table in SCSB database.
DELIVERY_RESTRICTIONS	Customer codes to which deliveries are to be restricted during a cross partner borrowing.	VARCHAR(2000)	Y
INSTITUTION_ID	Foreign Key	INT(11)	Y	Foreign Key Reference: INSTITUTION_ID column of institution_t table.

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

Column Name	Description	Data Type	Mandatory
ITEM_BARCODE	Primary Key. The item barcode as maintained in GFA.	VARCHAR(45)	Y
CUSTOMER_CODE	The customer code as maintained in GFA.	VARCHAR(45)	Y
ITEM_STATUS	The item status as captured by GFA. This information is used to derive the item availability status in the item_t table.	VARCHAR(45)	Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
HOLDINGS_ID		INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the holdings data within the table in SCSB database
CONTENT	Holdings content.	BLOB		Y
CREATED_DATE	The time-stamp when holdings data was first accessioned.	DATETIME		Y
CREATED_BY	The creator of the holdings record is persisted here.	VARCHAR(45)		Y
LAST_UPDATED_DATE	The time-stamp when information was last updated.	DATETIME
LAST_UPDATED_BY	Records information on who last updated the record.	VARCHAR(45)		Y
OWNING_INST_ID	Foreign Key and Composite Primary Key. The ID representing the institution that owns the holdings.	INT(11)		Y	Foreign Key Reference: INSTITUTION_ID column of institution_t table.
OWNING_INST_HOLDINGS_ID	Composite Primary Key. The unique identifier used internally in the institution's ILS for the holdings data.	VARCHAR(100)		Y
IS_DELETED	This column will function as a flag to denote if the item record has been deleted (deaccessioned).	TINYINT(1)			It takes in either 0 or 1 as values. 0 = not deleted and 1 = deleted

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
INSTITUTION_ID	Primary key	INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the institution within the table in SCSB database
INSTITUTION_CODE	A code that defines the institution	VARCHAR(45)		Y
INSTITUTION_NAME	The name of the institution	VARCHAR(45)		Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
HISTORY_ID	Primary key	INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the historic record within the table in SCSB database
OWNING_INST	The institution that owns the item.	VARCHAR(45)
OWNING_INST_ITEM_ID	The unique identifier used internally in the institution's ILS for Item.	VARCHAR(45)
OLD_BARCODE	The old barcode of the item record that has been removed from the item_t table	VARCHAR(45)
NEW_BARCODE	The new barcode of the item record that has replaced the removed old_barcode	VARCHAR(45)
CREATED_DATE	The date and time stamp at which this history record is being created.	DATETIME

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
ITEM_CHANGE_LOG_ID	Primary Key.	INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the change log within the table in SCSB database
UPDATED_BY	Data of the user who initiated the change.	VARCHAR(45)	NULL
UPDATED_DATE	Date and time stamp at which the change was initiated	DATETIME	NULL
OPERATION_TYPE	Defines the type of operation that has been initiated	VARCHAR(200)	NULL
RECORD_ID	The primary key id on their own SCSB tables.	INT(11)	NULL
NOTES	The notes entered by the user at the time of initiating the operation.	VARCHAR(2000)	NULL

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

Column Name	Description	Data Type	Mandatory	Comments
OWNING_INST_HOLDINGS_ID	Composite Primary and Foreign Key. The unique identifier used internally in the institution's ILS for Holdings.	VARCHAR(100)	Y	Foreign Key Reference: OWNING_INST_HOLDINGS_ID column of holdings_t table.
HOLDINGS_INST_ID	Composite Primary and Foreign Key. The ID representing the institution that owns the title/holdings/item.	INT(11)	Y	Foreign Key Reference: OWNING_INST_ID column of holdings_t table.
ITEM_INST_ID	Composite Primary and Foreign Key. The ID representing the institution that owns the title/holdings/item.	INT(11)	Y	Foreign Key Reference: OWNING_INST_ID column of item_t table.
OWNING_INST_ITEM_ID	Composite Primary and Foreign Key. The unique identifier used internally in the institution's ILS for Item.	VARCHAR(45)	Y	Foreign Key Reference: OWNING_INST_ITEM_ID column of item_t table.

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
ITEM_STATUS_ID	Primary key	INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the item status within the table in SCSB database
STATUS_CODE	A code that defines the item status	VARCHAR(45)		Y
STATUS_DESC	The description of the item status in detail	VARCHAR(2000)		Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ITEM_ID		INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the item within the table in SCSB database
BARCODE	The Bar code assigned to the item by the institution	VARCHAR(45)		Y
CUSTOMER_CODE	Customer Code	VARCHAR(45)		Y
CALL_NUMBER	The call number assigned to the item by the institution	VARCHAR(2000)	NULL
CALL_NUMBER_TYPE	The type of call number	VARCHAR(80)	NULL
ITEM_AVAIL_STATUS_ID	Foreign Key. Item availability status	INT(11)		Y		Foreign Key Reference: ITEM_STATUS_ID column of item_status_t table.
COPY_NUMBER	Copy Number	INT(11)	NULL
OWNING_INST_ID	Composite Primary Key. The ID representing the institution that owns the title/holdings/item.	INT(11)		Y		Foreign Key Reference: INSTITUTION_ID column of institution_t table.
COLLECTION_GROUP_ID	Foreign Key. Collection Group Designation	INT(11)		Y		Foreign Key Reference: COLLECTION_GROUP_ID column of collection_group_t table.
CREATED_DATE	The time-stamp when item was first accessioned.	DATETIME		Y
CREATED_BY	The creator of the item record is persisted here.	VARCHAR(45)		Y
LAST_UPDATED_DATE	The time-stamp when information was last updated.	DATETIME
LAST_UPDATED_BY	Records information on who last updated the record.	VARCHAR(45)		Y
USE_RESTRICTIONS	This is to indicate any specific restrictions regarding the usage of the item.	VARCHAR(2000)	NULL
VOLUME_PART_YEAR	This is the volume/part/year information	VARCHAR(2000)	NULL
OWNING_INST_ITEM_ID	Composite Primary Key. The unique identifier used internally in the institution's ILS for Item.	VARCHAR(45)		Y
IS_DELETED	This column will function as a flag to denote if the item record has been deleted (deaccessioned).	TINYINT(1)	0			It takes in either 0 or 1 as values. 0 = not deleted and 1 = deleted
CATALOGING_STATUS	The status of the record on whether it is complete or incomplete.	VARCHAR(20)	NULL			It takes in either 'Complete' or 'Incomplete' as statuses.
INITIAL_MATCHING_DATE	This field is used to identify the record whose CGD was modified through the initial matching algorithm. It also serves as a date holder as to when the CGD change happened.	DATE	NULL
CGD_CHANGE_LOG	This field is used to identify the record whose CGD was changed to 'Private' from 'Open' or 'Shared' to include it in the deleted feed.	VARCHAR(20)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
JOB_PARAM_DATA_ID	Primary key	INT(11)		Y	This is an incremental system-generated value which would help to uniquely identify the job param data id within the table in SCSB database
PARAM_NAME	The name of the parameter	VARCHAR(100)		Y
PARAM_VALUE	The value of the parameter	VARCHAR(2000)		Y
RECORD_NUM	The record number that is referenced from the job_param_t table.	INT(11)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
RECORD_NUM	Primary Key	INT(11)	NULL			This is an incremental system-generated value which would help to uniquely identify the job parameter within the table in SCSB database
JOB_NAME	The name of the job referenced from the job_t table.	VARCHAR(45)		Y	Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
JOB_ID	Primary key	INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the job id within the table in SCSB database
JOB_NAME	The name of the job	VARCHAR(45)	NULL
JOB_DESC	A short description about the job	VARCHAR(2000)	NULL
LAST_EXECUTED_TIME	The time-stamp at which the job was last executed.	DATETIME	NULL
NEXT_RUN_TIME	The time-stamp at which the job is scheduled to run next time	DATETIME	NULL
CRON_EXP	The cron expression that defines the schedule at which the job needs to be executed. More details here on how to define a cron expression.	VARCHAR(45)	NULL
STATUS	The status of the job	VARCHAR(45)	NULL		Values are either 'Scheduled' or 'Unscheduled'.
JOB_INSTANCE_ID	Spring framework generated id for the job through which multiple executions of the job can be tracked.	INT(11)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
MATCHING_BIB_INFO_DATA_DUMP_ID	Primary Key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the matching bib info within the table in SCSB database
BIB_ID	The bibliographic id of the record in SCSB database	VARCHAR(45)		Y
OWNING_INST_BIB_ID	The bibliographic id of the record in the owning institution's database	VARCHAR(45)		Y
OWNING_INST	The id of the institution to which the record belongs to.	VARCHAR(5)		Y
LATEST_RECORD_NUM	The record number of the record under discussion.	INT(11)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ID	Primary Key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the matching bib record within the table in SCSB database
ROOT		VARCHAR(200)		Y
BIB_ID	The bibliographic id of the record in SCSB database	INT(11)		Y
OWNING_INSTITUTION	The id representing the institution to which the bibliographic record belongs.	VARCHAR(5)	NULL
OWNING_INST_BIB_ID	The bibliographic id of the record in the partner's ILS.	VARCHAR(45)	NULL
TITLE	The title of the bibliographic record. Usually derived from the 245 $a.	VARCHAR(2000)	NULL
OCLC	The OCLC number of the bibliographic record.	VARCHAR(1500)	NULL
ISBN	The ISBN of the bibliographic record.	VARCHAR(1500)	NULL
ISSN	The ISSN of the bibliographic record.	VARCHAR(1500)	NULL
LCCN	The LCCN of the bibliographic record.	VARCHAR(500)	NULL
MATERIAL_TYPE	The material type of the bibliographic record.	VARCHAR(45)	NULL			Monograph, Serial or other
MATCHING		VARCHAR(45)	NULL
STATUS		VARCHAR(45)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ID	Primary Key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the matching matchpoint record within the table in SCSB database
MATCH_CRITERIA	The criteria defines the type of value the records match on. For example, OCLCNumber	VARCHAR(45)
CRITERIA_VALUE	The actual value of the matching criteria.	VARCHAR(500)
CRITERIA_VALUE_COUNT	The number of times the value of the matching criteria occurs.	INT(11)

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ID	Primary Key	INT(11)	System-generated	Y		This is an incremental system-generated value.

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
PERMISSION_ID	Primary key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the permission record within the table in SCSB database
PERMISSION_NAME	The name of the permission	VARCHAR(120)	NULL
PERMISSION_DESCRIPTION	A brief description of the permission	VARCHAR(80)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
REPORT_DATA_ID	Primary key	INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the record data within the table in SCSB database
HEADER_NAME	Header name is a short description of the value in the Header value column. Eg: Total records in file, Total bibs loaded, etc.	VARCHAR(100)		Y
HEADER_VALUE	Header value is the statistical value that is arrived at, on processing the input data.	VARCHAR(8000)		Y
RECORD_NUM		INT(11)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Comments
RECORD_NUM	Primary key	INT(11)	System-generated	Y	This is an incremental system-generated value which would help to uniquely identify the record number within the table in SCSB database
FILE_NAME	The name of the file that has been uploaded for loading data.	VARCHAR(45)		Y
TYPE	This denotes success or failure of the loading process.	VARCHAR(45)		Y
CREATED_DATE	The time-stamp at which the process was completed.	DATETIME		Y
INSTITUTION_NAME	The name of the institution which provided the file.	VARCHAR(5)		Y	Values are CUL, PUL or NYPL

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

Column Name	Description	Data Type	Mandatory	Unique	Comments
REQUEST_STATUS_ID	Primary Key.	INT(11)	Y	Y	This is an incremental system-generated value
REQUEST_STATUS_CODE	A code for the request statuses that are to be maintained in SCSB	VARCHAR(45)	Y	Y
REQUEST_STATUS_DESC	A description for the request statuses that are to be maintained in SCSB	VARCHAR(512)	Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
REQUEST_ID	Primary key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value
ITEM_ID	Foreign Key. The item id assigned to the item by SCSB	INT(11)		Y		Foreign Key Reference: ITEM_ID column of item_t table.
REQUEST_TYPE_ID	Foreign Key. The type of request	INT(11)		Y		Foreign Key Reference: REQUEST_TYPE_ID column of request_type_t table.
REQ_EXP_DATE	The time-stamp at which the request would expire.	DATETIME	NULL
CREATED_BY	The id of the user by whom the request was created.	VARCHAR(45)		Y
CREATED_DATE	The time-stamp at which the request was created.	DATETIME		Y
LAST_UPDATED_DATE	The time-stamp at which the request was updated.	DATETIME	NULL
PATRON_ID	The patron id assigned to the patron by the institution	VARCHAR(45)		Y
STOP_CODE	Stop Code for Delivery purpose	VARCHAR(45)		Y
REQUESTING_INST_ID	Foreign Key. The Id assigned to the institution of the patron	INT(11)		Y		Foreign Key Reference: INSTITUTION_ID column of institution_t table.
REQUEST_STATUS_ID	Foreign Key. The id assigned to the request status.	INT(11)		Y		Foreign Key Reference: REQUEST_STATUS_ID column of request_item_status_t table.
NOTES	Notes relevant to the request	VARCHAR(2000)	NULL
EMAIL_ID	Email id of the patron or those relevant to the request	VARCHAR(45)	NULL

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
REQUEST_TYPE_ID	Primary key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the request type within the table in SCSB database
REQUEST_TYPE_CODE	The code assigned to the request type	VARCHAR(45)		Y
REQUEST_TYPE_DESC	The description of the request type	VARCHAR(45)		Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ROLE_ID	Foreign Key. The Id to uniquely identify the role within the table in SCSB database.	INT(11)		Y		Foreign Key Reference: ROLE_ID column of roles_t table.
PERMISSION_ID	Foreign Key. The Id to uniquely identify the permission within the table in SCSB database.	INT(11)		Y		Foreign Key Reference: PERMISSION_ID column of permissions_t table.

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ROLE_ID	Primary key	INT(11)	System-generated	Y		This is an incremental system-generated value which would help to uniquely identify the role within the table in SCSB database
ROLE_NAME	The name of the role	VARCHAR(45)		Y	Y
ROLE_DESCRIPTION	A brief description about the role	VARCHAR(80)	NULL
CREATED_DATE	The date on which the role was created	DATETIME		Y
CREATED_BY	The user by whom the role was created	VARCHAR(45)		Y
LAST_UPDATED_DATE	The date on which the role was updated	DATETIME		Y
LAST_UPDATED_BY	The user by whom the role was updated	VARCHAR(45)		Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
USER_ID	Foreign Key. The Id to uniquely identify the user within the table in SCSB database.	INT(11)		Y		Foreign Key Reference: USER_ID column of user_t table.
ROLE_ID	Foreign Key. The Id to uniquely identify the role within the table in SCSB database.	INT(11)		Y		Foreign Key Reference: ROLE_ID column of roles_t table.

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
USER_ID	Primary key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the user within the table in SCSB database
LOGIN_ID	The login Id that the user uses to log into their respective institution services.	VARCHAR(45)		Y
USER_INSTITUTION	Foreign Key. The id of the institution to which the user belonged.	INT(11)		Y		Foreign Key Reference: INSTITUTION_ID column of institution_t table.
USER_DESCRIPTION		VARCHAR(45)		Y
USER_EMAILID	The email address of the user	VARCHAR(60)
CREATED_DATE	The date on which the user was created	DATETIME		Y
CREATED_BY	The user by which this user was created	VARCHAR(45)		Y
LAST_UPDATED_DATE	The date on which the user data was updated	DATETIME		Y
LAST_UPDATED_BY	The user by whom this user was last updated	VARCHAR(45)		Y

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

Column Name	Description	Data Type	Default Value	Mandatory	Unique	Comments
ID	Primary key	INT(11)	System-generated	Y	Y	This is an incremental system-generated value which would help to uniquely identify the record within the table in SCSB database
XML_RECORD	The record content in the XML format.	LONGBLOB		Y
XML_FILE	The name of the file from which the XML content was taken.	VARCHAR(100)		Y
OWNING_INST_BIB_ID	The bibliographic id maintained in the ILS of the owning institution.	VARCHAR(45)		Y
OWNING_INST	The owning institution that owns the bibliographic record.	VARCHAR(10)		Y
DATE_LOADED	The time-stamp at which the record was loaded.	DATETIME		Y