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/05/11 11:01] – schema fixes 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. | ||
Line 15: | Line 18: | ||
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 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 34: | Line 37: | ||
CREATE TABLE serial.subscription ( | CREATE TABLE serial.subscription ( | ||
- | id SERIAL PRIMARY KEY, | + | id SERIAL PRIMARY KEY, |
start_date TIMESTAMP WITH TIME ZONE NOT NULL, | start_date TIMESTAMP WITH TIME ZONE NOT NULL, | ||
end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription | end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription | ||
- | record_entry | + | record_entry |
- | expected_date_offset INT DEFAULT NULL | + | expected_date_offset INTERVAL DEFAULT NULL |
-- acquisitions/ | -- acquisitions/ | ||
); | ); | ||
- | --one distribution per org_unit holding issues | + | --at least one distribution per org_unit holding issues |
CREATE TABLE serial.distribution ( | CREATE TABLE serial.distribution ( | ||
id SERIAL | id SERIAL | ||
Line 48: | Line 51: | ||
holding_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | holding_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, | ||
label TEXT | label TEXT | ||
- | default_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, | + | receive_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 | + | receive_unit_template INT REFERENCES asset.copy_template |
- | -- ultimately, we need some sort of shared/ | + | bind_call_number BIGINT REFERENCES asset.call_number |
- | -- ideally one which also stores location (rendering the default_location column obsolete) | + | bind_unit_template INT REFERENCES asset.copy_template |
- | -- copy_transparency INT REFERENCES asset.copy_transparency | + | |
- | default_location INT REFERENCES asset.copy_location | + | |
unit_label_base TEXT | unit_label_base TEXT | ||
unit_label_suffix TEXT | unit_label_suffix TEXT | ||
Line 65: | Line 66: | ||
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 74: | ||
date_published TIMESTAMP WITH TIME ZONE DEFAULT NULL, | date_published TIMESTAMP WITH TIME ZONE DEFAULT NULL, | ||
holding_code TEXT DEFAULT NULL, | holding_code TEXT DEFAULT NULL, | ||
- | holding_type TEXT DEFAULT NULL CHECK (holding_type IN (NULL,' | + | holding_type TEXT DEFAULT NULL CHECK ( holding_type IS NULL OR holding_type IN (' |
holding_link_id INT DEFAULT NULL | holding_link_id INT DEFAULT NULL | ||
-- TODO: add columns for separate enumeration/ | -- TODO: add columns for separate enumeration/ | ||
Line 80: | Line 81: | ||
CREATE TABLE serial.item ( | CREATE TABLE serial.item ( | ||
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 101: | Line 102: | ||
id SERIAL PRIMARY KEY, | id SERIAL PRIMARY KEY, | ||
item INT NOT NULL REFERENCES serial.item (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | item INT NOT NULL REFERENCES serial.item (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, | ||
- | creator INT NOT NULL DEFAULT 1, | + | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | ||
pub BOOL NOT NULL DEFAULT FALSE, | pub BOOL NOT NULL DEFAULT FALSE, | ||
Line 129: | Line 130: | ||
); | ); | ||
- | COMMIT;</ | + | 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 144: | 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 199: | 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 228: | Line 262: | ||
id SERIAL PRIMARY KEY, | id SERIAL PRIMARY KEY, | ||
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, | ||
- | creator INT NOT NULL DEFAULT 1, | + | creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, |
create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | ||
pub BOOL NOT NULL DEFAULT FALSE, | pub BOOL NOT NULL DEFAULT FALSE, | ||
Line 237: | Line 271: | ||
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(), | ||
Line 287: | Line 321: | ||
- 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. | - 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 ' | 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-05-03: Schema and diagram updated | ||
* 2010-03-03: Schema proposal update, diagram added | * 2010-03-03: Schema proposal update, diagram added | ||
* 2009-11: Initial preview | * 2009-11: Initial preview |
acq/serials/basic_predicting_and_receiving.txt · Last modified: 2022/02/10 13:34 by 127.0.0.1