Index - Major Sections
Home

**InHCc HMIS**

Site Map
 Health Economic and Reform

Benefits

Discussion

Data and Data Analysis

Health Management

Product and Services
References
Team

_______________

Index - Same Level Subject


 

Index - Child Subjects

Introduction

The following considerations will be made in the database design:

  • Data will not have to be entered multiple times.
  • Data may be entered by a simple click of the screen

  • Redundant data will be reduced

A classic example of a data structure built to support client invoicing has a Client Master, a Visit-header, and detail line-items table.  These tables are structured so that the client's permanent information is entered only in the client table. the visit-header table contains all the information needed to identify a visit such as data, time, services requested, attending health care professional, and so on, and a reference to the client who made the visit. The line-items table contains quantities and services actually delivered to the client. The services delivered are represented by service codes. Each code refers to the service in the Service tables.

Data Design

Definitions are standard procedures in the design of the data.

Index

 

See technical layout at Technical Issues -

 

Database Design

Data Design must take place at three levels:

  • The Front End where data is collected from the Health Care Worker or at the User Interface Level

  • The Storage of the Data or the Database Design

  • The Back End where data is presented to the User. 

Aggregate and Calculated data 

SQL Aggregate Functions will be used for simple aggregates. Aggregate data or the value of summarized detail data will not be entered as direct input into the Central or Local Database fields. ONLY the detail information will be entered into the system. However, for the purpose of OLAP System, summarized, non-normalized data fields will exist. 

 

Back to Top

Data common to each Record

  • Location Identification Code

  • Date and Time of entry of the information

  • Healthcare Worker Identification Code (Based on the signed in ID) 

  • Sequence numbering of certain records Delete/Change code to indicate that the data on this record has been changed or deleted. and to link that change to a change information file.

  • Memo field for free text. This field is used for additional information that is not coded to a standard format. It can also be used for progress notes that the health care worker writes to him or her self and for summary information.  

  • Computer Identification Code

Back to Top

Date Fields

Date fields in Access are always stored internally in the form month/day/year. Dates are displayed to the user based on the Regional Options set in the Control Panel.

 

It is suggested that dates be displayed in the International date format...day/month/year.

Date fields will allow both date/time values to entered into the single field. In order to select out specific information such as month, day of week, etc. an Access Format Function such as Month() will be used. 

 

Default Entries

Default Entries will be used for the following:

  • Client ID (where a new record based on a client selection has been made)

  • Visit Date (automatically generated based on the computer system date

  • Visit ID (automatically generated by the system)

  • Sequence (automatically generated by the system)

Although default values provide for faster input of data, and in some cases more accurate, the InHCc system will not use defaults for input of health care information. The health care worker must make take positive action in order for the data to be entered. This is especially true in cases where it would be possible to use "Normal" as the default. 

Back to Top

Form Design

The data collected at the lowest level will be largely in pre-coded form. This is implemented for several reasons:

  • Pre-coded data allows Healthcare Professionals to follow procedures easily

  • Data can be input with a simple point-and-click. 

  • Pre-Coded data can be compared across languages and countries with no additional work. 

  • Having procedures in Pre-Coded formats reinforces training and education.

  • Data collected in coded form at the original entry point provides for a cost effective method of data collection.

Local Tables ("Local" tables - Geographical)

 

Each location has the ability to set its own environment. Local Tables contain information that can be used as defaults values for its data entry and reporting. Examples are City and Community codes may be set as default values for client's address information. 

 

Local Groups will also be controlled by using standard Windows Security protocols. A Local organization will first be placed in geographical Organization Units (OUs) and these OUs will be assigned permissions to create and access only certain files and reports. 

 

Location  tables in the InHCc system will begin with the literal letters of Local_.

Look Up Tables ("LU" tables)

A Look Up table is table whose purpose is to supply a list of predefined values (standard descriptive terms) for the user to chose as a response. A Look Up tables can also translate the descriptive value into a standard code without the user having to see or even knowing that the codes exist.  Whereas descriptive information can be written in the language of the user, codes are uniform throughout the system. This system will use codes for all fields that can be translated into a different language. 

 

Using these tables increase the efficiency and accuracy of data entry by eliminating the need to type in text or to remember codes. It also increases the accuracy by only providing the descriptive values that are "standard" for that event. 

 

This provides the original health care worker the ability to collect data using his own language, and at the same time, for other users to view that collected data in their own language.  

 

This "mapping" also allows the system, to "translate" terms from one system onto the Standard System. An example, is education types and classes. One region may have a different schooling system but the descriptions can be easily mapped to the Standard.

 

Look-Up tables in the InHCc system will begin with the literal letters of LU_

Back to Top

User Tables ("User" tables - Functional)

 

Each user has the ability to set his or her own environment. User Tables contain information that can be used as defaults values for data entry and reporting. User tables in the InHCc system will begin with the literal letters of User_.

 

Users will also be controlled by using standard Windows Security protocols. A User will first be placed in Functional (job) Organization Units (OUs) and these Function OUs will be assigned permissions to create and access only certain files and reports. 

 

Normalization of Attributes

These Record Layouts are designed according to Normalization Rules (for the most part) using a Relational Data Base Design. OLAP design using the Star will be discuss under the section on OLAP and will be implemented under a different database design than the Centralized database. 

 

This Section does not list the Record Layouts used in Accounting and Finance (see Chart of Accounts).

Percentages

There are generally two ways to enter percentages: Enter the percentage as a whole number (for example. 5.0) or enter the percentage as a decimal number (.05). This InHCc will expect percentages to be entered in Whole Numbers. A Rule will check each percentage entered in order to check that it is > 1.0 if not a warning message will be displayed. 

 

Queries to the Centralized Database

Since all "real" queries to the centralized database is performed using coded data, no language has to be translated into the "language of the database." An example is that when a Healthcare Professional needs to see all "male,"  he or she does not have to translate their own language into the "English" language. Male (or what every the term may be in the local language) will simple be a "1" in the database and will map to any local language. 

Back to Top

Zero-Length Strings and Null Values

In most cases, for the purpose of the management of operations, a blank entry requires no special treatment or interpretation. However, in research, a blank field may suggest several different meanings, each important to the results of the research. 

In the first case, the blank may mean that the information is unknown at the time but a value does exist. In the second case, the blank may mean that the value is not applicable to this application.

The InHCc system will support the ability to distinguish between the two types of blank fields:

(1) A NULL value that means unknown at the time of entry.If the field contains a Null value, this means that the value was unknown at the time. 

(2) a ZERO-LENGTH STRING that means the value is known and its value is blank (not applicable) for that record. If the field contains a zero-length string, then there is no relevant value for that field in that record. a zero-length is entered by type "" in a field (a pair of double quotation marks with NO SPACE between them).

Back to Top

"Explosion" of Global Values

Many values, either for ease of use or as a result of the nature of their structure, are values that can be "exploded" either up the hierarchical tree or down the hierarchal tree.  The best way to descript these values is by example. 

A value for city, can be exploded up a tree. By listing a city, the district, state, and country will be automatically created. 

Geographical

  • Country
    • State
      • District
        • City 
          • Community

Product (Procedure or services given)

  • Procedure Level 1
    • Procedure Level 2
      • Product Type
        • Product

Cost Accounting is another place where explosion is used. A global cost for "family planning services" may be exploded down a tree. 

  • Family Planning Services
    • Nursing Time
    • Drugs
      • Vitamins A
      • Tetanus
    • Supplies
      • Condoms
    • Overhead
      • Administration
      • Facilities

Back to Top