acq:serials:basic_predicting_and_receiving
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
acq:serials:basic_predicting_and_receiving [2009/11/18 16:36] – dbw2 | acq:serials:basic_predicting_and_receiving [2022/02/10 13:34] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Proposal: Serials - Basic Predicting and Receiving ====== | ====== Proposal: Serials - Basic Predicting and Receiving ====== | ||
FIXME This proposal is still in progress, please do not edit yet FIXME | FIXME This proposal is still in progress, please do not edit yet FIXME | ||
+ | |||
+ | Okay - but let's keep a link to the [[https:// | ||
+ | |||
===== Summary ===== | ===== Summary ===== | ||
Now that MFHD records can be added and are linked to bib records, the next step is to start populating the issuance table in order to predict and receive issues. As issues are received the MFHD record must be updated accordingly. | Now that MFHD records can be added and are linked to bib records, the next step is to start populating the issuance table in order to predict and receive issues. As issues are received the MFHD record must be updated accordingly. | ||
===== Details ===== | ===== Details ===== | ||
+ | ==== Proposed Schema - Major Revision ==== | ||
+ | The following is a work-in-progress rewrite of the schema originally proposed (still shown below). | ||
+ | < | ||
+ | |||
+ | BEGIN; | ||
+ | |||
+ | CREATE SCHEMA serial; | ||
+ | |||
+ | CREATE TABLE serial.record_entry ( | ||
+ | id BIGSERIAL PRIMARY KEY, | ||
+ | record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | ||
+ | owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | ||
+ | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | source INT, | ||
+ | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | active BOOL NOT NULL DEFAULT TRUE, | ||
+ | deleted BOOL NOT NULL DEFAULT FALSE, | ||
+ | marc TEXT NOT NULL, | ||
+ | last_xact_id TEXT NOT NULL | ||
+ | ); | ||
+ | |||
+ | CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator ); | ||
+ | CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor ); | ||
+ | CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted ); | ||
+ | |||
+ | CREATE RULE protect_mfhd_delete AS ON DELETE TO serial.record_entry DO INSTEAD UPDATE serial.record_entry SET deleted = true WHERE old.id = serial.record_entry.id; | ||
+ | |||
+ | CREATE TABLE serial.subscription ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | start_date TIMESTAMP WITH TIME ZONE NOT NULL, | ||
+ | end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription | ||
+ | record_entry | ||
+ | expected_date_offset INTERVAL DEFAULT NULL | ||
+ | -- acquisitions/ | ||
+ | ); | ||
+ | |||
+ | --at least one distribution per org_unit holding issues | ||
+ | CREATE TABLE serial.distribution ( | ||
+ | id SERIAL | ||
+ | subscription | ||
+ | holding_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | ||
+ | label TEXT | ||
+ | receive_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | receive_unit_template INT REFERENCES asset.copy_template (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | bind_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | bind_unit_template INT REFERENCES asset.copy_template (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | unit_label_base TEXT | ||
+ | unit_label_suffix TEXT | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.stream ( | ||
+ | id | ||
+ | distribution | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.issuance ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | label TEXT DEFAULT NULL, | ||
+ | date_published TIMESTAMP WITH TIME ZONE DEFAULT NULL, | ||
+ | holding_code TEXT DEFAULT NULL, | ||
+ | holding_type TEXT DEFAULT NULL CHECK ( holding_type IS NULL OR holding_type IN (' | ||
+ | holding_link_id INT DEFAULT NULL | ||
+ | -- TODO: add columns for separate enumeration/ | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.item ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | issuance INT NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | stream INT NOT NULL REFERENCES serial.stream (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | unit INT REFERENCES serial.unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | ||
+ | date_expected TIMESTAMP WITH TIME ZONE DEFAULT NULL, | ||
+ | date_received TIMESTAMP WITH TIME ZONE DEFAULT NULL | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.unit ( | ||
+ | label TEXT DEFAULT NULL, | ||
+ | label_sort_key TEXT DEFAULT NULL, | ||
+ | contents TEXT NOT NULL | ||
+ | ) INHERITS (asset.copy); | ||
+ | |||
+ | CREATE TABLE serial.item_note ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | item INT NOT NULL REFERENCES serial.item (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | ||
+ | pub BOOL NOT NULL DEFAULT FALSE, | ||
+ | title TEXT NOT NULL, | ||
+ | value TEXT NOT NULL | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.bib_summary ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | generated_coverage TEXT NOT NULL, | ||
+ | textual_holdings TEXT | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.sup_summary ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | generated_coverage TEXT NOT NULL, | ||
+ | textual_holdings TEXT | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.index_summary ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | generated_coverage TEXT NOT NULL, | ||
+ | textual_holdings TEXT | ||
+ | ); | ||
+ | |||
+ | COMMIT; | ||
+ | |||
+ | -- to be moved to asset schema file | ||
+ | CREATE TABLE asset.copy_template ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | creator BIGINT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | editor BIGINT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | ||
+ | edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | ||
+ | name TEXT NOT NULL, | ||
+ | -- columns above this point are attributes of the template itself | ||
+ | -- columns after this point are attributes of the copy this template modifies/ | ||
+ | circ_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | status INT, | ||
+ | location INT, | ||
+ | loan_duration INT CHECK ( loan_duration IS NULL OR loan_duration IN (1,2,3) ), | ||
+ | fine_level INT CHECK ( loan_duration IS NULL OR loan_duration IN (1,2,3) ), | ||
+ | age_protect INT, | ||
+ | circulate BOOL, | ||
+ | deposit BOOL, | ||
+ | ref BOOL, | ||
+ | holdable BOOL, | ||
+ | deposit_amount NUMERIC(6, | ||
+ | price NUMERIC(8, | ||
+ | circ_modifier TEXT, | ||
+ | circ_as_type TEXT, | ||
+ | alert_message TEXT, | ||
+ | opac_visible BOOL, | ||
+ | floating BOOL, | ||
+ | mint_condition BOOL | ||
+ | ); | ||
+ | |||
+ | </ | ||
==== Schema ==== | ==== Schema ==== | ||
+ | === Problems and Solutions === | ||
Starting with the database schema, here are some notable problems and possible solutions. | Starting with the database schema, here are some notable problems and possible solutions. | ||
- | - Problem: the current schema provides no means of storing prediction information or reception metadata.\\ Solution: expand the issuance table to include such data. < | + | - Problem: the current schema provides no means of storing prediction information or reception metadata.\\ Solution: expand the issuance table to include such data. |
- | | + | - Problem: Multi-issue subscriptions are not easily accommodated.\\ Solution: Add another abstraction table called ' |
- | + creator | + | - Problem: binding unit is too specific for certain use cases.\\ Solution: Replace the ' |
- | + editor | + | === Proposed Schema Relationship Diagram === |
- | + create_date | + | {{: |
- | + edit_date | + | === Proposed Schema === |
- | subscription | + | < |
- | target_copy | + | |
- | location | + | CREATE TABLE serial.record_entry |
- | binding_unit | + | id BIGSERIAL PRIMARY KEY, |
- | label | + | record BIGINT REFERENCES |
- | + date_published | + | owning_lib INT NOT NULL REFERENCES |
- | + date_expected | + | creator INT NOT NULL REFERENCES |
- | + date_received | + | editor INT NOT NULL REFERENCES |
- | + date_claimed | + | source INT, |
- | + is_expected | + | create_date TIMESTAMP WITH TIME ZONE NOT |
- | + is_received | + | edit_date TIMESTAMP WITH TIME ZONE NOT |
- | + is_claimed | + | active BOOL NOT NULL DEFAULT TRUE, |
- | + expected_code | + | deleted BOOL NOT NULL DEFAULT FALSE, |
- | + comment | + | marc TEXT NOT NULL, |
- | );</ | + | last_xact_id TEXT NOT NULL |
- | - Problem: Multi-issue subscriptions are not easily accommodated.\\ Solution: Add another abstraction table called ' | + | ); |
- | | + | |
- | - callnumber | + | CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator |
- | uri | + | CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor ); |
- | | + | CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted ); |
- | | + | |
- | ); | + | CREATE RULE protect_mfhd_delete AS ON DELETE TO serial.record_entry DO INSTEAD UPDATE serial.record_entry SET deleted = true WHERE old.id = serial.record_entry.id; |
+ | |||
+ | CREATE TABLE serial.subscription ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | start_date TIMESTAMP WITH TIME ZONE NOT NULL, | ||
+ | end_date TIMESTAMP WITH TIME ZONE -- interpret NULL as current subscription | ||
+ | -- acquisitions/ | ||
+ | ); | ||
CREATE TABLE serial.distribution ( | CREATE TABLE serial.distribution ( | ||
- | | + | |
- | | + | subscription |
- | | + | -- distribution to record_entry |
- | | + | record_entry |
- | );</ | + | -- owning_lib column allows for a possible ' |
- | - Problem: binding unit should function as an asset in some way.\\ Solution: we could add a link from the ' | + | owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, |
+ | uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | ||
+ | label TEXT | ||
+ | default_call_number BIGINT REFERENCES asset.call_number | ||
+ | -- the copy_transparency table was meant for something else, but may serve us here | ||
+ | -- ultimately, we need some sort of shared/ | ||
+ | -- ideally one which also stores location (rendering the default_location column obsolete) | ||
+ | -- copy_transparency INT REFERENCES asset.copy_transparency (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | default_location INT REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | shelving_unit_label_base TEXT | ||
+ | shelving_unit_label_suffix TEXT | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.shelving_unit ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | label TEXT NOT NULL, | ||
+ | label_sort_key TEXT NOT NULL, | ||
+ | call_number | ||
+ | CONSTRAINT su_label_once_per_dist UNIQUE (distribution, | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.issuance | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | shelving_unit INT REFERENCES serial.shelving_unit | ||
+ | label TEXT DEFAULT NULL, | ||
+ | date_published TIMESTAMP WITH TIME ZONE DEFAULT NULL, | ||
+ | date_expected TIMESTAMP WITH TIME ZONE DEFAULT NULL, | ||
+ | date_received TIMESTAMP WITH TIME ZONE DEFAULT NULL, | ||
+ | copies_expected INT NOT NULL DEFAULT 1, | ||
+ | copies_received INT NOT NULL DEFAULT 0, | ||
+ | has_claims BOOL NOT NULL DEFAULT FALSE, | ||
+ | expected_code TEXT DEFAULT NULL | ||
+ | -- TODO: add columns for separate enumeration/ | ||
+ | ); | ||
+ | |||
+ | -- 1-to-many map of shelving_units (1) to copies (many) | ||
+ | CREATE TABLE serial.shelving_unit_copy_map ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | shelving_unit INT NOT NULL REFERENCES serial.shelving_unit (id), | ||
+ | copy BIGINT NOT NULL UNIQUE REFERENCES | ||
+ | CONSTRAINT su_copy_once UNIQUE (shelving_unit,copy) | ||
+ | ); | ||
+ | CREATE INDEX serial_shelving_unit_copy_map_su_idx ON serial.shelving_unit_copy_map (shelving_unit); | ||
+ | |||
+ | CREATE TABLE serial.issuance_note ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | issuance INT NOT NULL REFERENCES serial.issuance | ||
+ | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | ||
+ | pub BOOL NOT NULL DEFAULT FALSE, | ||
+ | title TEXT NOT NULL, | ||
+ | value TEXT NOT NULL | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.claim ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
+ | issuance INT NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | date_claimed TIMESTAMP WITH TIME ZONE DEFAULT NULL, | ||
+ | notes TEXT DEFAULT NULL | ||
+ | --vendor key? | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.bib_summary ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | generated_coverage TEXT NOT NULL, | ||
+ | textual_holdings TEXT | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.sup_summary ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | generated_coverage TEXT NOT NULL, | ||
+ | textual_holdings TEXT | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE serial.index_summary ( | ||
+ | id SERIAL PRIMARY KEY, | ||
+ | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
+ | generated_coverage TEXT NOT NULL, | ||
+ | textual_holdings TEXT | ||
+ | );</ | ||
==== MFHD Choices ==== | ==== MFHD Choices ==== | ||
The MFHD standard allows for a fair amount of flexibility in some areas. | The MFHD standard allows for a fair amount of flexibility in some areas. | ||
Line 47: | Line 307: | ||
- No compression: | - No compression: | ||
- Partial compression: | - Partial compression: | ||
- | - Full compression (**initial plan**): fully compressed holdings express full coverage information in the least possible space. | + | - Full compression (**initial plan**): fully compressed holdings express full coverage information in the least possible space. |
=== Automatic vs. manual editing === | === Automatic vs. manual editing === | ||
- Fully automatic: the holdings fields are fully managed by the serials interface. | - Fully automatic: the holdings fields are fully managed by the serials interface. | ||
- Fully manual: manually editing holdings with no automation should be possible, but only as a last resort. | - Fully manual: manually editing holdings with no automation should be possible, but only as a last resort. | ||
- Partially automatic (**initial plan**): partial automation will allow a mixture of auto-generated fields and manually edited fields. | - Partially automatic (**initial plan**): partial automation will allow a mixture of auto-generated fields and manually edited fields. | ||
+ | ==== Workflow ==== | ||
+ | The overall workflow and interface still needs to be hammered out, but will need to go something like this: | ||
+ | - Whenever a serial is created (manually or through import), the MFHD record will be consulted to determine the latest held issue and the serial pattern. | ||
+ | - The interface will ask the user to input (or estimate) the last-received date of the serial in order to determine a reasonable expected date (as offset from the chronology date). | ||
+ | - The system will use the MFHD record and functions to populate the issuance table with a set number of predicted issues (eventually based on global or serial-level preference, but perhaps set at a sane level such as 30 to start). | ||
+ | - When the next issue arrives, the user will search for the serial and be presented with a list of predicted issues in the order predicted. | ||
+ | - The system will reinstantiate the received issuance' | ||
+ | - The system will then temporarily reinstantiate the last available issuance for the journal in question, predict another issuance based on this instance, then add the new issuance to the database. | ||
+ | It is also desirable for the user to have the ability to ' | ||
+ | ===== Historical info of potential interest ===== | ||
+ | [[acq: | ||
===== Status Updates ===== | ===== Status Updates ===== | ||
+ | * 2010-05-17: Added historical proto-schema | ||
+ | * 2010-05-03: Schema and diagram updated | ||
+ | * 2010-03-03: Schema proposal update, diagram added | ||
+ | * 2009-11: Initial preview |
acq/serials/basic_predicting_and_receiving.1258580217.txt.gz · Last modified: 2022/02/10 13:33 (external edit)