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 [2010/03/03 16:47] – schema proposal updated 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 === | === Problems and Solutions === | ||
Line 11: | Line 171: | ||
- Problem: binding unit is too specific for certain use cases.\\ Solution: Replace the ' | - Problem: binding unit is too specific for certain use cases.\\ Solution: Replace the ' | ||
=== Proposed Schema Relationship Diagram === | === Proposed Schema Relationship Diagram === | ||
- | (coming soon) | + | {{: |
=== Proposed Schema === | === Proposed Schema === | ||
< | < | ||
Line 18: | Line 178: | ||
id BIGSERIAL PRIMARY KEY, | id BIGSERIAL PRIMARY KEY, | ||
record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | ||
- | owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | + | owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, |
- | creator INT NOT NULL DEFAULT 1, | + | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
- | editor INT NOT NULL DEFAULT 1, | + | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
source INT, | source INT, | ||
create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
Line 38: | Line 198: | ||
CREATE TABLE serial.subscription ( | CREATE TABLE serial.subscription ( | ||
id SERIAL PRIMARY KEY, | id SERIAL PRIMARY KEY, | ||
- | start_date DATE NOT NULL, | + | start_date TIMESTAMP WITH TIME ZONE NOT NULL, |
- | end_date DATE -- interpret NULL as current subscription | + | end_date TIMESTAMP WITH TIME ZONE -- interpret NULL as current subscription |
-- acquisitions/ | -- acquisitions/ | ||
); | ); | ||
CREATE TABLE serial.distribution ( | CREATE TABLE serial.distribution ( | ||
- | id SERIAL | + | id SERIAL |
- | subscription | + | subscription |
-- distribution to record_entry is 1-to-1 | -- distribution to record_entry is 1-to-1 | ||
- | record_entry | + | record_entry |
- | uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | + | -- owning_lib column allows for a possible ' |
- | | + | 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, |
- | default_location BIGINT REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED | + | |
+ | default_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, | ||
+ | -- 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 | ||
+ | -- 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 | ||
); | ); | ||
Line 58: | Line 226: | ||
distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | distribution INT NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
label TEXT NOT NULL, | label TEXT NOT NULL, | ||
- | -- shelving_unit to call_number is 1-to-1 | + | label_sort_key TEXT NOT NULL, |
- | call_number | + | call_number |
- | default_location BIGINT REFERENCES asset.copy_location(id) | + | |
CONSTRAINT su_label_once_per_dist UNIQUE (distribution, | CONSTRAINT su_label_once_per_dist UNIQUE (distribution, | ||
); | ); | ||
Line 66: | Line 233: | ||
CREATE TABLE serial.issuance ( | CREATE TABLE serial.issuance ( | ||
id SERIAL PRIMARY KEY, | id SERIAL PRIMARY KEY, | ||
- | creator INT NOT NULL DEFAULT 1, | + | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
- | editor INT NOT NULL DEFAULT 1, | + | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
Line 73: | Line 240: | ||
shelving_unit INT REFERENCES serial.shelving_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | shelving_unit INT REFERENCES serial.shelving_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
label TEXT DEFAULT NULL, | label TEXT DEFAULT NULL, | ||
- | date_published DATE DEFAULT NULL, | + | date_published TIMESTAMP WITH TIME ZONE DEFAULT NULL, |
- | date_expected DATE DEFAULT NULL, | + | date_expected TIMESTAMP WITH TIME ZONE DEFAULT NULL, |
- | date_received DATE DEFAULT NULL, | + | date_received TIMESTAMP WITH TIME ZONE DEFAULT NULL, |
- | | + | |
- | | + | |
has_claims BOOL NOT NULL DEFAULT FALSE, | has_claims BOOL NOT NULL DEFAULT FALSE, | ||
expected_code TEXT DEFAULT NULL | 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 asset.copy (id), | ||
+ | CONSTRAINT su_copy_once UNIQUE (shelving_unit, | ||
+ | ); | ||
+ | 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 (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.claim ( | CREATE TABLE serial.claim ( | ||
id SERIAL PRIMARY KEY, | id SERIAL PRIMARY KEY, | ||
- | creator INT NOT NULL DEFAULT 1, | + | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
- | editor INT NOT NULL DEFAULT 1, | + | editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), | ||
edit_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, | issuance INT NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
- | date_claimed DATE DEFAULT NULL, | + | date_claimed TIMESTAMP WITH TIME ZONE DEFAULT NULL, |
notes TEXT DEFAULT NULL | notes TEXT DEFAULT NULL | ||
--vendor key? | --vendor key? | ||
Line 120: | 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. | ||
Line 129: | Line 316: | ||
- 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. | - 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 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). | + | - 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. | - 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 reinstantiate the received issuance' | ||
Line 135: | Line 322: | ||
It is also desirable for the user to have the ability to ' | It is also desirable for the user to have the ability to ' | ||
- | ===== Status Updates | + | ===== Historical info of potential interest |
+ | [[acq: | ||
+ | ===== 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.1267652825.txt.gz · Last modified: 2022/02/10 13:33 (external edit)