Hudson River Psychiatric Center and 3NF Consulting, Inc. present Developing Cross-Departmental

1. Hudson River Psychiatric Center and 3NF Consulting, Inc. present Developing Cross-Departmental Database Applications with the HRPC Patient and Staff Management System

2. Anne Matheus Director of MIS at Hudson River Psychiatric Center Alan Labouseur President 3NF Consulting, Inc. The Presenters

3. Beginning at the End: PSMS: The Patient and Staff Management System Why?

4. Separate applications Specialized functions Different development tools: Condor, Clipper (Summer 87 and version 5), dBase Partially redundant Unpredictable Not maintainable The Problem

5. Many users Fragmented data No “real” database, rather many compartmentalized flat file systems Waste of Space Not important as space is cheap. But... Inconsistent Data The Problem

6. Costs in Time Information hard to find, if possible Many people entering similar or redundant data Costs in Money Many individual software purchases and upgrades Hard to maintain means expensive analysis and programming costs The Problem

7. Life Cycle: Analysis, Design, Development, Testing, Documentation, Rollout Analysis Design Testing Rollout Development Document Approach

8. Customer Meetings: Two phases Executives / management Overall goals, strategic directions End Users Day to day issues, performance, etc… How do they get the data Where is it entered, how often Analysis

9. Analysis • Department and Functions analyzed • Nursing services • Staffing • Medical Clinic • Utilization Review • Relatives • Medical record tracking • Employee evaluations • Staffing rosters • Staff development video tapes

10. Deriving Business Rules From users, existing forms and reports example: Utilization Reviews new admission, 1 day, 30 day, 90 day, etc. inpatient, outpatient, family care What is meant vs. what is said complex tasks are hard for people to formalize example: UR: weekends Analysis

11. Group the analysis results into collections of attributes and then form entities. Normalize those entities (tables) We first need to identify the functional dependencies within the entities so we know how to decompose large tables into normalized smaller ones. Ward/Unit Doctors Medical Records Patients Staff Design

12. Determine the functional dependencies within the tables patientId --> C number, name, gender, DOB, ward/unit, Medicaid number, etc. Note: patientID does not functionally determine relative values because there can be many relatives per patient. staffId --> name, position, shift, FTE, seniority date, tier, etc. Note: staffID does not functionally determine URC values. Design

13. Unnormalized Patient attributes Unnormalized patients table with relatives Design Problems Duplicate person record needed to store both relatives Inconsistent first names for the same person We cannot delete Gerrald Jones, Jr.’s relative without deleting Gerrald himself

14. Decompose by functional dependencies and their determinants to form normalized tables Design Normalized Patients table and Relatives table Tables broken up by similar items and related with a common key (PatientID). PatientID is the primary key of the Patients table. It’s a foreign key in the Relative table which lets us connect relatives to patients.

15. Identify the primary key for each table definition: an attribute (column) or collection of attributes that uniquely identifies every row in the table. Associate the tables with foreign key-primary key relationships. definition: an attribute or collection of attributes in one table which must exist in another table or be null. Design

16. Normal forms Measure of the degree of normalization of your database More normalized = Higher data integrity Higher data integrity = LESS ERRORS Third normal form is the goal of every database Design

17. All attributes (columns) are dependent on the key, the whole key, and nothing but the key. (So help me, Codd!) Design Third Normal Form:

18. Relationships and E-R diagrams Document the relationships between the tables (which are represented through your primary and foreign keys) with an Entity-Relationship diagram. Rectangles for the entities (tables) Lines for the relationships Labels on the lines to document the primary key / foreign key links Design

19. Staff Leave Types Consulting Physicians Utilization Review Auth. Fill Division URC List Dept. Ward / Unit Borrowed Evaluation Relatives Fixed Values Family Relations Patient Leave Types Topics Positions Patients Staff URC Pct. Clinic Calendar Tapes Clinic Diagnosis and Action Medical Records PSMS Database Design SID 1 PID 1 1 1 1 WU WU SL PL 1 M M M M PID M 1 PC PC M M 1 1 M RID 1 M DeptID 1 PID M 1 SID DeptID 1 M M 1 M M URC Div 1 1 PID M 1 1 1 1 1 SID PID M PID SID M M TID DID M 1 1 1 M DID 1 M

20. Check constraints Used to enforce legal data entry values In a SQL create table statement: Shift varchar(7) default 'Day' check (Shift='Day' or Shift='Evening' or Shift='Night') Iterations and changes Filters back to earlier design steps. Applications are never done, they’re just due. Design

21. Windows GUI application Advanced, easy-to-use interface need for many usability features LAN-based communication with database server Tool: Delphi 4 Client/Server Robust object model Readable (and therefore maintainable) language Development

22. Development Delphi 4 Client/Server Development Environment

23. Overall structure and layout Much time taken on the database design, so implementation needed to go quickly Begin with the broad strokes and fill in the details later (rapid prototyping) Menu structure Buttons and navigation Immediate user feedback Development

