Entity Relationship Diagram (ERD)

1

Jeff Murray’s Programming Shop, Inc.

RW CAREWare 4.1

Entity Relation Diagrams

Version 1.5

May 2008

Copyright © 2002-2008 Jeff Murray's Programming Shop, Inc. (jProg). All rights reserved. Creation of and updates to this document were funded by the U.S. Government, contract number 231-02-0001.

Table of Contents

Demographics

Overview

Non-Referential Tables

Demographic Diagram

Service

Overview

Non-Referential Tables

Service Diagram

Annual Review

Overview

Non-Referential Tables

Annual Review Diagram

CERF

Overview

Non-Referential Tables

CERF Diagram 1

CERF Diagram 2

CERF Diagram 3

Referrals

Overview

Non-Referential Tables

Referral Diagram

Relations, Counseling/Testing, Pregnancy Hx, Custom Subform

Overview

Non-Referential Tables

Relations, Counseling/Testing, Pregnancy Hx, Custom Subform Diagram

APA/HIP

Overview

Non-Referential Tables

APA/HIP Diagram

Logs

Overview

Non-Referential Tables

Logs Diagram

Domain

Overview

Non-Referential Tables

Domain Diagram

Permissions

Overview

Non-Referential Tables

User Permissions Diagram

Client Sharing

Overview

Non-Referential Tables

Client Sharing Diagram

Reports

Overview

Non-Referential Tables

Reports Diagram

CADR Setup

Overview

Non-Referential Tables

CADR Setup Diagram

Custom Data

Overview

Non-Referential Tables

Custom Data Diagram

Value Storage

Overview

Non-Referential Tables

Value Storage Diagram

Table and Field Comments

Demographics

Overview

These tables hold basic demographic data, store funding eligibility, and indicate which providers serve which clients.

Non-Referential Tables

Cw_client / Holds basic demographic data; each record represents one client.
Cw_client_custom / Holds data in custom fields that will be added by administrators. Maintains one-to-one relationship with Cw_client.
Cw_physician / Holds a list of physicians tracked by the APA module.
Cw_map_client_provider / Shows which providers serve which clients. Spans a many-to-many relationship between client records and domain records.
Cw_grantee_eligibility_map / Allows users to track clients’ eligibility for specific funding sources; this eligibility applies to all providers.
Cw_funding_source / Lists funding sources. Users can add to the list but cannot delete defaults.
Cw_provider_eligibility_map / Allows users to track clients’ eligibility for specific funding sources for specific providers.
Cw_domain / Stores information about providers and about the central site.

Demographic Diagram

Service

Overview

These tables hold basic service data and track receipts, contracts, and funding.

Non-Referential Tables

Cw_service / Holds actual service records.
Cw_subservice / Stores service definitions, which are user-defined.
Cw_service_custom / Holds data in custom fields that will be added by administrators. Maintains one-to-one relationship with Cw_service.
Cw_client / Maintains one-to-many relationship with Cw_service.
Cw_receipt / Stores payments not related to the subservice’s specified funding source.
Cw_domain / Service records are provider-specific, hence the need for this one-to-many relationship with Cw_service.
Cw_contract_subservice_map / Shows which subservices are represented by which contract items (a many-to-many relationship).
Cw_subservice_funding_map / Shows which subservices are funded by which sources (a many-to-many relationship).
Cw_contract_item / Stores representations of one or more subservices for use in contracts.
Cw_funding_source / Lists funding sources. One can be applied to a subservice for each month. Users can add to the list but cannot delete defaults.
Cw_contract / Holds records that set a unit price and limit spending for a specific provider.

Service Diagram

Annual Review

Overview

These tables hold data that relate to specific fiscal years.

Non-Referential Tables

Cw_annual_review / Each record stores data for one client for one year.
Cw_client / Maintains one-to-many relationship with Cw_annual_review. Each client can have only one Cw_annual_review record per year.
Cw_annual_custom / Holds data in custom fields that will be added by administrators. Maintains one-to-one relationship with Cw_annual_review.

Annual Review Diagram

CERF

Overview

The functionality of the CERF, which originally stood for “Clinical Encounter and Referrals Form,” tracks various client clinical data.

Non-Referential Tables

