Development of a National Adult Protective Services Data System: Namrs Pilot Final Report (volume 2). Appendix D. Namrs Pilot Data Warehouse Tables and Columns

09/25/2015

This section lists the Tables, Columns, and Column Attributes for the NAMRS Pilot Data Warehouse.

[DataStore].[CaseDataSet]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
CaseDataSetId Int 4 N Y N Primary key
CasFiscalYear Int 4 N N N  
CasRegionName NVarChar (100) 100 N N N  

 

[DataStore].[Client]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N Y N Primary key
InvestigationId Int 4 N N Y Link to Investigation table
ClientCode NVarChar (32) 32 N N N  
CliFacilityCode NVarChar (32) 32 Y N N  
CliFacilityCodeStatus NVarChar (10) 10 N N N  
CliFips Char (5) 5 Y N N  
CliFipsStatus NVarChar (10) 10 N N N  
CliState NVarChar (100) 100 Y N N  
CliCounty NVarChar (100) 100 Y N N  
CliCloseReason NVarChar (100) 100 Y N N  
CliCloseReasonStatus NVarChar (10) 10 N N N  
CliAge NVarChar (100) 100 Y N N  
CliAgeStatus NVarChar (10) 10 N N N  
CliGenderIdentity NVarChar (100) 100 Y N N  
CliGenderIdentityStatus NVarChar (10) 10 N N N  
CliSexualOrientation NVarChar (100) 100 Y N N  
CliSexualOrientationStatus NVarChar (10) 10 N N N  
CliEnglishCompetency NVarChar (100) 100 Y N N  
CliEnglishCompetencyStatus NVarChar (10) 10 N N N  
CliPrimaryLanguage NVarChar (100) 100 Y N N  
CliPrimaryLanguageStatus NVarChar (10) 10 N N N  
CliMarital NVarChar (100) 100 Y N N  
CliMaritalStatus NVarChar (10) 10 N N N  
CliSchooling NVarChar (100) 100 Y N N  
CliSchoolingStatus NVarChar (10) 10 N N N  
CliEmployment NVarChar (100) 100 Y N N  
CliEmploymentStatus NVarChar (10) 10 N N N  
CliIncome NVarChar (100) 100 Y N N  
CliIncomeStatus NVarChar (10) 10 N N N  
CliVeteran NVarChar (100) 100 Y N N  
CliVeteranStatus NVarChar (10) 10 N N N  
CliAdlScore NVarChar (100) 100 Y N N  
CliAdlScoreStatus NVarChar (10) 10 N N N  
CliIadlScore NVarChar (100) 100 Y N N  
CliIadlScoreStatus NVarChar (10) 10 N N N  
CliLivingSettingStart NVarChar (100) 100 Y N N  
CliLivingSettingStartStatus NVarChar (10) 10 N N N  
CliLivingSettingClose NVarChar (100) 100 Y N N  
CliLivingSettingCloseStatus NVarChar (10) 10 N N N  
CliPreviousReport NVarChar (100) 100 Y N N  
CliPreviousReportStatus NVarChar (10) 10 N N N  
CliMaltreatmentSetting NVarChar (100) 100 Y N N  
CliMaltreatmentSettingStatus NVarChar (10) 10 N N N  
CliRaceStatus NVarChar (10) 10 N N N  
CliEthnicityStatus NVarChar (10) 10 N N N  
CliBenefitStatus NVarChar (10) 10 N N N  
CliDisabilityStatus NVarChar (10) 10 N N N  
CliBehavioralHealthScreeningStatus NVarChar (10) 10 N N N  
CliBehavioralHealthDiagnosisStatus NVarChar (10) 10 N N N  
CliDecisionmakerStartStatus NVarChar (10) 10 N N N  
CliDecisionmakerCloseStatus NVarChar (10) 10 N N N  
CliInteragencyCoordinationStatus NVarChar (10) 10 N N N  
CliServiceStartStatus NVarChar (10) 10 N N N  
CliServiceCloseStatus NVarChar (10) 10 N N N  
CliServiceApsStatus NVarChar (10) 10 N N N  
CliServiceReferredStatus NVarChar (10) 10 N N N  

 

[DataStore].[ClientBehavioralHealthDiagnosis]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliBehavioralHealthDiagnosis NVarChar (100) 100 N N N  

 

[DataStore].[ClientBehavioralHealthScreening]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliBehaviorHealthScreening NVarChar (100) 100 N N N  

 

[DataStore].[ClientBenefit]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliBenefit NVarChar (100) 100 N N N  

 

[DataStore].[ClientDecisionmakerClose]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliDecisionmakerClose NVarChar (100) 100 N N N  

 

[DataStore].[ClientDecisionmakerStart]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliDecisionmakerStart NVarChar (100) 100 N N N  

 

[DataStore].[ClientDisability]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliDisability NVarChar (100) 100 N N N  

 

[DataStore].[ClientEthnicity]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliEthnicity NVarChar (100) 100 N N N  

 

[DataStore].[ClientInteragency]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliInteragency NVarChar (100) 100 N N N  

 

[DataStore].[ClientRace]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliRace NVarChar (100) 100 N N N  

 

[DataStore].[ClientServiceAps]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliServiceAps NVarChar (100) 100 N N N  

 

[DataStore].[ClientServiceClose]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliServiceClose NVarChar (100) 100 N N N  

 

[DataStore].[ClientServiceReferred]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliServiceReferred NVarChar (100) 100 N N N  

 

