|This page contains information about data migration ETL work for HAVRC's deployment of CollectionSpace|
Migration of HAVRC legacy data is dependent upon the ability to create and deploy a number of authority files within the CollectionSpace environment. Nearly every table in the current HAVRC system is dependent on data from one or more of these authorities. All authorities must be able to create both hierarchical and associative relationships between records in the same authority. Both preferred and non-preferred terms should be searchable and display in predictive text handling lists (see Wireframes - Predictive Text ) Once these basic authority structures have been developed and instances are available for use, the first step in the data migration process should be the migration of existing authority data into these structures. Since many of the values that populate the HAVRC authorities (such as personal and corporate Names, Place names, Work types, Styles, Periods, Materials and Techniques, etc.) were derived from the licensed Getty vocabularies (http://www.getty.edu/research/tools/vocabularies/index.html), an ideal situation going forward would be the ability to access the online version of these thesauri from within the CSapce application via Web services (see http://www.getty.edu/research/tools/vocabularies/obtain/download.html) for both searching and data retrieval. This would eliminate the need to maintain the entire thesaurus structure within the application and take the advantage of continuing product development by the Getty.
The HAVRC Names table currently contains 7289 names. Each name is typed with one of 19 name types. HAVRC alt_name currently contains 75 records representing alternate names, also typed, that are linked to records in the names table. Both name and alt_name records will map to the person and organization authority. Donor names, staff names, and patron names exist in the donor, staff and patron tables and should be mapped into the CSpace Name Authority, preferably as separate vocabularies from artist names. Donor and staff names will have to be parsed into first and last names.
- HAVRC Names table combines Name and role in one record, resulting in multiple records for the same artist name with different roles associated (e.g. Rubens as painter, designer, architect, sculptor, printmaker, etc.) Multiples need to be identified so that the name may be migrated once, with all associated roles mapped as multiple values in the repeatable Occupation field.
- Name block needs to be a repeating group to allow for the inclusion of alternate names. (fixed in Ver. 2.4)
- Additional fields needed for repeating data source block for name data source and source_id (ULAN id, LCNAF id, etc.). (fixed in Ver. 2.4)
- Source names should ultimately be derived from the Brief Citation field of the Citation Authority.
- CSpace birth date and death date fields need to be structured dates rather than calendar dates.
- Additional (structured) date fields needed to record activity dates. Best approach would be a repeating activity dates group with earliest_active, latest active, and activity place. (See wireframe attached to JIRA CSPACE-4996)
- Additional Qualifier field needed in Production Person Group in the Catalog record to hold "School of", "Circle of", "Follower of", etc.
- May need a local field to hold HAVRC name_ids to aid migration?
|HAVRC Table Name||HAVRC Field Name||HAVRC Name Type||CSpace Field Name||Notes|
||preferred_name||personal||Display name||Preferred names with qualifiers included such as "(File with)", "(After)", or "(Attr)" need to be isolated. These records should map the name, qualifier, and role to the catalog record of the work they are linked to as a production person (e.g. Production Person = "Rubens, Peter Paul", Production Role = "painter" Qualifier = "After"|
|| Determines whether the data from this record goes into the Person or Organization Authority (see name types mapping document)
|name_abbrev||personal||This field was used to save real estate on slide labels. While no longer needed, some data cleanup is required here. Some of the values in this field are alternate names|
|qualifier||personal||Addition to name||Data cleanup required here. Many of these values (Circle of, follower of, workshop of) denote unnamed individuals whose work displays affinities with those of the named artist.|
|| This field currently contains 26 unique values and needs some cleanup
|ulan_id||New field needed for names derived from ULAN. Could be part of a source + source_detail group (Source="ULAN" and Source_detail="500002921")|
|call_num_cd||Call number code associated with analog collection. Recommend keeping this is a local field for now. May be useful for sorting all the attributeds, circle of, school of names. Perhaps a local field could be added to the person and org authorities to hold this legacy data if pre-migration data cleanup is not possible.|
|first_active_date||personal||New field group needed here made up of structured date + place name|
|lang_name||personal/organization||Need new field to record language of the name (Fixed in Ver. 2.4)|
Place names in HAVRC are largely derived from the Getty Thesaurus of Geographic Names (TGN) and are stored in the Locations table and in several views (www_locations, xdb_loc_qvnc, xdb_loc_acv, xdb_loc_qvoj). Unlike the AAT and ULAN vocabularies (which was loaded into Sybase tables and made available within the HAVRC application), the values derived from the TGN are cut and pasted by cataloging staff from the public website maintained by the Getty Vocabulary Program (http://www.getty.edu/research/tools/vocabularies/tgn/) into the Locations table. Hierarchical relations are created one by one by cataloging staff. Because all the Getty vocabularies are continually growing and changing, and data input errors are inevitable, some of this place name data may be incorrect or out of date. Unlike the AAT and ULAN data, the TGN data has never been refreshed or updated annually from Getty-provided materials. Therefore, it may be advisable to run a verification check against the most current TGN data to identify and correct data errors before importing into CSpace.
A certain amount of data massage may be necessary on import to create consistent qualified names and display names out of HAVRC legacy data. For example, homonyms could have qualified names that include the parent name appended in parentheses, e.g. "Cambridge (Massachusetts)" vs "Cambridge (England)". Display names should have the qualified name plus name type, e.g. "Cambridge (Massachusetts, inhabited place)". Display Names for place names that have no homonyms and no qualifiers could be comprised of place name + place name type, e.g. "India (nation)". The place name qualifiers are necessary for disambiguation when place names are presented in a term completion picklist with no other information. The display names are likely to get long and unwieldy unless abbreviations are used for the parent terms, e.g. "Cambridge (MA, inhabited place)", but these abbreviations have not been entered consistently and many would have to be added or created on export.
Data Cleanup Needed
- Isolation of HAVRC location terms that already include the qualifier in the location term string (e.g. "Castello (sestiere)" ) from those that include alternate names (e.g. "Abu Sunbul (Abu Simbel)"). Currently there are 205 place name records that include something in parentheses following the place name.
- Parsing of HAVRC location terms that include alternate place names in the string, e.g. "Abu Sunbul (Abu Simbel)" to create a preferred name and an alternate name.
|HAVRC Table Name||HAVRC Field Name||CSpace Information Group||CSpace Field Name||Notes|
|Locations||loc_id|| Name Authority Body
|| loc_term + loc_type_descr
|| Place names
|| Qualified name
|| loc_term + "(" + loc_type_descr + ")" - Example Cambridge (Massachusetts)
|www_locations|| loc_term + loc_type_descr
||Name Authority Body||Display_name||Display name should be comprised of Qualified name + name type. Some place names have alternate names (enclosed in parentheses) included in the loc_term field. These need to be parsed and the primary place name entered into the preferred name field and alternate place name entered into a repeating non-preferred name element.|
||Place names||Name||Place names without alternate names or qualifiers|
|| Place names
|| Name type
|Locations||loc_abbrev||Place names||Place Name Abbreviation||Not all location have abbreviations|
|Locations||term_type||Place names||Status||Controlled list (c=Current, h=Historical, b=Both)|
|xdb_loc_qvnc||parent_term||Hierarchy||Broader Context||Hierarchy links made after all location terms have been migrated|
|Location||latitude||Locality Information, Georeference Information||Verbatim latitude, Decimal latitude|
|Location||longitude|| Locality Information,
|Verbatim longitude, Decimal longitude|
|Location||source_abbrev||Place Authority body, Place names||Source, Source||(In repeating term block in Ver. 2.4)|
|tgn_id||Place names||Source ref_id||Only for records with TGN in source_abbrev (In repeating term block in Ver. 2.4)|
|loc_notes|| Place Authority body
|Coordinate system||All records should have "Latitude and Longitude" value|
|Location||Latitude + longitude||Locality information||Verbatim coordinates||Concatenate latitude +" X " + longitude|
Because the HAVRC collection consists of images of objects, rather than the objects themselves, it is important for HAVRC staff to document the sources from which data about the object(s) depicted in the images are derived. Data is gathered from a variety of (primarily) secondary sources, and nearly every table in the HAVRC application has two fields to reference these sources--Source abbreviation and source type. While most cited sources are print publications (books, journals, exhibition catalogs and ephemera), data is also gathered from a variety of other sources, including websites, documentation provided by image vendors, information found on analog slide and photo labels, faculty input (oral history), etc. Details about HAVRC sources are recorded in one of 7 source type tables: Books, Journals, Vendor Images, Donors/Duplicates, Electronic, Exhibition, and Other Sources. Each table has a different field structure to hold data relevant to that source type. Data in these tables will all map to a common structure in the CSpace Citations Authority. The only field they all share in common is a field called source_abbreviation. It is this source_abbreviation field + the source type that links the information about the source to the data recorded in other tables.
Initial migration might simply map the source_abbreviation and source_id into the Brief Citation and Local Source Id fields in the Citation Authority, with the rest to follow based on the source_id. Full citations are created for the nightly snapshot of data for the HAVRC website and may be migrated from www_source_citations to the CollectionSpace Source Full Citation field for all legacy sources. Once the legacy source data is migrated, these full citations will have to be either entered by hand, or copied and pasted from a citation string generated from an online service such as WorldCat or Refworks for all new citation authority records. Data in the 7 source type tables should be mapped as follows to the UCB Proposed Citations Authority Schema.
- Additional field to hold legacy source_id to aid migration?
HAVRC Table Name HAVRC Field Name
CSpace Information Group CSpace Field Name Notes
Source Citation Autuhority source_abbrev Citation Information Brief Citation source_id Citation Information Local Source Id (Custom field) Either create a custom field or map to Other Resources-> Source Other Identifier with a Source Other Identifier type="legacy_source_id" source_type Citation Information Source Type b=book, j=journal, v=vendor, d=donor/duplicate, m=electronic media, e=exhibition, o=other. Note: other suggested source types are more granular but these values will speed the initial data migration. www_source_citations cite Citation Information Source Full Citation The cite field in this table contains a fully formatted citation for each accession (including page and plate numbers). SQL code used to populate this field for the website data (format_cdwa_source?) could be edited to populate this field with legacy data (i.e. edit the part of the code that adds page and plate numbers and put that page&plate number data in the source detail field of the media handling record for that accession. Full citation for new citation records added post-migration will have to be constructed manually for this field or generated and pasted from an external application such as Refworks. Book Citation Authority For all those source records where source_type=b source_id Citation Information Local Source Id (Custom field)
Either create a custom field or map to Other Resources-> Source Other Identifier with a Source Other Identifier type="legacy_source_id" author Person/Org Authority Display Name Many HAVRC book records contain multiple authors in the author field. This data will need to be parsed and imported as separate names and entered into the Person Authority, or separated manually post-migration. book_title Title and Issue Information Source Title 447 HAVRC book titles contain subtitle or section title info. Data will either need to be parsed and imported separately, or done so manually post-migration. series_title Title and Issue Information
Source Title Create new Citation record for the series title with Source Type="Series". Link as broader context in Hierarchical Relationships to record where series title appears in HAVRC series_title field. edition Title and Issue Information
Source Edition publish_location Publisher Information Source Publication Place publisher_name Publisher Information
Source Publisher publish_year Title and Issue Information
Source Publication Year book_call_num Resource Identifiers Source Other Identifier Map with "Call number" in Source Other Identifier Type book_volume Title and Issue Information Source Volume book_abbrev From Source table (see above)? book_comments Publisher Information Remarks ISBN Resource Identifers Source Other Identifier Map with "ISBN" in Source Other Identifier Type date_entered Citation Information Source Data Capture Date Journal Citation Authority For all those source records where source_type=j source_id Citation Information Local Source Id (Custom field)
Either create a custom field or map to Other Resources-> Source Other Identifier with a Source Other Identifier type="legacy_source_id" journal_title
Title and Issue Information
Title and Issue Information
Source Volume journal_issue Title and Issue Information
Source Issue journal_year Title and Issue Information
Source Publication Year journal_month Title and Issue Information
No real place for this in new schema unless added to Source Issue. Example: For Art Bulletin volume LXV issue 3, published in September of 1983: in Source Issue put "3 (SEP)" journal_abbrev Citation Information
ISSN Resource Identifers
Source Other Identifier
Map with "ISSN" in Source Other Identifier Type
journal_call_num Resource Identifiers Source Other Identifier Map with "Local Call Number" in Source Other Identifier Type journal_comment Publisher Information Source Remarks date_entered Citation Information Source Data Capture Date Vendor Images Citation Authority For all those source records where source_type=v. Vendor images purchased both individually and in sets. Data from Vendor table maps to Org Authority. Data from vendor_set (or xdb_vendor_set_qv) to Citation Authority xdb_vendor_set_qv source_id Citation Information Local Source Id (Custom field)
vendor_name Publisher Information Source Publisher Vendor name maps to either Person or Org Authority. Will need custom map for legacy data vendor_set_abbrev Citation Information Brief Citation If set_flag is "No" map only vendor_order_abbrev to Brief Citation.
vendor_item_id Title and Issue Information Source Title If set_flag is "yes" map vendor_item_id to Title and Issue Information - Source Title set_comments Publisher Information Source Remarks set_flag Citation Information Vendor Set (local boolean field?) Determines whether the citation refers to an individual image or a set of images. Use for migration of legacy data only or put in custom local field "Vendor Set" date_entered Citation Information Source Data Capture Date Donors/Duplicates Citation authority For all those source records where source_type=d. Most donor names map to Person Authority, a few to Org Authority. Need to cull duplicate names entered with different donation types (e.g. same name entered as source for donated slide, duplicate slide, and oral history, etc.)
donor source_id Citation Information Local Source Id (Custom field)
donor_name Person Authority Person Display Name; Forename; Surname Person and Org names will need to be separated manually. After dups are culled, map donor_name to Person displayName, parse last name and first name at comma and place in Forename and Surname fields of Person Authority. For orgs, map donor_name to Organization displayName date_entered Citation Information Source Data Capture Date xdb_donor_qv
credit_line Media Handling Copyright Statement Any data in credit line maps to media handling records for the accessions that point to this source_id as image source. copyright_owner Media Handling Rights Holder If copyright_owner=1 ("yes") then map name to Rights Holder field of Media Handling record for each accession linked to this source_id as image source. donor_comments Publisher Information Source Remarks Electronic Resources Citation Authority For all those source records where source_type="m"
xdb_electronic_media_qv source_id Citation Information Local Source Id (Custom field)
electronic_media_abbrev Citation Information Source Brief Citation electronic_media_title Title and Issue Information
publisher_name Publisher Information Source publisher publish_location Publisher Information Source Publication Place publish_year Title and Issue Information
Source Publication Year
url Resource identifiers Source Other identifier medium Citation Information Source type data_format add to comments? series_title no data media_count only 1 record contains a value - add to comments media_call_num no data system_requirements add to comments? media_size no data electronic_media_comments Citation Information Source Remarks date_entered Citation Information Source Data Capture Date Exhibition Citation Authority For all those source records where source_type=e. Most data in this table will map to Exhibition Authority www_source_citations
source_id Citation Information Local Source Id (Custom field)
exhibit_abbrev Citation Information Source Brief Citation
exhibit_title Citation Information
Source Title xdb_exhibit_qv
exhibit_item_type Citation Information Source Type date_entered Citation Information Source Data Capture Date Other Sources Citation Authority For all those source records where source_type=o other_sources source_id Citation Information
Local Source Id (Custom field)
other_abbrev Citation Information
Source Brief Citation
other_descr Citation Information
xdb_other_sources_qv pre_seurat_order_num Resource Identifiers Source Other Identifier format_descr Resource identifiers Source Other Identifier Type date_entered Resource Identifier Source Identifier Capture Date
Processes and Techniques
Styles and Periods
HAVRC Orders Data Mapping to CSpace Intake
Simple vs Complex Works
Works of art that consist of a single object are expressed in HAVRC as a single record in the works table, to which one or more names, dates, places, materials, techniques, subjects, and accession records may be linked via the work_id created by the system when a new work record is created. Works of art that consist of one or more component parts are expressed in HAVRC by links between the works and work_component tables. A sequential component_id is created by the system whenever a component record is saved and linked to the parent work via the work_id recorded in the work_component table. True component works cannot be completely understood without inheriting data from the parent work and this is what distinguishes these part-whole relationships from other associative relationships. For these more complex works, one or more names, dates, places, materials, techniques, subjects and accessions may be linked to each component work as well as to the parent work.
There are currently 1082 work records that have a sum total of 4058 linked component records. Until CollectionSpace has implemented the ability to create relationships between collection objects, HAVRC will need to employ a workaround to capture the relationships between parent works and their component parts as PAHMA has done (see PAHMA Collection Objects Data Mapping - Object-Object Relationships). Additional local fields will likely be needed in order to prevent data loss and/or corruption during the initial migration and additional functionality will be needed going forward to preserve and enable data inheritance between works and their components.
HAVRC Accessions Data Mapping to CSpace Media Handling