24. Common objects Get Next ID patient, staff, relative, doctor, tape, etc. generated our own ids rather than relying on an external one uses “critical section” concept Development

25. Common Objects One-to-Many editor relatives, tape borrowers Pick-list table editor Development • Formatting methods • Conversion methods

26. OOP for Windows Robust object design Takes advantage of Windows messaging system variable dialog boxes, inter-process communication extending built-in Windows functions by overriding their message methods Allows us to visually represent the relationships in the data Development

27. Development Visually showing the one-to-many relationship between patients and their relatives

28. Running parallel used both old system and new found errors in the old system Data entry refine check constraints develop input masks Testing

29. Reports Patient roster UR computation/reports ACD list with notes Crystal Reports Accurate Easy to check against other systems Testing

30. Users and Roles Every user has a userid Userids can be grouped in Roles Database object privileges can be granted to Roles so that you don’t have to manage users individually Jim, Leonard, Montgomery, Hikaru, Pavel, Janice PSMS Administrators PSMS Users Will, Deanna, Beverly, Jean-Luc, Geordi Security new user Robin Database Object Privileges

31. Grant and Revoke to groups of users (Roles) Security /* Picklist tables */ Grant insert, update, select, alter on Ward_Unit to PSMS_USER; Grant insert, update, select, alter on Staff_Leave_Types to PSMS_USER; Grant insert, update, select, alter on URC_List to PSMS_USER; Grant insert, update, delete, select, alter on Ward_Unit to PSMS_ADMIN; Grant insert, update, delete, select, alter on Staff_Leave_Types to PSMS_ADMIN; Grant insert, update, delete, select, alter on URC_List to PSMS_ADMIN; /* Interactive tables */ Grant insert, update, delete, select, alter on Patients to PSMS_USER; Grant insert, update, delete, select, alter on Staff to PSMS_USER; Grant insert, update, delete, select, alter on Consulting_Physicians to PSMS_USER;

32. Oracle Server installation Starting Oracle on Netware Autoexec.ncf SEARCH ADD 1 VOL1:ORACLE8\NLM oraload load svrmgr30 @vol1:oracle8\startdb.sql startdb.sql connect internal /password startup pfile=vol1:oracle8\database\initorcl.ora exit Rollout

33. Client Installation Shortcut to PSMS executable on LAN BDE Middleware needed for the Delphi PSMS Windows application to talk to the Oracle Database server. Like ODBC, ADO, etc. (Uses SQL-Links) Rollout

34. Net8 Oracle client transport protocol used to communicate with the listener service on the server Oracle Listener Net8 BDE PC PC PC PSMS.EXE Oracle Database Engine Oracle Database Storage Rollout PC workstation Novell 4.11 Server LAN

35. Installation Database Engine Installation BDE Administrator Copy ora803.dll oci.dll Crystal Report runtime files Scripts Tables Roles Future End user manual Help system Documentation

36. Oracle $10,000 Design and implementation $22,000 Software Delphi Client/Server $2500 Crystal Reports Professional $700 TOAD $425 Total $35,625 Costs

37. Elimination of an entire grade six at the cost of $32,000 ($24,000 salary, $8000 cost of benefits) Accurate data and reliable information Meeting HCFA and legal requirements Y2K ready Benefit

38. SDLC Modular design Cost benefit Installation Outcomes Summary

39. About the Authors Alan G. Labouseur With more than a decade of IT consulting experience, Mr. Labouseur has worked in more than 10 industries on over 35 projects in broad range of capacities. From hands-on database and software development to training and support, from networking to strategy and project management, he has done it all. Mr. Labouseur has Bachelor's and Master's degrees in Computer Science and teaches database systems analysis and design for several universities. In addition to his lengthy experience with a wide variety of database products, Mr. Labouseur’s specialty is data modeling and database design. He continues to consult on both large-scale and small-scale projects in the areas of data architecture, object-oriented programming, messaging, n-tier architecture, Web-server integration, workflow and data warehousing. Contact Alan by e-mail at alan@3NFconsulting.com . Alan teaching in 2001

40. About the Authors Anne Matheus Anne Matheus currently is the Director of Computer Literacy at Marist College, Poughkeepsie, New York. Her responsibilities include the administration of the Information/Computer Literacy program for undergraduates. She is currently working on the revision of the courses being offered to undergraduates to meet the new challenges of joining technology and literacy. Prior to accepting this position, Anne was the Director of Management Information Systems at a facility operated by the New York State Office of Mental Health. In that capacity, and under her direction, her facility was one of the original recipients of a statewide award for Facility Information Center of the Year in 1999. She earned a bachelor’s degree in Psychology, a master’s degree in Community Psychology, and a master’s degree in Computer Science from Marist College; she is completing her Ph.D. in Information Science at the State University of New York at Albany. Contact Anne by e-mail at Anne.Matheus@marist.edu.