[DataStore].[ClientServiceStart]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
ClientId Int 4 N N Y Link to Client table
CliServiceStart NVarChar (100) 100 N N N  

 

[DataStore].[Investigation]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
InvestigationId Int 4 N Y N Primary key
CaseDataSetId Int 4 N N Y Link to CaseDataSet table
InvestigationCode NVarChar (32) 32 N N N  
InvReportDate Date 3 Y N N  
InvReportDateStatus NVarChar (10) 10 N N N  
InvReportSource NVarChar (100) 100 Y N N  
InvReportSourceStatus NVarChar (10) 10 N N N  
InvAgencyFips Char (5) 5 Y N N  
InvAgencyFipsStatus NVarChar (10) 10 N N N  
InvAgencyState NVarChar (100) 100 Y N N  
InvAgencyCounty NVarChar (100) 100 Y N N  
InvStartDate Date 3 Y N N  
InvStartDateStatus NVarChar (10) 10 N N N  
InvDispositionDate Date 3 Y N N  
InvDispositionDateStatus NVarChar (10) 10 N N N  
InvCaseClosureDate Date 3 N N N  

 

[DataStore].[Maltreatment]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
MaltreatmentId Int 4 N Y N Primary key
ClientId Int 4 N N Y Link to Client table
MaltreatmentType NVarChar (100) 100 Y N N  
MaltreatmentDisposition NVarChar (100) 100 Y N N  

 

[DataStore].[MaltreatmentPerpetrator]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
MaltreatmentId Int 4 N Y Y Link to Maltreatment table
PerpetratorId Int 4 N Y Y Link to Perpetrator table

 

[DataStore].[PerpBehavioralHealthDiagnosis]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
PerpetratorId Int 4 N N Y Link to Perpetrator table
PrpBehaviorHealthDiagnosis NVarChar (100) 100 N N N  

 

[DataStore].[PerpBehavioralHealthScreening]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
PerpetratorId Int 4 N N Y Link to Perpetrator table
PrpBehaviorHealthScreening NVarChar (100) 100 N N N  

 

[DataStore].[PerpDisability]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
PerpetratorId Int 4 N N Y Link to Perpetrator table
PrpDisability NVarChar (100) 100 N N N  

 

[DataStore].[PerpEthnicity]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
PerpetratorId Int 4 N N Y Link to Perpetrator table
PrpEthnicity NVarChar (100) 100 N N N  

 

[DataStore].[Perpetrator]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
PerpetratorId Int 4 N Y N Primary key
InvestigationId Int 4 N N Y Link to Investigation table
PerpetratorCode NVarChar (32) 32 Y N N  
PrpAge NVarChar (100) 100 Y N N  
PrpAgeStatus NVarChar (10) 10 N N N  
PrpGenderIdentity NVarChar (100) 100 Y N N  
PrpGenderIdentityStatus NVarChar (10) 10 N N N  
PrpRaceStatus NVarChar (10) 10 N N N  
PrpEthnicityStatus NVarChar (10) 10 N N N  
PrpDisabilityStatus NVarChar (10) 10 N N N  
PrpBehavioralHealthScreeningStatus NVarChar (10) 10 N N N  
PrpBehavioralHealthDiagnosisStatus NVarChar (10) 10 N N N  

 

[DataStore].[PerpRace]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
PerpetratorId Int 4 N N Y Link to Perpetrator table
PrpRace NVarChar (100) 100 N N N  

 

[DataStore].[Relationship]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
RelationshipId Int 4 N Y N Primary key
PerpetratorId Int 4 N N Y Link to Perpetrator table
ClientId Int 4 N N Y Link to Client table
RelCohabitationStart NVarChar (100) 100 Y N N  
RelCohabitationStartStatus NVarChar (10) 10 N N N  
RelCohabitationClose NVarChar (100) 100 Y N N  
RelCohabitationCloseStatus NVarChar (10) 10 N N N  
RelKinship NVarChar (100) 100 Y N N  
RelKinshipStatus NVarChar (10) 10 N N N  
RelAssociationStart NVarChar (100) 100 Y N N  
RelAssociationStartStatus NVarChar (10) 10 N N N  
RelAssociationClose NVarChar (100) 100 Y N N  
RelAssociationCloseStatus NVarChar (10) 10 N N N  
RelDecisionmakerStartStatus NVarChar (10) 10 N N N  
RelDecisionmakerCloseStatus NVarChar (10) 10 N N N  
RelLegalRemedyStatus NVarChar (10) 10 N N N  

 

[DataStore].[RelDecisionmakerClose]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
RelationshipId Int 4 N N Y Link to Relationship table
RelDecisionmakerClose NVarChar (100) 100 N N N  

 

[DataStore].[RelDecisionmakerStart]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
RelationshipId Int 4 N N Y Link to Relationship table
RelDecisionmakerStart NVarChar (100) 100 N N N  

 

[DataStore].[RelLegalRemedy]:
Column Datatype Size Nullable InPrimaryKey IsForeignKey Description
RelationshipId Int 4 N N Y Link to Relationship table
RelLegalRemedy NVarChar (100) 100 N N N  

View full report

Preview
Download

"NAMRSpilot-V2.pdf" (pdf, 1.83Mb)

Note: Documents in PDF format require the Adobe Acrobat Reader®. If you experience problems with PDF documents, please download the latest version of the Reader®