Differences

This shows you the differences between two versions of the page.

--- acq:serials:basic_predicting_and_receiving [2010/03/15 10:38] – miker
+++ 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 ======
 FIXME This proposal is still in progress, please do not edit yet FIXME
+Okay - but let's keep a link to the [[https://docs.google.com/drawings/view?id=196Cyl9CAIzZ33cOVt0AphvUSbAwc9kfRGRB5Nz-5tTI&hl=en&pli=1|latest schema diagram]] handy, at least.
 ===== 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.  This proposal will focus on fleshing-out the database schema and defining how the MFHD record will hook in.
 ===== Details =====
+==== Proposed Schema - Major Revision ====
+The following is a work-in-progress rewrite of the schema originally proposed (still shown below).  It is changing rapidly and may contain some syntax errors!  Primary changes include a de-emphasis of MFHD records and a more direct representation of individual issues.
+<code>DROP SCHEMA serial CASCADE;
+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    	BIGINT		REFERENCES serial.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+	expected_date_offset	INTERVAL	DEFAULT NULL
+	-- acquisitions/business-side tables link to here
+);
+--at least one distribution per org_unit holding issues
+CREATE TABLE serial.distribution (
+        id              	SERIAL  PRIMARY KEY,
+        subscription    	INT     NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+	holding_lib		INT	REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+        label			TEXT    NOT NULL,
+	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    DEFAULT NULL,
+        unit_label_suffix	TEXT    DEFAULT NULL
+);
+CREATE TABLE serial.stream (
+        id             	SERIAL  PRIMARY KEY,
+        distribution   	INT     NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+);
+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 ('basic','supplement','index') ),
+        holding_link_id	INT	DEFAULT NULL
+	-- TODO: add columns for separate enumeration/chronology values
+);
+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/creates
+	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,2),
+	price		NUMERIC(8,2),
+	circ_modifier	TEXT,
+	circ_as_type	TEXT,
+	alert_message	TEXT,
+	opac_visible	BOOL,
+	floating	BOOL,
+	mint_condition	BOOL
+);
+</code>
 ==== Schema ====
 === Problems and Solutions ===
@@ Line 11: / Line 171: @@
   - Problem: binding unit is too specific for certain use cases.\\ Solution: Replace the 'binding_unit' table with a more flexible 'shelving_unit' table which can contain any number of single issues and bound volumes.
 === Proposed Schema Relationship Diagram ===
-{{:acq:serials:serials_schema_diagram.gif|Serials Schema Diagram}}
+{{:acq:serials:serials_schema_diagram_v3.gif|Serials Schema Diagram}}
 === Proposed Schema ===
 <code>CREATE SCHEMA serial;
@@ Line 18: / Line 178: @@
 	id		BIGSERIAL	PRIMARY KEY,
 	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,
 	create_date	TIMESTAMP WITH TIME ZONE	NOT NULL DEFAULT now(),
@@ Line 38: / Line 198: @@
 CREATE TABLE serial.subscription (
 	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 // or should this be interpreted as an open-ended subscription?
+	end_date	TIMESTAMP WITH TIME ZONE	-- interpret NULL as current subscription
 	-- acquisitions/business-side tables link to here
 );
 CREATE TABLE serial.distribution (
-        id              	SERIAL  PRIMARY KEY,
+        id              		SERIAL  PRIMARY KEY,
-        subscription    	INT     NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+        subscription    		INT     NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 	-- distribution to record_entry is 1-to-1
-        record_entry    	BIGINT  UNIQUE REFERENCES serial.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+        record_entry    		BIGINT  UNIQUE REFERENCES serial.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
-	uri			INT	REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+	-- owning_lib column allows for a possible 'global' record_entry (e.g. record_entry per consortium, distribution per library)
-        call_number_base	TEXT    DEFAULT NULL,
+	owning_lib			INT	REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
-        call_number_suffix	TEXT    DEFAULT NULL,
+	uri				INT	REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
-	default_location	BIGINT	REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED
+        label				TEXT    NOT NULL,
+	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/global copy template (current copy templates are stored as a user setting),
+	-- 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    DEFAULT NULL,
+        shelving_unit_label_suffix	TEXT    DEFAULT NULL
 );
@@ Line 58: / Line 226: @@
 	distribution		INT	NOT NULL REFERENCES serial.distribution (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 	label			TEXT	NOT NULL,
-	-- shelving_unit to call_number is 1-to-1
+	label_sort_key		TEXT	NOT NULL,
-        call_number     	BIGINT  UNIQUE REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+        call_number     	BIGINT  REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
-	default_location	BIGINT	REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED,
 	CONSTRAINT su_label_once_per_dist UNIQUE (distribution, label)
 );
@@ Line 66: / Line 233: @@
 CREATE TABLE serial.issuance (
 	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(),
         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,
 	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,
-        is_expected	BOOL	NOT NULL DEFAULT TRUE,
+        copies_expected	INT	NOT NULL DEFAULT 1,
-        is_received	BOOL	NOT NULL DEFAULT FALSE,
+        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/chronology values?
+);
+-- 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,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 (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 (
 	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(),
         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	DATE	DEFAULT NULL,
+        date_claimed	TIMESTAMP WITH TIME ZONE	DEFAULT NULL,
 	notes		TEXT	DEFAULT NULL
 	--vendor key?
@@ Line 134: / 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.
 It is also desirable for the user to have the ability to 'regenerate all predictions' for any given serial.
+===== Historical info of potential interest =====
+[[acq:serials:proto_schema_geometry|Proto-schema using geometric datatypes for issuance tracking and indexing]]
 ===== 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