Mapping GIFTS tables and columns to hGrant properties

From Grantsfire

Jump to: navigation, search

Mapping between GIFTS database tables and columns and hGrant properties

This document presents a proposed mapping between columns in GIFTS database tables and properties specified in the current hGrant specification.

The central table describing a grant within GIFTS is the Requests table. Each row in this table describes a unique grant or proposal within the database. Because the Requests table describes both proposals, funded grants, and closed grants, the mapping application must filter the Request records appropriately. The proposed query strategy below begins with the Requests table and joins a number of additional tables:

  • Organizations: Each row describes a single grantee organization. There is a many-to-one mapping between Requests and Organizations records.
  • Contacts: Each row describes a contact person affiliated with an organization. There is a many-to-one mapping between Organizations and Contacts records. There is a one-to-one mapping for primary contacts between Requests and Contacts.
  • Addresses: Each row describes an address, with phone and email information. There is a many-to-one mapping between Organizations and Addresses, and between Contacts and Addresses.


Query strategy

Only process Requests records where Requests.Disposition='Approved'. This ensures that only funded grants appear in the report. The query may filter for grants based on the project start date (Requests.Project_Start_Date), or on the basis of the last change to the grant's Disposition (Requests.Disposition_Date).

Field mapping

  • description: maps to Requests.Project_Title
  • amount: maps to Requests.Grant_Amount
  • url: GIFTS does not provide unique URLs for each grant; rather, there is a single URL for each organization. This means that there's no way to recover the project-specific URL for a grant given to a major research university; all grants for that research university will use the university's primary URL.
  • period:
    • dtstart: maps to Requests.Project_Start_Date
    • dtend: maps to Requests.Project_End_Date. (This date is mutable. If a grant slips, the foundation may extend this date.)
  • grantee: join to Organizations on Requests.Organization_ID equals Organizations.ID
    • org: maps to Organizations.Name
    • fn: maps to Organizations.Name
    • adr: join to Addresses on Organizations.Primary_Address_ID equals Addresses.ID
      • street_address: Addresses.Address_1 + Addresses.Address_2 + Addresses.City
      • region: Addresses.State_Province_Code
      • postal_code: Addresses.Zip_Postal_Code
      • country_name: Addresses.Country
      • tel: must decide whether to map to the organization's primary telephone or to the telephone of the grant's primary contact
        • for organization's phone: Organizations.Telephone + Organizations.Extension
        • for primary contact's phone: join to Contacts on Requests.Primary_Contact_ID equals Contacts.ID. Map to Contacts.Office_Telephone + Contacts.Office_Extension
      • email: must decide whether to map to the organization's primary email or to the grant's primary contact's email
        • for organization's email: Organizations.Email_Address
        • for primary contact's email: Contacts.Email_Address
      • url: GIFTS does not provide unique URLs for each grant; rather, there is a single URL for each organization. This means that there's no way to recover the project-specific URL for a grant given to a major research university; all grants for that research university will use the university's primary URL. Map to: Organizations.WWW_Address
Retrieved from "http://wiki.grantsfire.org/wiki/Mapping_GIFTS_tables_and_columns_to_hGrant_properties"
Personal tools