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 revisionNext revisionBoth sides next revision | ||
acq:serials:basic_predicting_and_receiving [2010/05/10 20:50] – new schema dbw2 | acq:serials:basic_predicting_and_receiving [2010/05/21 16:45] – dbw2 | ||
---|---|---|---|
Line 15: | Line 15: | ||
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 34: | ||
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 |
- | -- subscription to record_entry is now 1-to-1, should record_entry even exist any more? | + | record_entry |
- | record_entry | + | expected_date_offset INTERVAL DEFAULT NULL |
- | expected_date_offset INT 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 49: | Line 48: | ||
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 66: | Line 63: | ||
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 74: | Line 71: | ||
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 81: | Line 78: | ||
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 94: | Line 91: | ||
CREATE TABLE serial.unit ( | CREATE TABLE serial.unit ( | ||
- | id SERIAL PRIMARY KEY, | ||
label TEXT DEFAULT NULL, | label TEXT DEFAULT NULL, | ||
label_sort_key TEXT DEFAULT NULL, | label_sort_key TEXT DEFAULT NULL, | ||
Line 103: | Line 99: | ||
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 131: | Line 127: | ||
); | ); | ||
- | 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 146: | Line 175: | ||
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 201: | Line 230: | ||
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 230: | Line 259: | ||
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 239: | Line 268: | ||
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 289: | Line 318: | ||
- 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