Differences

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

--- acq:serials:basic_predicting_and_receiving [2010/05/03 18:01] – updated schema, updated diagram 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 ======
 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 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 73: / 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 102: / Line 262: @@
 	id		SERIAL	PRIMARY KEY,
 	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(),
 	pub		BOOL	NOT NULL DEFAULT FALSE,
@@ Line 111: / Line 271: @@
 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(),
@@ Line 161: / 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