Cw_client / Maintains one-to-many relationships with Cw_medication, Cw_vital_signs, Cw_test, Cw_diagnosis, Cw_case_note and Cw_immunization.
Cw_medication / Each record represents one medication for a client for a specified date span.
Cw_available_medication / Holds the list of medications that are used across the entire network.
Cw_active_medication_map / Shows which medications specific providers use.
Cw_domain / Some CERF data, like vital signs data, are provider specific. Other data, like medication data, are not.
Cw_vital_signs / Each record can hold one vital sign measurement of each type unlike in other CERF tables. Only one record per client per date is allowed. The existence of a record equals the existence of a CERF encounter.
Cw_test / Stores lab, screening lab and screening data.
Cw_quantitative_test_result / Lists available non-numeric test results.
Cw_test_class / Limits available Cw_quantitative_test_result values for specific tests.
Cw_test_definition / Lists labs, screening labs and screenings available in test records. Users can add definitions but cannot delete or modify defaults.
Cw_test_active_map / Lists test definitions that are available by provider.
Cw_action / Lists actions available in screening records. Users can add definitions but cannot delete or modify defaults.
Cw_diagnosis / Holds diagnosis data (not provider-specific).
Cw_diagnosis_definition / Lists available diagnosis definitions. Users can add definitions but cannot delete or modify defaults.
Cw_active_diagnosis_map / Shows which diagnosis definitions are available for specific providers.
Cw_case_note / Stores case notes, which are provider-specific.
Cw_immunization / Stores immuinization records, which are not provider-specific.

CERF Diagram 1

CERF Diagram 2

CERF Diagram 3

Referrals

Overview

These tables track internal, external and silent referrals.

Non-Referential Tables

Cw_referral / Stores referral records.
Cw_client / Maintains one-to-many relationships with Cw_referral.
Cw_service / A service record can consume internal referrals.
Cw_subservice / Internal and silent referrals specify subservices; external referrals specify categories only.
Cw_domain / For internal referrals, referring and referred-to providers are stored. For external and silent referrals, only the referring provider is stored.

Referral Diagram

Relations, Counseling/Testing, Pregnancy Hx, Custom Subform

Overview

These tables track optional client data.

Non-Referential Tables

Cw_relation_map / Indicates that two clients are related and specifies that relation.
Cw_pregnancy_history / A record for each pregnancy a client has.
Cw_counseling_testing / Stores HIV counseling and testing data for clients. These records are used for “confidential” counts on the CADR.
Cw_custom_subform / Holds custom data that have a many-to-one relationship with Cw_client.
Cw_domain / Cw_counseling_testing records are provider-specific, hence the need for this one-to-many relationship with Cw_service.
Cw_client / Maintains one-to-many relationships with Cw_relation_map (implying a many-to-many recursive relationship), Cw_pregnancy_history, Cw_counseling_testing, and Cw_custom_subform.

Relations, Counseling/Testing, Pregnancy Hx, Custom Subform Diagram

APA/HIP

Overview

These tables store drug and insurance payments funded by APA and HIP.

Non-Referential Tables

Cw_insurance_payment / Stores insurance payment data for both APA and HIP.
Cw_drug_payment / Stores drug payment data for APA.
Cw_dispenser / A list of APA drug dispensers referred to in Cw_drug_payment.
Cw_available_medication / A list of medications referred to in Cw_drug_payment.
Cw_active_medication_map / Shows which medications specific providers use.
Cw_domain / All drug and insurance payment records are provider-specific.
Cw_client / Maintains one-to-many relationships with Cw_insurance_payment and Cw_drug_payment.

APA/HIP Diagram

Logs

Overview

These tables store user log and change log data.

Non-Referential Tables

Cw_change_log / Stores logs of changes to data.
Cw_user_log / Stores logs of user activity.
Cw_client / Used by Cw_user_log when it tracks a user’s access to client records.
Cw_domain / The user’s current domain is always stored in Cw_change_log.
Cw_user / Tracked in all change log records and in almost all user log records (occasionally this log stores an event that happens before a user logs in).

Logs Diagram

Domain

Overview

These tables hold data relating to providers, funding sources for providers, and central sites.

Non-Referential Tables

Cw_domain / Stores one record for each provider and one record for the central site.
Cw_grant_funding_map / Spans a many-to-many relationship between Cw_domain and Cw_funding_source.
Cw_funding_source / Lists funding sources. Users can add to the list but cannot delete defaults.

Domain Diagram

Permissions

Overview

These tables store user and domain permissions.

Non-Referential Tables

