This proposal is still in progress, please do not edit yet

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.

Starting with the database schema, here are some notable problems and possible solutions.

1. Problem: the current schema provides no means of storing prediction information or reception metadata.
   Solution: expand the issuance table to include such data.
2. Problem: Multi-issue subscriptions are not easily accommodated.
   Solution: Add another abstraction table called 'distribution' to sit between 'subscription' and the other pieces. It would perform many duties of the current subscription table, leaving subscription to deal with global data and to be the linking point to the acquisitions side. Subsequent tables (binding_unit, issuance, *_summary) will then link to 'distribution' rather than 'subscription'. The distribution table will also provide the linking point to the 'record_entry' (MFHD) table (and exist in a 1-to-1 relation with 'record_entry').
3. 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.

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		NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
	creator		INT		NOT NULL DEFAULT 1,
	editor		INT		NOT NULL DEFAULT 1,
	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	DATE	NOT NULL,
	end_date	DATE	-- interpret NULL as current subscription // or should this be interpreted as an open-ended subscription?
	-- acquisitions/business-side tables link to here
);

CREATE TABLE serial.distribution (
        id              	SERIAL  PRIMARY KEY,
        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,
	uri			INT	REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
        call_number_base	TEXT    DEFAULT NULL,
        call_number_suffix	TEXT    DEFAULT NULL,
	default_location	BIGINT	REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE serial.shelving_unit (
	id			SERIAL	PRIMARY KEY,
	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
        call_number     	BIGINT  UNIQUE 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)
);

CREATE TABLE serial.issuance (
	id		SERIAL	PRIMARY KEY,
	creator		INT	NOT NULL DEFAULT 1,
        editor		INT	NOT NULL DEFAULT 1,
        create_date	TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
        edit_date	TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
	distribution	INT	NOT NULL REFERENCES serial.distribution (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,
        date_published	DATE	DEFAULT NULL,
        date_expected	DATE	DEFAULT NULL,
        date_received	DATE	DEFAULT NULL,
        is_expected	BOOL	NOT NULL DEFAULT TRUE,
        is_received	BOOL	NOT NULL DEFAULT FALSE,
        has_claims	BOOL	NOT NULL DEFAULT FALSE,
        expected_code	TEXT	DEFAULT NULL
);

CREATE TABLE serial.claim (
	id		SERIAL	PRIMARY KEY,
	creator		INT	NOT NULL DEFAULT 1,
        editor		INT	NOT NULL DEFAULT 1,
        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,
	notes		TEXT	DEFAULT NULL
	--vendor key?
);

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
);

The MFHD standard allows for a fair amount of flexibility in some areas. An eventual goal will be to provide configuration switches to meet the preferences of individual libraries, but the initial implementation will focus on a single working model. A few examples are:

1. No compression: When not using compression, MFHD records will have a separate field 863 for every issue. This is generally untenable for anything but the simplest cases (or cases where the serial is unpredictable), and there are no plans to support it at this time.
2. Partial compression: partially compressed holdings will normally have a separate 863 for each bound volume. This physical level of holding information is more or less duplicated in the 'binding_unit' table and unnecessary for expressing holdings from a coverage perspective. As some libraries wish to express this physical-item/holding relationship in their MFHD records, this option is still planned for future development.
3. Full compression (initial plan): fully compressed holdings express full coverage information in the least possible space. Links to physical items are not supported. The initial receiving code will focus on fully compressed holdings as they meet the primary purpose of holdings information (coverage) with the least effort. I wonder if, for shoving data into the MFHD record itself, we should leave it uncompressed, and then just use the summary tables to store the fully compressed summary in the three summary tables? -miker

1. Fully automatic: the holdings fields are fully managed by the serials interface. This approach may seem ideal, but it restricts manual editing by knowledgeable librarians and depends upon a fully implemented and flexible model. It also depends on accommodating historical information (pre-Evergreen holdings) in some way.
2. Fully manual: manually editing holdings with no automation should be possible, but only as a last resort.
3. Partially automatic (initial plan): partial automation will allow a mixture of auto-generated fields and manually edited fields. There is no direct expression of this in the standard, so we will use 'internal note' subfield to tag fields which are under automated control (setting the note to 'EGAUTO' or something similar). Any field lacking the this tag will remain untouched by the automation code.

The overall workflow and interface still needs to be hammered out, but will need to go something like this:

1. 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.
2. 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).
3. 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 generate and store a human-readable label of each issuance's enumeration, chronology, expected date, and a flattened 863 representation. For subscription objects with an end date, we should project the currently purchased run – that is, for a 5-year subscription, project the next 5 years at purchase time – and for open-ended subscriptions we should probably use an OU setting with a sane (if not set) default. Eh? -miker
4. 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 matching issuance will be selected and set as received in some fashion.
5. The system will reinstantiate the received issuance's 863 and add it to the MFHD record, then sort the 'EGAUTO' tagged 863 fields (if necessary) and compress any which represent continuous holdings (if possible).
6. 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.

• 2010-03-03: Schema proposal update, diagram added
• 2009-11: Initial preview