Case Management Databases
Submitted by gabrielle on Tue, 07/18/2006 - 7:41am
by James Spencer, Missouri
Several terms are used when talking about Case Management Systems (CMS), which typically relate to databases because case management systems use a database structure to hold and index data. This article will define and explain these terms as they relate to databases and case management systems.
Database
A database is merely a storage vessel for information. For example, with a case management system, the minimum a program may want to store would include: the client's name, contact information (address, telephone number) and what advocate is assigned to provide services to that client. A database holds and indexes that information and allows a user to quickly find and use that information. In most datbases information is organized by table, record and field.
Think of a 3x5 index card box wherein each card contains one client's case information. The box would be akin to the database itself. Each card in the box a record in the database. On each card are spaces for the client and case information; these would be akin to database fields. You may decide that because of the number of cards in you box it would be easier to divide the cards into different sections within the box. This would be akin to tables within a database.
One advantage an electronic database has over the index card system is the abillity to index client records automatically as they are added. The index is a method of storing data so that the information can quickly be searched using one or more criteria and the results quickly located. Databases compile this and much more information electronically.
A database is the backbone of any case management system. The type and stucture of the database can affect how many users can effectively access the data simultaneously, how much bandwidth is required, and how effectively the data is kept, recorded, reported, and managed. Databases have a what is called a "front-end" (how the user sees the database and inputs information) and a "back-end" (where all the data is kept in numerous data tables).
Types of Databases
Databases can be written using different methods that store and integrate their contents. Access or SQL are common versions of different database systems. In addition, these databases can be housed and delivered to users in different ways, i.e. a SQL database's information can be delivered via a SQL server or via a Terminal Server.
Database Considerations
When considering what type of database to use to support your CMS, you must consider the following factors:
- Scalability and Office Size / Locations. How many users can it reasonably accommodate without slowing user's ability to quickly utilize the database? How many offices and workstations will be connected?
- Installation, Set-up of system. How easy or difficult will it be to install, set up and run? How do you plan to store the data (via a Terminal Server, SQL server, or other), and how will that decision affect costs and connectivity?
- Connectivity needs. What are the bandwidth needs of the database if you have more than one office? (If you have high-speed connections across offices (e.g. T1), a SQL server might be the best choice. If connections are not fast enough or if you have a high number of users in each outlying office, you may need a Terminal Server to provide the necessary CMS functions.)
- Functionality of the database. What is the functionality of the database to ensure clean data, easy use by your staff, and promote management reporting needs?
- Costs.
Microsoft Access - a Microsoft written database program. This program can contain data, forms (for displaying information), reports (for reporting on the information), as well as other information on how to use and store the data. When the front-end contacts the back end for information, all the information contained is transferred. This method of data requires a higher bandwidth between the computer that contains the back-end and the workstations (e.g., 10/100Mbps LAN).
- Scalability of the Database: Microsoft Access as a back-end is most effective in an environment that supports up to 15- 20 users simultaneously in one office location, connected via a Local Area Network (WAN). When using Microsoft Access as the back-end of your database, you must consider the amount of data you will be holding and your network resources (i.e., how fast your network will transfer data).
- Installation: An Access-based back-end is normally placed on a server. People must have the ability to get to the data. Most of the time, setup is easy. Installation is normally automatic and does not require many (if any) additional skills over the ability to click "Next." You may have to set up a shared directory on the server for users to be able to get to the back-end. Here again, instructions sent with the program should be able to guide you through this process.
- Changes or Modifications: Case Management Systems based in Microsoft Access can be changed and modified based upon the organization's needs using a full version of Access. Installation of the front-end is usually automatic and only requires a little configuration to let the front-end know where the back-end is located.
- Costs: Because data in an Access back-end is transferred in full to the front-end, connectivity is an important consideration with Access. In other words, while the user may only need information that is stored on one "index card," the Access database only knows how to transfer the entire file cabinet to the user, rather than a single card. This means that if bandwidth is inadequate, the user's ability to access information on the system will be slowed considerably and may result in time-outs or failures in communication. Costs for the Access database are usually reasonable. However, bandwidth costs to support its transferability of information across offices will be high because of its bandwidth needs.
Microsoft SQL Database on a SQL Server - a Microsoft database server. SQL as a database is a piece of active software that runs on the server and does many things. The primary purpose is to handle requests and serve up data. Many times a SQL database can be addressed by many different front ends, such as Excel, Access, and even word processors. With a SQL server, you can keep throwing more memory (RAM) and more processors (2, 3, 4 etc. Pentiums) at it and it uses them to become faster. It can also be backed up while open and provides other sophisticated features such as roll back (the ability to go back to a certain period in time).
With SQL, the server is responsible for hosting the data, finding the data, and returning results. The server performs most database processing; this can make case management more efficient for users. Only the information that is requested is transferred to the workstation. As a result, the bandwidth required between the SQL Server and the workstation is much less than that required with an Access back-end.
- Scalability: If a large amount of data is going to be held (e.g., 200,000+ records), and you will have a large user group (50 or more people), you may wish to consider using an SQL server to host your data. An SQL server can hold millions of data records and accommodates hundreds of simultaneous users. Additionally, it takes away the processing requirements from the workstation. A scaled down version of the SQL server is also available (Microsoft Data Engine - MSDE), but it limits the number of simultaneous users and does not offer all of the flexibility that the full SQL server would.
- Installation and Set-Up for SQL Back-End: This option requires a little more skill than just clicking "Next." Microsoft has made the SQL setup much like the setup of most other Microsoft programs, so there is some intuitive ability to set it up without formal training. Case Management systems utilizing this form of back-end will frequently send instructions (step-by-step) on how to create the database in the SQL server and how to set it up for use on the network.
- Functionality: Because the SQL back-end is a robust database, it can be programmed to include your office's "business rules," thereby eliminating routine staff entries and ensuring cleaner data.
- Connectivity: A SQL server is best used if you have multiple offices that are connected via high-speed connections (e.g., T1). If so, you may have to use a SQL Server. If the connections aren't fast enough, or if you have a high number of users in each of the outlying offices, you might need to use a Terminal Server setup to provide case management functions.
If you have multiple offices with slow or unreliable connections, you may have to look at a multiple SQL Server setup, with each office having a SQL Server, and data replication occurring between the offices. In this setup, each office has it's own SQL Server, so that connectivity to other offices is not a concern with each workstation getting it's information. The SQL Servers could then be made to replicate changes in their data during the night when no one was in the office and the connection to the other offices would normally be unused.
Terminal Server - This is a server that is set up to provide applications to remote offices. In this setup, a workstation does not actually run the application (e.g., Access), but contacts the server. The server then starts and runs the application, sending screen shots back to the workstation. The user can type or use the mouse to use the application normally and merely sends the information of what has been input to the server, which then passes that information to the application and the results are again viewed on the workstation's screen. This normally requires less bandwidth between the workstation and server than would an SQL server setup. The Terminal Server contacts the SQL Server or Access back-end to provide data to the remote user.
- Installation: With a Terminal Server, more setup skills and knowledge of the operation of servers as well as networks are needed. Here a server will be setup with Terminal Services enabled. Workstations will have to have the Terminal Services Client installed and properly configured. The server will also have to have the appropriate applications installed and configured. Although this may require more in the installation phase, it will usually not require a great deal of additional maintenance after that and will reduce the bandwidth required on your networking connections. See diagram at the end of this article.
Compiled Case Management Systems is a compiled case management system that may use a common database back-end (e.g., DBXL, d-base, etc.), or may use a proprietary back-end (a special, company-built database). In either event, you may only be able to retrieve your data using the Case Management system. Additionally, because the program is compiled, you will probably not be able to change the way it functions or how it operates (e.g., think of Quickbooks or some other financial/accounting software). Installation will usually be automatic for both the front and back-ends. The only configuration that is required is to let the front-end know where the back-end is located and how to use it. Unfortunately, because this type of CMS is compiled and used in a stock format (i.e., everyone gets the same features and benefits), it doesn't tend to allow for much flexibility.
Web Database Systems - Most of the major databases now allow you to access them via a web browser. A front end is written in html and a web programming language (like PHP or ColdFusion) that gives any user with a web browser access to the data. The database and the front end interface reside on a web server that are accessed via a LAN, Intranet or the Internet. These systems allow for easy centralization of a database and easy access becasue no special software is required on workstations. Also web browser connections tend to be much faster and require less bandwidth than the other options. Examples of this type of web database systems written for the legal services community are the new arrivals Pika Case Management System and Telelawyer Case Management System.