Cw_user_permission_map / Spans the many-to-many relationship between Cw_user and Cw_permissions_rft. Also specifies the domain to which the permission applies.
Cw_user / Lists the users who have the permissions specified in Cw_user_permission_map.
Cw_domain / Lists the domains that have the permissions specified in Cw_domain_permission_map. Also lists domains to which user permissions apply.
Cw_domain_permission_map / Spans the many-to-many relationship between Cw_domain and Cw_permissions_rft.
Cw_adapter / Stores import and export adapters. Exactly one import adapter exists for each store and forward provider.

User Permissions Diagram

Client Sharing

Overview

These tables store records of client sharing between providers.

Non-Referential Tables

Cw_client_sharing_map / Stores requests and agreements among providers.
Cw_client / Lists the client record whose sharing is in question.
Cw_domain / Used twice by Cw_client_sharing_map: once for the requesting provider and once by the sharing provider.

Client Sharing Diagram

Reports

Overview

Non-Referential Tables

Cw_report_specification / Holds one record per custom report.
Cw_crosstab_report / Holds one record per crosstab wizard report; unlike with custom reports, the filter is part of the main record.
Cw_field_selection / Defines columns for custom reports.
Cw_domain / All reports are domain-specific. Users with permission can copy reports and distribute them to multiple providers.
Cw_filter / Defines “where” clause of SQL for custom reports.
Cw_report_field / Lists fields available for use in custom reports.
Cw_crosstab_field / Lists fields available for use with the crosstab wizard.

Reports Diagram

CADR Setup

Overview

These tables hold entered CADR data.

Non-Referential Tables

Cw_cadr_setup / Holds entered Title I-IV CADR data—one record per year per provider.
Cw_funding_summary / Holds entered APA/HIP CADR data—one record per year per provider.
Cw_domain / Lists providers specified in Cw_cadr_setup and Cw_funding_summary.

CADR Setup Diagram

Custom Data

Overview

These tables define user-designed fields and indicate which providers use them.

Non-Referential Tables

Cw_custom_field / One record per custom field.
Cw_domain_custom_map / Spans the many-to-many relationship between Cw_custom_field and Cw_domain.
Cw_domain / Lists the domains that make use of the fields defined in Cw_custom_field.

Custom Data Diagram

Value Storage

Overview

Non-Referential Tables

Cw_common_storage / Stores values that relate to all domains and users.
Cw_domain_storage / Stores values that relate to a specific domain.
Cw_user_storage / Stores values that relate to a specific user.
Cw_domain / Lists domains that store values in Cw_domain_storage.
Cw_user / Lists user records that store values in Cw_user_storage.

Value Storage Diagram

Table and Field Comments

cw_act_program_rftIndicates that a domain record represents a Title I-IV, HIP, or APA provider.

act_pr_activeChecked if this record is active.

act_pr_labelOriginal values: Title I-IV, APA and

act_pr_last_updatedThe date and time this record was last

updated.

act_pr_rpkNumeric code that starts at 1.

cw_actionLists optional actions providers take as a result of screenings.

act_act_cl_fkFK to the action class table; allows

combo box to show only desired action

act_codeOptional and not unique; allows admins

to link different actions

act_hrsa_definedHRSA-defined records cannot be

modified by users.

act_last_updatedThe date and time this record was last

updated.

act_nameThe text that appears to users.

act_pkStandard GUID PK.

cw_action_classGroups action records; only one class of actions shows in the action combo box at any one time.

act_cl_hrsa_definedHRSA-defined fields cannot be modified

by users.

act_cl_last_updatedThe date and time this record was last

updated.

act_cl_nameThe text that appears to users.

act_cl_pkStandard GUID PK.

cw_active_diagnosis_mapIndicates which diagnoses are active for the related provider.

act_dg_dgn_df_fkFK to the diagnosis definition table.

act_dg_dmn_fkFK to the domain table.

act_dg_pkCounter PK only.

cw_active_medication_mapIndicates which medications are active for the related provider.

act_md_avl_md_fkFK to the available medications table.

act_md_dmn_fkFK to the domain table.

act_md_pkCounter PK only.

cw_adapterStores import and export adapters. Note that exactly one import

adapter exists for each S&F provider.

adp_encryption_keyThe grantee's public encryption key.

adp_last_updatedThe date and time this record was last

updated.

adp_nameFor import adaptors, the name is the

provider name. Grantees never

consciously create an import adapter;

they just add filers in the permission tree.

Providers, however, do create export

adapters, or they import them from setup

