Narrative Description for Sprint 1 Pre-Order Record Logical Data Model Version 2

LR 1/31/2011 Update: Added KFS mapping information based on a variety of sources

KFS ERDs on the KFS wiki site.
mapping documents that Jonathan Keller provided
Requisition Functional Specifications v 1.5document on the KFS wiki
Functional Field Description Lookup on the KFS Test Drive site.
Title/Bib data remains a question mark – am still unsure if we will store this data relationally – and if so – how we want to format it (esp. the indicators and subfield tagging) or if we are going to try and create/load this data as XML documents (either MARC or ONIX?)

General Information

2 Primary entities: Pre-Order and the associated Title (i.e., bibliographic resource) that is under consideration for ordering
I drafted this based on
- attributes listed by Stuart Miller (Team Darby) in the Sprint 1 Item 3 Library Staff Request (User Story 7103)
- the Order entity and attributes identified by the OLE Data Modeling subgroup https://docs.google.com/a/kuali.org/document/d/1_hKPHChb6ceoEzdUDxFubBPymu6lEJJcSm-HkCyODr8/edit?hl=en
See also full Kuali OLE Conceptual Model created by the Data Model subgroup for a fuller sense of how bibliographic and authority entities relate to each other.
The draft LDM was drawn using “Gliffy” on the Kuali OLE wiki – and cannot create ddls.

Pre-Order Entity - This entity appears to map the KFS Requisition Entity -

Attributes

Pre-Order ID

Primary Key

KFS Mapping

