Overview



Database DesignBackground Check SystemIT Technical Design GuideVersion 01 SAVEDATE \@ "M/d/yyyy" \* MERGEFORMAT 4/22/2015Table of Contents TOC \o "1-2" \u 1Overview PAGEREF _Toc412118384 \h 42Logical Database Design PAGEREF _Toc412118385 \h 43BcsLogging Database Overview PAGEREF _Toc412118386 \h 53.1BcsLogging Database Schema PAGEREF _Toc412118387 \h 53.2BcsLogging Database Table Definitions PAGEREF _Toc412118388 \h 64BcsRegistry Database Schema PAGEREF _Toc412118389 \h 64.1California (CA) Registries/Tables PAGEREF _Toc412118390 \h 74.2DC Registries/Tables PAGEREF _Toc412118391 \h 104.3Kentucky (KY) Registries/Tables PAGEREF _Toc412118392 \h 114.4New Mexico (NM) Registries/Tables PAGEREF _Toc412118393 \h 134.5Minnesota (MN) Registries/Tables PAGEREF _Toc412118394 \h 154.6Nevada (NV) Registries/Tables PAGEREF _Toc412118395 \h 174.7Oklahoma (OK) Registries/Tables PAGEREF _Toc412118396 \h 184.8Utah (UT) Registries/Tables PAGEREF _Toc412118397 \h 214.9West Virginia (WV) Registries/Tables PAGEREF _Toc412118398 \h 224.10Office of Inspector General (OIG) List of Excluded Individuals and Entities (LEIE) Registries/Tables PAGEREF _Toc412118399 \h 235Bcs Database Overview PAGEREF _Toc412118400 \h 245.1Configuration Tables PAGEREF _Toc412118401 \h 245.2Task and Notification Tables PAGEREF _Toc412118402 \h 265.3User Management Tables PAGEREF _Toc412118403 \h 285.4Application Tables PAGEREF _Toc412118404 \h 305.5Determination Tables PAGEREF _Toc412118405 \h 325.6Registry Tables PAGEREF _Toc412118406 \h 345.7Employment Tables PAGEREF _Toc412118407 \h 365.8Appeal Tables PAGEREF _Toc412118408 \h 395.9Files and Notes Tables PAGEREF _Toc412118409 \h 415.10Rap Back Tables PAGEREF _Toc412118410 \h 435.11Payment Tables PAGEREF _Toc412118411 \h 455.12Report Tables PAGEREF _Toc412118412 \h 475.13Organization Tables PAGEREF _Toc412118413 \h 485.14Queue Management Tables PAGEREF _Toc412118414 \h 505.15Integration Rejection Tables PAGEREF _Toc412118415 \h 52OverviewThis database design document will introduce the State Information Technology (IT) team to the structure and definitions of the Background Check System (BCS) databases.Logical Database DesignBCS Source DatabasesDescriptionBcsContains the core tables for the applicationBcsLoggingContains logging information regarding system events such as authentication, as well as data updatesBcsRegistryContains imported registry data that is used in registry screeningBcsLogging Database OverviewBcsLogging Database SchemaBcsLogging Database Table DefinitionsTableTable DescriptionCategoryContains the categories of events that are recorded in the Log tableCategoryLogJoin table that associates entries in the Log table with the correct categoryLogContains one row for each logged event, such as authenticationDataLogContains a log of all data inserted or updated in the applicationBcsRegistry Database SchemaThe BcsRegistry contains one or more tables for each registry that is implemented as a local database lookup. Usually there will be a table and a staging table for each registry. The staging table is loaded first, then moved when no errors are detected.California (CA) Registries/TablesTableDescriptionCaDojExport_StagingStaging table for data exported for CA Department of Justice (DOJ)CaDoj_StagingStaging table for imported data from CA DOJCaDoj_StagingOffenseLineStaging table for imported offense data from CA DOJCaNarContains information imported for the CA Nurse Aide RegistryCaLarsContains information imported for the CA Licensing Administrative-Action Records SystemCaNar_StatusLookup table with the Status for each CaNar recordCaNar_StagingStaging table for CA Nurse Aide RegistryCaLars_StagingStaging table for CA Licensing Administrative-Action Records System RegistryCaRapback_StagingStaging table for CA Rap backCaRapback_StagingOffenseLineStaging table for Offense in CA Rap backDC Registries/TablesTableDescriptionDcNarContains information imported for the DC Nurse Aide RegistryDcPlContains information imported for the DC Professional LicensesDcNar_StagingStaging table for DcNarDcPl_StagingStaging table for DcPlKentucky (KY) Registries/TablesTableDescriptionKyNar_NurseAideAbuseRegistryContains information imported for the KY Nurse Aide and Home Health Aide Abuse RegistryKyNar_NurseAideContains information imported for the KY Nurse Aide RegistryKyPhyContains information imported for the KY Board of Medical Licensure Physician’s registryKyPhyTContains information imported for the KY Board of Medical Licensure Temporary Physician’s RegistryKyPhyAContains information imported for the KY Board of Medical Licensure Assistant Physician’s RegistryKyKbptContains information imported for the KY Board of Physical Therapists RegistryKyKbnContains information imported for the KY Board of Nursing RegistryKyKbn_StagingStaging table for KyKbnKyKbpt_StagingStaging table for KyKbptKyPhyT_StagingStaging table for KyPhyTKyPhyA_StagingStaging table for KyPhyAKyPhy_StagingStaging table for KyPhyNew Mexico (NM) Registries/TablesTableDescriptionNmNarContains information imported for the NM Nurse Aide RegistryNmNar_StagingStaging table for NmNarNmEar_IncidentLookup Table used for the NM Employee Abuse RegistryNmEar_EmployeeLookup Table used for the NM Employee Abuse RegistryMinnesota (MN) Registries/TablesTableDescriptionMnEmploymentContains employment information imported from MN NetStudy 1.0MnNarContains information imported for the MN Board of Nursing registryMnOigLeieContains information imported for the MN Exclusion List registryMnPersonContains person information imported from MN NetStudy 1.0MnProviderContains provider information imported from MN NetStudy 1.0MnNslisContains information imported from MN NetStudy 1.0 / License Holder Online Information Update System (LIS)Nevada (NV) Registries/TablesTableDescriptionNvBonContains information imported for the NV Board of Nursing registryNvFacContains information imported for NV facilitiesNvLicDieticianContains information imported for licensed dieticiansNvLicLabContains information imported for licenses laboratory workersOklahoma (OK) Registries/TablesTableDescriptionOkVorContains information imported for the OK Violent Offender RegistryOkSorContains information imported for the OK Sex Offender RegistryOkNar_PersonsLookupType for OkNar - OK Nurse Aide and Non-Technical Service Worker RegistryOkNar_AbuseLookupType for OkNar - OK Nurse Aide and Non-Technical Service Worker RegistryOkNar_ActionTypeLookupType for OkNar - OK Nurse Aide and Non-Technical Service Worker Registry OkNar_StatesLookupType for OkNar - OK Nurse Aide and Non-Technical Service Worker RegistryOkNar_StatusLookupType for OkNar - OK Nurse Aide and Non-Technical Service Worker RegistryOkNar_Abuse_HistoryLookupType for OkNar - OK Nurse Aide and Non-Technical Service Worker RegistryUtah (UT) Registries/TablesTableDescriptionUtPlContains information imported for the UT Professional Licenses registryUtPl_StagingStaging table for UtPlWest Virginia (WV) Registries/TablesTableDescriptionWvNarContains information imported for the WV Nurse Aide registryWvExclContains information imported for the WV Exclusions List registryWvExcl_StagingStaging table for WV Exclusions List registryOffice of Inspector General (OIG) List of Excluded Individuals and Entities (LEIE) Registries/TablesTableDescriptionOigExclusionsContains information imported for the Department of Health and Human Services' OIG Exclusions listOigExclusionTypesLookup table with the description of each exclusion typeOigleie_StagingStaging table for OIG LEIE importBcs Database OverviewConfiguration TablesTableDescriptionBulletinMessageContains messages that will be displayed on the Home page when a user logs inBulletinMessageTypeLookup table with valid types of bulletin messages, such as Welcome and BulletinConfigurationContains configuration setting values that may be changed by a system, such as password strength rules and SMTP server settingConfigurationTypeLookup table with configuration settings for the Configuration tableDepartmentTextStringContains customizations to text strings for a departmentLinkContains information about links that are shown on the Application Forms pageLinkTypeSpecifies the type of link that is shown on the Application Forms page; the valid values for LinkType are Help (indicates that the link goes to the Help page) and Form (indicates that the form should open via SSRS)LookupGeneric lookup table which contains values used to populate dropdown boxes, such as hair color, race, and other demographic informationLookupEncryptedEncrypted version of the lookup table to hold Criminal Justice Information Services (CJIS) related lookup valuesLookupTypeContains type of lookup and department for each lookup valueTextStringContains text strings associated with notificationsTask and Notification TablesTableDescriptionNotificationContains the set of email notifications that can be automatically sent to system users, for example, Determination AvailableNotificationOptOutAllows users to opt out of notification typesOutboundMessageContains emails waiting to be sentOutboundMessageFileContains files that will be sent as an attachment to a mass emailPermissionContains the permissions (if necessary) for receiving notificationsTaskContains the set of automated tasks that run on a scheduled basis to generate notifications or perform other actionsTaskItemActions that are queued to run asynchronously, such as letters to be generated to the report queueTaskItemTypeContains the set of all the types of TaskItems that can be generatedTaskNotificationJoin table that shows notifications associated with tasksTaskScheduleContains the schedule parameters for tasksTaskScheduleRegistryShows the schedule for individual registry rechecksUser Management TablesTableDescriptionDepartmentUserTypeJoin table between UserType and Department; shows which user types are available to which departmentsPermissionContains the set of permissions that are enforced by the applicationPermissionUserTypeSpecifies the types of users (State, Provider, or Vendor) that are eligible to be assigned to each permissionPriorPasswordContains a list of passwords that have previously been used by a User; passwords in this table cannot be reusedRoleContains the set of defined roles to which users can be assignedRolePermissionJoin table between Role and Permission that specifies the set of permissions that a user assigned to this role can useUserDepartmentContains the department to which each user is assignedUserDivisionSpecifies the one division that a user is associated withUserProviderJoin table that specifies the Providers assigned to each UserUserQueueTypeSpecifies the list of queue types to which a State user can be assignedUserRoleJoin tables that specify the roles to which a user is assignedUsersContains one row for each user who can access the applicationUserSecurityQuestionContains the set of security questions that a user has set up to use in the case of a forgotten passwordUserStatusLookup table of valid user statuses, such as Enabled or LockedUserTypeLookup table with valid types of users, such as State or ProviderApplication TablesTableDescriptionAddressThis one address table contains address information for applicants, employees, providers, and departmentsAddressTypeDescribes what type of address is in the PersonAddress table, i.e., physical or mailingAliasContains other names by which an applicant has been knownApplicationContains one row for each application - an applicant applying for a particular position at a particular providerApplicationFileContains files associated with an applicationApplicationLicenseContains the licenses or certifications that were selected as applying to this applicationApplicationNoteMapping table between Application and NoteApplicationPaymentMapping table between Application and PaymentApplicationReleaseOfInformationMapping table between Application and Release of InformationApplicationStatusLookup table with valid applicant status values, such as in-process or closedApplicationStatusReasonLookup table with valid reasons that an application may have been closed such as Hired, Withdrawn, or Not Hired Due to Registry ChecksApplicationTypeSpecifies the type of application, such as Initiating, Connecting, and Registry RecheckPersonContains one row for each person in the system, whether an applicant or an employeeDeterminationContains one row for each criminal history background check that is performed; multiple applications can be attached to the same determinationPersonAddressJoin table that associates one or more address records with a personPersonFileJoin table between Person and Files; identity documents are stored as person filesPersonIdentityJoin table that associates person to Lookup table to get citizenshipPersonLicenseContains one row for each license or certification held by a personPositionLookup table with the list of positions for which a person can applyPositionCategoryLookup table with the set of position categories used by the Center for Medicare and Medicaid Services (CMS)ProviderContains a list of providers and facilities that are eligible to use the applicationReleaseOfInformationContains the text that the user must agree to on the Applicant Consent page when entering a new applicationDetermination TablesTableDescriptionAppointmentContains information about fingerprinting appointmentsCriminalHistoryPossibleMatchContains information about possible matches when name-based criminal history searches are performedCriminalHistoryPossibleMatchFileContains the source file with possible matches when name-based criminal history searches are performedDepartmentContains one row corresponding to the State department (i.e., Department of Health)DepartmentOriContains the valid ORI values for a departmentDepartmentDeterminationEventTypeContains the DeterminationEventTypes that are valid for each departmentDepartmentDeterminationStatusReasonContains the set of determination status reasons that are valid for a given departmentDeterminationContains one row for each criminal history background check that is performed; multiple applications can be attached to the same determinationDeterminationEventContains one row for each event that happens while a determination is in process, such as Fingerprints Taken or Criminal History ReceivedDeterminationEventTypeLookup table with the list of known determination event types, such as Fingerprints Taken or Criminal History ReceivedDeterminationFileContains files associated with a determinationDeterminationNoteContains Notes associated with determinationsDeterminationOffenseContains information about a determination relating to an offenseDeterminationOffenseNoteMapping table between DeterminationOffense and NoteEncrypted; all notes for a DeterminationOffense are encryptedDepartmentOffenseStatusContains the set of offense statuses that are valid for a given departmentDeterminationStatusLookup table with valid determination status values, such as Pending, In-Process, Eligible, and ClosedDeterminationStatusReasonContains a list of the available reasons that a determination is in the current status, such as Auto Determination or Closed - Fingerprints Past DueFingerprintContains a record for each set of fingerprintsFingerprintTypeContains the set of fingerprint types, i.e., card scan or electronic scanLivescanProviderContains a list of the locations of live scan machines where an applicant can be fingerprintedOffenseLookup table with offense names, codes, and descriptionsOffenseStatusLookup table noting whether or not an offense is disqualifyingRegistry TablesTableDescriptionDepartmentRegistryContains a list of the registries that will always show up on the Research Registries page of the new applicant workflowDepartmentRegistryDeterminationStatusContains the set of RegistryDeterminationStatus values that are valid for a state; these values are what is shown in the results dropdown on the Research Registries pageRegistryContains information of registries available to the applicationRegistryDeterminationContains the results of each registry checkRegistryDeterminationFileMapping table between RegistryDetermination and FileRegistryDeterminationNoteMapping table between RegistryDetermination and NoteRegistryDeterminationStatusContains available statuses for a registry determinationRegistryEventFileMapping table between RegistryEvent and FileRegistryEventContains details for a RegistryMatch, including EventDate and DetailsRegistryMatchContains match information and information about the personRegistryMatchFileMapping table from RegistryMatch to FileRegistryRapback Contains one row for each registry recheck that occursRegistryRapbackProviderMapping table between RegistryRapback and ProviderRegistryRecheckLogLog table that shows all registry rechecks that were performed, regardless of whether a match was found or notEmployment TablesTableDescriptionDepartmentEmployeeTypeContains the set of employee types that are valid for a given department; this set of values is shown in the employee type dropdown on the Pre-Employment Information pageEmployeeImportStagingStaging table used to hold the employee information that is being importedEmployeeTypeContains the different employee types available to an applicant/employment recordEmploymentContains one row for each employment record - an employee working in a particular position at a particular providerEmploymentStatusLookup table with valid employment status values, such as Provisional, Active, or TerminatedPersonContains one row for each person in the system, whether an applicant or an employeePositionLookup table with the list of positions for which a person can applyPositionCategoryLookup table with the set of position categories used by CMSProviderContains a list of providers and facilities who are eligible to use the applicationAppeal TablesTableDescriptionAppealContains data for an appeal by an applicantAppealEventContains one row for each event that is tracked for a given appealAppealEventTypeLookup table with valid types of appeal eventsAppealFileContains files associated with an appealAppealSourceContains available sources for an appealAppealStatusLookup table with the list of valid statuses for an appealAppealStatusReasonLookup table with the set of reasons that an appeal can be closedAppealTypeContains available appeal types for an appealDepartmentAppealStatusReasonContains the set of appeal status reasons that are valid for a given departmentDepartmentAppealTypeContains the set of appeal types that are valid for a given department; this set of values is shown in the dropdown on the Start Appeal pageDeterminationContains one row for each criminal history background check that is performed; multiple applications can be attached to the same determinationFiles and Notes TablesTableDescriptionFileChunkStores the smaller “chunks” of data that large files are broken intoFileImportContains one row for each file that is importedFileImportDetailContains messages from the import process, including errorsFileImportStatusLookup table with valid statuses for a FileImportFileImportTypeContains available file import typesFileProcessorDefines the set of processes that are used to manipulate input files in the task serviceFilesContains the files that were importedFileTypeContains the set of file types, such as Application, Determination, Attachment, etc.; the file type controls where the file can be viewed in the applicationFileUserTypeMapping table between Files and UserType; this table is used to specify which user types can view which filesFileWatchContains configuration information for each file watch serviceNoteContains all the notes that are entered into the systemNoteEncryptedEncrypted version of the Note table to hold CJIS sensitive notesNoteTypeContains a list of the valid note types, such as Determination or RegistryNoteUserTypeMapping table between Note and UserType; this table is used to specify which user types can view which notesProviderImportContains one row for each file of provider data that was importedProviderImportTypeLookup table with set of valid types of provider importsRegistryImportContains one row for each file of registry data that was importedRap Back TablesTableDescriptionCriminalHistoryRapbackContains information pertaining to the event of a rap backCriminalHistoryRapbackProviderMapping table between CriminalHistoryRapback and ProviderPersonContains one row for each person in the system, whether an applicant or an employeeRapbackEnrollmentShows the time period that a person is enrolled in rap backRapbackPossibleMatchContains information about possible matches when name-based criminal history is being processedRapbackPossibleMatchProviderContains information about providers associated with possible matches when name-based criminal history is being processedRapbackStatusContains available statuses for a CriminalHistoryRapbackRegistryRapbackContains one row for each registry recheck that occursRegistryRapbackProviderMapping table between RegistryRapback and ProviderPayment TablesTableDescriptionApplicationContains one row for each application - an applicant applying for a particular position at a particular providerApplicationPaymentCombines all of the fees for an application and holds the total of all feesApplicationPaymentBatchContains a record for a batch of application paymentsApplicationPaymentBatchDetailMapping table between payment details and application payment; contains the details associated with a batch paymentApplicationPaymentDetailContains the details of the payment, i.e., confirmation numbers, check numbers, and verification numbersApplicationPaymentFeeCombines the individual fees for an application that will make up the full amount of what is owedFeeContains the amounts that need to be collected for each part of the background check FeeGroupContains information about a grouping of fee recordsFeeLevelContains the set of fee levels; this table is used to support varying pay amounts by type of providerPaymentDetailLookup table describing the detailed information regarding the paymentPaymentMethodThe available payment options for a departmentPaymentMethodDetailCombines the payment methods with the types of information that should be gathered during the payment process, such as check number confirmation number, etc.PaymentMethodTypeLookup table that holds the available method to pay; cash, check, and govpayReport TablesTableDescriptionDepartmentReportJoin table that combines the department with the report and gives it a custom name for the department; it also combines the permissions to the reportDepartmentReportTagJoin table to combine the ReportTag to the DepartmentReport tableFileQueueHolds the link to letters that have been automatically generated and need to be printedReportContains the location where each report residesReportTagGroups the reports together by which page the reports should be displayed on and where they should be displayedOrganization TablesTableDescriptionDepartmentContains configuration information at the department levelDepartmentProviderTypeContains the list of provider types that are valid for a department, along with their mapping to one of the CMS used provider typesDivisionContains the list of divisions that are subordinate to a departmentDivisionContactContains additional contacts for divisionsProgramContains a list of programs that belong to a divisionProviderContains a list of all the providersProviderContactContains additional contacts for providersProviderProgramJoin table that lists which programs are associated with which providersProviderTypeContains the list of CMS used provider typesSourceIndicates where the source from which provider and user records were created; valid values are Manual, Import, and SSOUserDepartmentJoin table that lists which department is associated with each providersUserDivisionJoin table that lists which users are associated with which divisionsUsersList of all users who can access the systemUserProviderJoin table that lists which users are associated with which providersQueue Management TablesTableDescriptionAppealContains a list of all appealsApplicationContains a list of all applicationsDeterminationContains a list of all determinationsQueueLogContains a list of all queued items and the user to which they are assignedQueueTypeLists all queue types supported in the applicationDepartmentQueueTypeContains the set of queue types that are valid for a given departmentUserQueueContains the set of queue types that are valid for a given userIntegration Rejection TablesTableDescriptionIntegrationRejectionAppointmentSaveContains invalid integration records for Appointment SaveIntegrationRejectionChangeToSidContains invalid integration records for Change To SidIntegrationRejectionCriminalHistoryAvailableContains invalid integration records for Criminal History AvailableIntegrationRejectionCriminalHistoryRapbackContains invalid integration records for Criminal History Rap BackIntegrationRejectionDeterminationUpdateContains invalid integration records for Determination UpdateIntegrationRejectionFingerprintsTakenContains invalid integration data for Fingerprints TakenIntegrationRejectionFingerprintsRejectionContains invalid integration records for Fingerprints Rejected ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download