files (see the HLD).

adp_pkStandard GUID PK.

cw_agency_type_rftIndicates whether the agency is a provider or a grantee and what

title funding is used.

agn_tp_activeChecked if this record is active.

agn_tp_labelThe text seen by users.

agn_tp_last_updatedThe date and time this record was last

updated.

agn_tp_rpkNumeric code that starts at 1.

cw_alarm_status_rftUsed by the user log table to indicate that an alarm has been

cleared, has not been seen, has been passed on to the next admin,

alr_st_labelThe text seen by users.

alr_st_rpkNumeric counter starting at 1.

cw_annual_customStores annual and quarterly data. There are always four quarterly

fields for each concept.

ann_cs_last_updatedThe date and time this record was last

updated.

ann_cs_pkStandard GUID PK.

cw_annual_reviewStores annual data. Note that user-defined fiscal years specify the

start and end dates.

ann_rv_art_dateDate ART first prescribed

ann_rv_art_pr_rfkFK to ART Prescribed RFT

ann_rv_art_tp_rfkFK to Type of ART prescribed RFT

ann_rv_cln_fkFK to the client table.

ann_rv_eis_external_yes_na_rfkWas the client referred outside of the EIS

(Title III) Program for any medical

services?

ann_rv_eis_internal_yes_na_rfkWas the client referred for entry into an

experimental clinical trial conducted

within the EIS (Title III) Program at any

time in the reporting period?

ann_rv_geno_pheno_yes_un_rfkWas genotypic/phenotypic testing

performed at any time during this

reporting year to determine resistance?

ann_rv_hiv_st_rfkFK to HIV Status RFT

ann_rv_household_incomeHousehold Income

ann_rv_household_sizeHousehold Size

ann_rv_hsn_lv_rfkFK to Housing/Living Arrangement RFT

ann_rv_ins_tp_rfkFK to Insurance Type RFT

ann_rv_last_updatedThe date and time this record was last

updated.

ann_rv_mdc_cr_rfkFK to Medical Care RFT

ann_rv_pkStandard GUID PK.

ann_rv_poverty_levelPoverty Level: read-only; calculated

from household size and income.

ann_rv_q1_mental_health_hst_rpkDoes the client have a self-reported

and/or documented history of a mental

health condition(s)?

ann_rv_q1_mental_health_trt_st_rfkWhat is the client's current mental health

treatment/counseling status at the time

of the last visit during this quarter?

ann_rv_q1_substance_abuse_hst_rfkDoes the client have a self-reported

and/or documented history of substance

abuse or dependency problems

(including injection drugs, alchohol)?

ann_rv_q1_substance_abuse_trt_st_rfkWhat is the client's substance abuse

treatment or counseling status at the time

of the last visit during this quarter?

ann_rv_q2_mental_health_hst_rpkDoes the client have a self-reported

and/or documented history of a mental

health condition(s)?

ann_rv_q2_mental_health_trt_st_rfkWhat is the client's current mental health

treatment/counseling status at the time

of the last visit during this quarter?

ann_rv_q2_substance_abuse_hst_rfkDoes the client have a self-reported

and/or documented history of substance

abuse or dependency problems

(including injection drugs, alchohol)?

ann_rv_q2_substance_abuse_trt_st_rfkWhat is the client's substance abuse

treatment or counseling status at the time

of the last visit during this quarter?

ann_rv_q3_mental_health_hst_rpkDoes the client have a self-reported

and/or documented history of a mental

health condition(s)?

ann_rv_q3_mental_health_trt_st_rfkWhat is the client's current mental health

treatment/counseling status at the time

of the last visit during this quarter?

ann_rv_q3_substance_abuse_hst_rfkDoes the client have a self-reported

and/or documented history of substance

abuse or dependency problems

(including injection drugs, alchohol)?

ann_rv_q3_substance_abuse_trt_st_rfkWhat is the client's substance abuse

treatment or counseling status at the time

of the last visit during this quarter?

ann_rv_q4_mental_health_hst_rpkDoes the client have a self-reported

and/or documented history of a mental

health condition(s)?

ann_rv_q4_mental_health_trt_st_rfkWhat is the client's current mental health

treatment/counseling status at the time

of the last visit during this quarter?

ann_rv_q4_substance_abuse_hst_rfkDoes the client have a self-reported

and/or documented history of substance

abuse or dependency problems

(including injection drugs, alchohol)?