DB Table Name	DB Column Name: Type (Length)	Required	Description	Controlled by
PUR_REQS_T	REQS_ID: DECIMAL(8,0)	Yes – auto generated	The system-generated Requisition Number.[1	#_ftn1] This is an identifying number assigned to this requisition, and can be used for queries specific to the Purchasing/AP module of the KFS.[2	#_ftn2]

Pre-Order Date ID

Links to date type and date
Date Types = created, ordered, mailed, claimed, received, renewed (to be addressed by workflow?)
- Types taken from the DM subgroups “Order” entity description

KFS Mapping: This type of information appears to be associated with the “Route Log” – workflow status and progress within Kuali Workflow. Not sure how to map this to KFS.

Request Type ID

Request Types (per Stuart) = Purchase, License – libraries can add more data values

KFS Mapping: Does not appear to map to current KFS attribute.

Extended attribute needed and needs to be controlled by a “maintenance document” for “Request Type”. The initial values would be ‘Purchase’ and ‘License’.

Presumably it would be a 1 to 1 link to PUR_REQS_ITM

KFS Mapping: Extended Attribute

DB Table Name	DB Column Name: Type (Length)	Mandatory	Description	Controlled by
PUR_REQS_ITM	NEW REQ_TYP_ID DECIMAL(8,0)	N	Type of library request.	NEW Request Type “maintenance document”

Request Source Type ID

Request Source Types (per Stuart) = Open URL, in-house – libraries should also be able to add more data values

KFS Mapping: Extended Attribute

DB Table Name	DB Column Name: Type (Length)	Mandatory	Description	Controlled by
PUR_REQS_ITM	NEW REQ_SRC_TYP_ID DECIMAL(8,0)	N	Source of the requisition/pre-order request.	NEW Request Source Type “maintenance document”

Request Source URL

An Open Source URL can be entered/stored here (?) – per Stuart (and per Sprint 1, Item 2 Create Manual Request (User Story 2650) https://docs.google.com/a/kuali.org/document/d/1T9RDGtkPC_qNgV1cn4Z0t_xKRpIL0NdckkjoEvky0EE/edit?hl=en#)

KFS Mapping: Extended Attribute

DB Table Name	DB Column Name: Type (Length)	Mandatory	Description	Controlled by
PUR_REQS_ITM	NEW REQ_SRC_URL VARCHAR (700) ??	N	The OpenURL source of the requisition/pre-order request.	NEW Request Source Type “maintenance document”

Title ID

Link to a Title (aka Bib document) in the Document Store? Could be in MARC XML, MODS, FRBR, Dublin Core, EAD?
Link to Title Entity with some info and then link to the Document Store?

KFS Mapping: Extended Attribute. We may also want to populate some of the Title information in the ITM_DESC VARCHAR(4000) attribute – or figure out how we will display the bibliographic information in the Pre-Order/Requisition document.

DB Table Name	DB Column Name: Type (Length)	Mandatory	Description	Controlled by
PUR_REQS_ITM		Y	Link to the requested resource.

Agent ID

Agent (Vendor/Publisher) supplying the pre-ordered Title
This area needs lots of work - the DM Subgroup wanted a recursive relationship for Agents – where one Agent is the parent of another and where the Agents can be typed, e.g., - Vendor, Publisher, Consortia, Libraries, Institutions (?), Individuals within a specific organization (ex: technical support contact). I have not fully fleshed out the Agent entity but have, for now, only included an ID and a name.
Need to figure if we can re/use the Vendor database in KFS
Question: Would we consider using the Authority.Name entity to capture Vendor information?

KFS Mapping: The PURS_REQS table in KFS does not have a Vendor Number attribute, yet the Vendor number does appear on the Requisition Data Entry screen on the KFS Test Drive system. Users are allowed to select a vendor from the vendor table/database, but the data is pulled in and may be changed as needed. The vendor information is not linked back to via a foreign key.

DB Table Name	DB Column Name: Type (Length)	Req.	Description[3	#_ftn3]	Controlled by
PUR_REQS	VNDR_NM: VARCHAR(45)	N	Suggested Vendor: This field is used to enter the name of a vendor that is able to fulfill this requisition. If selected from the vendor table it will populate other fields in this tab based on information already on file in KFS for this vendor.	Vendor Module/Namespace
PUR_REQS	VNDR_DTL_ASND_ID	N	Vendor detail assigned identifier.[4	#_ftn4] Vendor Number: This field is used to define the vendor number when a suggested vendor is selected form the vendor table. (auto generated based on selected vendor name)	Vendor Module/Namespace
PUR_REQS	VNDR_LN1_ADDR: VARCHAR(45)	N	Address 1: This attribute is used to define the first line of the address for the suggested vendor. If a vendor has been selected from the vendor table this field will automatically be populated. If you wish to select a different vendor address than the default, use the lookup to choose from all addresses entered for this vendor.	Vendor Module/Namespace
PUR_REQS	VNDR_LN2_ADDR: VARCHAR(45)	N	Address 2 This field is used to define the second line of the address for the suggested vendor. If a vendor has been selected from the vendor table this field will automatically be populated.	Vendor Module/Namespace
PUR_REQS	VNDR_CTY_NM: VARCHAR(45)	N	City This is the suggested vendor’s City. If a vendor has been selected from the vendor table this field will automatically be populated.	Vendor Module/Namespace
PUR_REQS	VNDR_ST_CD: VARCHAR(2)	N	State This is the suggested vendor’s State. If a vendor has been selected from the vendor table this field will automatically be populated.	Vendor Module/Namespace
PUR_REQS	VNDR_PSTL_CD: VARCHAR(20)	N	Postal Code This is the selected vendor’s Postal Code. If a vendor has been selected from the vendor table this field will automatically be populated.	Vendor Module/Namespace
PUR_REQS	VNDR_CNTRY_CD: VARCHAR(2)	N	Country This is the selected vendor’s Country. If a vendor has been selected from the vendor table this field will automatically be populated.	Vendor Module/Namespace
PUR_REQS	VNDR_RSTRC_IND: VARCHAR(1)	N	Vendor restricted indicator[5	#_ftn5]	Vendor Module/Namespace
PUR_REQS	VNDR_PHN_NBR: VARCHAR(45)	N	Phone Number This is the selected vendor’s phone number.	Vendor Module/Namespace
PUR_REQS	VNDR_FAX_NBR: VARCHAR(40)	N	Fax Number This is the selected vendor’s fax number. If a vendor has been selected from the vendor table this field may automatically be populated.	Vendor Module/Namespace
PUR_REQS	VNDR_CUST_NBR: VARCHAR(30)	N	Customer Number This field is used to define the customer number that the vendor has assigned to the institution or.	Vendor Module/Namespace
PUR_REQS	VNDR_CONTR_GNRTD_ID: DECIMAL(10,0)	N	Vendor contract generated ID[6	#_ftn6]	Vendor Module/Namespace
PUR_REQS	VNDR_NTE_TXT: VARCHAR(255)	N	Notes to Vendor This field is used to include any notes you wish the vendor to see on the Purchase Order.	Vendor Module/Namespace
PUR_REQS	ALTRNT_1_VNDR_NM: VARCHAR(45)	N	Vendor Name (1-5) These fields are used to identify the additional suggested vendors that may be able to fulfill this requisition.	Vendor Module/Namespace
PUR_REQS	ALTRNT_2_VNDR_NM: VARCHAR(45)	N	Vendor Name (1-5) These fields are used to identify the additional suggested vendors that may be able to fulfill this requisition.	Vendor Module/Namespace
PUR_REQS	ALTRNT_3_VNDR_NM: VARCHAR(45)	N	Vendor Name (1-5) These fields are used to identify the additional suggested vendors that may be able to fulfill this requisition.	Vendor Module/Namespace
PUR_REQS	ALTRNT_4_VNDR_NM: VARCHAR(45)	N	Vendor Name (1-5) These fields are used to identify the additional suggested vendors that may be able to fulfill this requisition.	Vendor Module/Namespace
PUR_REQS	ALTRNT_5_VNDR_NM: VARCHAR(45)	N	Vendor Name (1-5) These fields are used to identify the additional suggested vendors that may be able to fulfill this requisition.	Vendor Module/Namespace

User ID

Multiple “users” can be associated with the Pre-Order – Requestor, Selector (can be one and the same user with different roles) – workflow?

KFS Mapping: Presumably this is handled via KIM – not sure on how to model this.

Note ID

Both Stuart and DM Subgroup want to be able to associate multiple “processing” notes with the pre-order record

KFS Mapping: Notes associated with Requisitions in KFS are associated with the “Route Log” – workflow status and progress within Kuali Workflow. Not sure how to map this to KFS. Multiple notes may be attached to the Requisition, but they are not “typed”, e.g., not noted as “Patron Note”, “Processing Note”, etc. Is a general note field sufficient?

Unit Price

Stuart noted price needs to be captured, DM subgroup calls this “unit price”

KFS Mapping:

DB Table Name	DB Column Name: Type (Length)	Mandatory	Description[7	#_ftn7]	Controlled by
PUR_REQS_ITM	ITM_UNIT_PRC: DECIMAL(19,4)	N	The cost per unit for the item or service on this line.

Price Source

From Stuart – presumably just a note field to indicate from where the requestor/selector got the price information.

KFS Mapping: In the current KFS implementation, there is only a displayed value of “Estimate”. However, in OLE, the functional requirement seems to be that the user wants to indicate where the pricing came from. With that in mind, we need to know the code possibilities, if they can add new codes, and they will probably also want the PO_CST_SRC_DESC field available for display (and update?), too.

DB Table Name	DB Column Name: Type (Length)	Req.	Description[8	#_ftn8]	Controlled by
PUR_REQS	PO_CST_SRC_CD: VARCHAR(4)	N	This field indicates how the pricing on the requisition was determined.	PUR_PO_CST_SRC_T (This table is in the Vendor module/namespace)

Number of Copies

Indicate the number of copies requestor/selector wants to order

KFS Mapping:

DB Table Name	DB Column Name: Type (Length)	Mandatory	Description[9	#_ftn9]	Controlled by
PUR_REQS_ITM	ITM_QTY: DECIMAL(11,2)	N	This is the quantity of the item. (Not used when Item Type is “Service”.)

Scope of Order

Per the DM subgroup this would indicate: one time vs. continuing, specific volumes

KFS Mapping: Notes associated with Requisitions in KFS are associated with the “Route Log” – workflow status and progress within Kuali Workflow. Not sure how to map this to KFS. Multiple notes may be attached to the Requisition, but they are not “typed”, e.g., not noted as “Patron Note”, “Processing Note”, etc. Is it acceptable to just include the Scope Note in this general note field?

Title Entity

Note: Not entirely sure how to model this as the attributes could/would all be included within the XML document that describes the Title.

Attributes

Title ID – Primary Key
Title Type ID

o I added this with a link to an authority list/pick list with the possible values of Creation-level, Edition-level, Collection-Level, Holdings, Item, Ad-hoc – which are all bibliographic types per the OLE Conceptual Model created by the DM subgroup

Title

o This equates to the MARC 245 field (and its indicators and subfields)

o If this data is stored relationally, as well as in a tagged XML field – how would want to format it? Based on MARC guidelines? With indicators, subfield codes? Or, would we parse out the MARC 245 field relationally? See http://www.loc.gov/marc/bibliographic/bd245.html

Edition

o In MARC edition information maps to the 250 field (and its indicators and subfields)

o See http://www.loc.gov/marc/bibliographic/bd250.html

Date of Publication (Per Stuart)

o I think the associated MARC 008 field positions 07-10 (Date 1) and 11-14 (Date 2) See http://www.loc.gov/marc/bibliographic/bd008.html

Place of Publication

o Place of Publication – usually stored in MARC 008 positions 15-17. See http://www.loc.gov/marc/bibliographic/bd008.html

o Question: Could this be pulled from the Agent (Publisher) data? Or, are there multiple places of publication and the library staff would want to select/enter the appropriate one?

Physical Description (Per Stuart)

o There are multiple MARC 3XX fields that traditionally capture physical descriptive information – not sure how we capture this information at the Pre-order stage if we intend to store it relationally – presumably the appropriate XML tagging would be used in a stored XML descriptive document.

Agent ID

o Agent (Publisher) of the pre-ordered Title

o Not sure if Place of Publication would be pulled from here or not.

o See previously raised questions on the Agent entity.

Authority.Name ID

o There are many “names” that could be associated with a Title in a wide variety of roles, i.e., author, subject, and added entry (contributor and the contribution role) – those roles are designated as such by MARC via the (1XX), (6XX), (7XX) fields respectively.

o Names can be Person, Corporate, Meeting, Uniform Title – and designated as such by the X00, X10, X11, and X30 MARC fields, respectively

Authority.Places ID

o Geographic authorities can either be subjects of a Title or can indicate the place of publication (per the Conceptual model)

Authority.Concept/Object ID

o These are (generally) the topical subjects of the Title

Format ID

o Traditionally, this information is conveyed via the MARC 008 fixed length field. Within 008 data elements are positionally defined by type of material. Descriptions of the elements defined for field 008 positions 18-34 are in seven separate sections corresponding to the following type of material configurations: Books (BK), Computer Files (CF), Maps (MP), Music (MU), Continuing Resources (CR), Visual Materials (VM), and Mixed Materials (MX). See http://www.loc.gov/marc/bibliographic/bd008.html for more information.

o I think what Stuart wants is to be able to associate a Format Type with a Title during the Pre-Order process. He wants an authority/pick list with the MARC format types: Books (BK), Computer Files (CF), Maps (MP), Music (MU), Continuing Resources (CR), Visual Materials (VM), and Mixed Materials (MX). But, he also wants to be able to add locally defined formats to that authority/pick list as well.

Standard Number Type ID

Per Stuart – need to know the type of standard number associated with the Title that is being pre-ordered
Standard Number Type Values = ISBN, ISSN - libraries should be able to add more data values

Standard Number

Title Recursive Relationship

A titles can be related to another Title, e.g., as a parent Series and well as in the FRBR types of relationships Collection, Creation, Edition, etc.

[1|#_ftnref1] Functional Field Description Lookup on the KFS Test Drive site.

[2|#_ftnref2] Requisition Functional Specifications v 1.5 document on the KFS wiki

[3|#_ftnref3] Requisition Functional Specifications v 1.5document on the KFS wiki

[4|#_ftnref4] Functional Field Description Lookup on the KFS Test Drive site.

[5|#_ftnref5] This attribute is included on the PUR_REQS table in KULDBA_PURAP_Tables.pdf, but is not found on the Requisition data entry screen on the test drive system, nor in the Requisition Functional Specifications.

[6|#_ftnref6] Ibid

[7|#_ftnref7] Requisition Functional Specification v1.5 document on the KFS wiki

[8|#_ftnref8] Requisition Functional Specification v1.5 document on the KFS wiki

[9|#_ftnref9] Requisition Functional Specification v1.5 document on the KFS wiki