Skip to content

Database

Jump to bottom
Michael Parsons edited this page Oct 20, 2023 · 2 revisions

Database

There are two databases used by the import and credential finder webapp:

  • CredFinder - contains all the main registry data and code tables
  • CE_ExternalData - contains common data such as NAICS, SOC, and codes such as languages and countries

All documentation is in the process of moving to external documents.

While these documents are in progress, we wanted to provide access immediately.

Old Documentation

Rather than attempting to maintain updates to tables, procs, and views, etc. there are backups provided for these databases. As well restore sql has been provided for both. New backups will be added where significant changes are made to the database structure.

There are two databases (as zipped SQL 2012) backups provided for credFinder:

  • credFinderGithubyymmdd.zip
    • Contains only code and support tables, no credential registry data.
    • Use this one to start fresh for imports.
  • credfinderTestyymmdd
    • Contains test data used by the development team. There will be a mix of data from production and test data from the registry sandbox.
    • Use this database if you want to first enable a working copy of the credential finder site.

Sample SQL restore files have been added to the database folder.

Credfinder Database

The database structure was designed based on prototyping using a constantly changed schema. So not perfect, but it is flexible and it works. There are separate tables for the main artifact types like credential, organization, assessment, learning opportunity and the manifests. To simplify working with child tables, we defined an Entity table. This is a pattern sometimes referred to as a Party table. This allows us, for example to have a single Entity.Property table under Entity that can be used for all entity types, rather than having Credential.Property, Organization.Property, Assessment.Property, etc.

Entity Table

Id

PK

EnityUid

UniqueIdentifier from the parent table - matches the RowId column. Any table that can have a child table (ultimately under Entity) will have a related Entity table. Foreign keys are defined based on:

        Parent.RowId = Entity.EntityUid

EntityTypeId

The entity type of the parent table. For a complete list see Codes.EntityTypes.

EntityBaseId

Integer PK of the parent table (convenience to access parent table in clear context, or using EntityTypeId.

EntityBaseName

Name of parent table - used primarily for prototyping, and use in views. Is maintained via an update trigger on parent table.

LastUpdated

LastUpdated is most recent last modified date of the parent entity or any of its child tables.

CE_ExternalData Database

We have some tables that are shared with other projects. Rather than duplicate these tables in multiple databases, we created a separate database: CE_ExternalData. References to tables in this database are mostly done through database views.

Key tables

  • CIPCode2010
  • Codes.Countries
  • Codes.Currency
  • Codes.Language
  • MilitaryOccupationCode
  • NAICS
  • ONET_SOC

Clone this wiki locally