ann_rv_q4_substance_abuse_trt_st_rfkWhat is the client's substance abuse

treatment or counseling status at the time

of the last visit during this quarter?

ann_rv_yer_rfkFK to the year table.

cw_art_prescribed_rftLists options that show whether the client was prescribed ART.

art_pr_activeChecked if this record is active.

art_pr_labelThe text seen by the user.

art_pr_last_updatedThe date and time this record was last

updated.

art_pr_rpkNumeric code that starts at 1.

cw_art_type_rftLists options that show the type of ART prescribed to the client

(usually depending on number of ART meds).

art_tp_activeChecked if this record is active.

art_tp_labelText seen by the user.

art_tp_last_updatedThe date and time this record was last

updated.

art_tp_rpkNumeric code that starts at 1.

cw_assay_rftLists available Viral Load examinations.

ass_activeChecked if this record is active.

ass_labelThe text seen by users.

ass_last_updatedThe date and time this record was last

updated.

ass_rpkNumeric code that starts at 1.

cw_available_medicationUsed in CERF medication and APA drug functionality. Records are included by default or are imported by central administrator.

avl_md_hrsa_idThe code HRSA uses to identify the

drug. Important since the PK is a GUID.

avl_md_hrt_cl_rfkFK to the HAART class RFT.

avl_md_nameIn most cases, the active ingredient. ART

med records also show brand name.

avl_md_pkStandard GUID PK.

cw_cadr_setupStores CADR setup data, which each provider can enter once per

year.

cdr_st_admin

cdr_st_adolescents

cdr_st_ambulatory

cdr_st_anon_antibodies

cdr_st_anon_pos

cdr_st_anon_posttest

cdr_st_anon_posttest_no_return

cdr_st_anon_pretest

cdr_st_capacity

cdr_st_care_other

cdr_st_children

cdr_st_dermatology

cdr_st_dmn_fkFK to the domain table.

cdr_st_eis

cdr_st_eis_sites

cdr_st_fiscal

cdr_st_gas

cdr_st_gay_adult

cdr_st_gay_youth

cdr_st_HIP_exp_total

cdr_st_hiv_ct

cdr_st_hiv_ct_funded

cdr_st_homeless

cdr_st_incarcerated

cdr_st_injection

cdr_st_last_updatedThe date and time this record was last

updated.

cdr_st_mental

cdr_st_migrant

cdr_st_minority

cdr_st_missing_fields

cdr_st_na

cdr_st_neurology

cdr_st_non_injection

cdr_st_notified

cdr_st_nutritional

cdr_st_obstetrics

cdr_st_optometry

cdr_st_oral

cdr_st_oral_fund

cdr_st_other

cdr_st_other_other

cdr_st_other_primary

cdr_st_outside_eis_clients

cdr_st_parolees

cdr_st_partner_notification

cdr_st_patient_other

cdr_st_patient_primary

cdr_st_pharm

cdr_st_pkStandard GUID PK.

cdr_st_plan_eval

cdr_st_quality

cdr_st_rcv_sct330

cdr_st_rehab

cdr_st_runaway

cdr_st_rural

cdr_st_src_title_adol

cdr_st_src_title1

cdr_st_src_title2

cdr_st_src_title3

cdr_st_src_title4

cdr_st_substance

cdr_st_tech_assist

cdr_st_third_other

cdr_st_third_primary

cdr_st_title1_fund

cdr_st_title2_fund

cdr_st_title3_fund

cdr_st_title3_other

cdr_st_title3_pharm

cdr_st_title3_primary

cdr_st_title4_fund

cdr_st_total_other

cdr_st_total_primary

cdr_st_women

cdr_st_yer_rfkFK to the year table; a four-digit year.

cw_case_noteStores case note records.

cas_nt_cln_fkFK to the client table.

cas_nt_dateThe date to which the note pertains.

cas_nt_dmn_fkFK to the domain table.

cas_nt_last_updatedThe date and time this record was last

updated.

cas_nt_noteThe note.

cas_nt_pkStandard GUID PK.

cw_change_logStores one record for each data entry change made by the user.

chn_lg_date_timeDate and time the change happened.

chn_lg_dmn_fkOne user can have access to multiple

domains, so indicate the current domain.

chn_lg_field_nameThe name of the changed field.

chn_lg_log_fl_rfkFK to the log field type RFT.

chn_lg_new_valueThe value after the field was changed.

chn_lg_old_valueThe value before the field was changed.