"How to create a client database"
CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge How to create a client database ______________________________________________________________________________ 1 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge HOW TO CREATE A CLIENT DATABASE Objective of the workshop The workshop explores the theme of how to create a client database in a participatory manner. Participants will share their approaches and experiences in assessing client needs, collection of data, confidentiality, designing databases, data sharing and search for innovation and improvement. ______________________________________________________________________________ 2 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge GOAL: • Contribute to a better knowledge and understanding of communities and clients PURPOSE: • Understand basics of database, know what others are doing and get familiarized with design of a simple database ______________________________________________________________________________ 3 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge WORKSHOP BACKGROUND FOR CERIS WORKSHOP LENGTH • Three hours with a 15-minute coffee break TRAINING METHODS • Adult education • Participatory • Experiential learning • Use of visual aids • Small group work: discussion, exercises • Computer aided instruction TEACHING AIDS AND EQUIPMENT REQUIRED • Transparencies • Flipchart, markers and masking tape • Overhead projector and screen • Handout • Case material • Computers with database software (2-3) • Notepads • Nametags • Pens ______________________________________________________________________________ 4 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge FACILITATION MEDIUM • English ASSUMPTIONS Community based agencies benefit from creating client database. This is an introductory program for frontline workers, practitioners and coordinators without background in database. Familiarity with computer literacy is strongly recommended. This workshop is not meant for database administrators. This workshop is not a coaching program in MS Access. FACILITATOR: REQUIRED KNOWLEDGE AND SKILLS • Familiarity with CERIS-PAC Project • Knowledge of immigrant and refugee serving agencies in Greater Toronto Area • Sound understanding of database architecture and functioning • Good skills and knowledge of adult education methods • Practical experience with development and use of client databases in non-profit sector NOTES TO THE TRAINER Trainer needs to arrange copies of overheads in the order of presentation in the workshop, case illustration, and handout in order of the modules. On the left side pouch in the folder insert draft agenda, a notepad and the evaluation form. The goal and purpose statements and the workshop objective could be written on flipcharts and taped on the wall before the workshop commencement. Round table(s) would be ideal for effective participation. Overhead projector, screen, flipcharts and computers with database software will be needed. Find out the facility for coffee, tea and drinking water at the venue and the location of wash rooms for men and women. While choosing a venue, prefer one with handicap accessibility. Effective use of participants as volunteers will be useful in conducting discussion sessions, computer lab session and in displaying flipcharts. ______________________________________________________________________________ 5 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge AGENDA • Workshop Introduction 1.1 Welcome 1.2 Introduction of facilitator and participants 1.3 Expectations of participants • Databases: Knowing About Your Clients and Constituents 2.1 Advantages of a database 2.2 Costs of creating a database 2.3 Relational database • Planning a Client Database 3.1 Planning issues 3.2 Production features of a database 3.3 Sources of information for creating a database • Learning from Practice – Case Discussion • Steps in Creating a Database 5.1 Non-technical aspects 5.2 Technical aspects • Workshop closure ______________________________________________________________________________ 6 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge CURRICULUM PART 1 Workshop Introduction 1.1 Welcome (5 minutes) The representative of the agency hosting the workshop venue (agency whose facility is hired for the workshop) could welcome the participants and introduce the facilitator. Further, the representative could walk through the physical facilities such as the arrangements for coffee/tea, washrooms and telephone facility. 1.2 Introduction of facilitator and participants (Discussion, 5 minutes) The facilitator may choose to introduce herself/himself briefly in terms of directly related work experience and academic background. Then, explain the purpose of CERIS-PAC Project on Knowledge for Action and Action for Knowledge and how the present workshop is related to it. The participants could be invited to introduce themselves by name, job title and their organizational affiliation. 1.3 Review of agenda and objectives (Flipchart, discussion, 10 minutes) The facilitator could explain the agenda for the workshop including the break time and end time. S (he) could elucidate the goal and purpose of the workshop. S (he) could also explain the context and background expected of the participants. The facilitator could ask each participant to describe his or her expectations and learning objectives. Then the facilitator may consider amending the agenda to build in participant requirements. The facilitator needs to clarify that this workshop is intended for those who have basic literacy PART 2 The Database: Knowing about your clients and constituents (Overheads, discussion, 15 minutes) The facilitator introduces the use of databases and ascertains the participant’s familiarity with creating and using databases. Then provide an overview of database illustrated with examples of using databases in non-profit sector. Some examples of databases are membership directory, donor list and client profile. Ask each participant to name a database they have heard of. Then introduce the transparency on advantages of database. In order for the participants to appreciate the significance and judge for themselves its relevance and utility, the overhead sheet - 1 presents the advantages of creating and using databases. ______________________________________________________________________________ 7 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge 2.1 Advantages of a database The facilitator explains the benefit of savings due to integrated databases in cost, storage space, and paper usage and in avoidance of duplication. For example, basic particulars of a client such as name and address need not be keyed in more than once unless there is a change in address itself. Once entered, these particulars could be accessed for any kind of service provided to the same client by different units of the same agency. Database can store entire record of interventions at one place making it easy to reconnect and save repeated entry of same information. For clients this could mean less waiting time, as information is stored once and updated from time to time. Even if the client comes later on but does not remember the date of visit and registration number (if any), a primary key such as SIN will help retrieve client’s file(s). Further, in case of referral from a mental health centre to an advanced treatment facility, client’s particulars can be electronically transferred via Internet or floppy diskette. Database results in wide sharing of data across software applications. The system helps control the degree of errors in addition and modification of data. Further, in a database, security provisions are possible defining who can access what kinds of data and who can view and who can modify the data. The facilitator while discussing the points in the transparency elucidates other advantages such as consistency of data and security restrictions. For example, certain items categorized as confidential in client’s information file can be locked away from all but the counselors directly involved. An area wherein organizations could benefit is the efficient use of information as a resource. Organizationally, database could contribute to better coordination and timely reporting. Besides sharing the good points about database, the facilitator sensitizes participants about the costs involved in database creation. 2.2 Costs of creating a client database The facilitator could draw a balance by elucidating possible costs involved in developing a client database. There could be non-recurring or recurring costs. An agency would need to invest on computers. It would need trained computer professional(s) to support database work. Further, it would need to invest in training to make front line personnel computer literate. There are social costs too. Since a large number of agencies rely on volunteers, they may need to look for volunteers feeling comfortable and proficient in using computers for some of the work. The frontline workers and supervisory personnel may resent the technological changes, which demand changes in their skill sets and working patterns. Unless well planned, computerization could lead to demoralization of staff. ______________________________________________________________________________ 8 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge The facilitator needs to underline the significance of agency management weighing the pros and cons before making a recommendation to its Board as creation of a client database is an important decision. 2.3 Relational database Using examples the facilitator presents the basic structure of a database. For example in a student database, the information about each student is presented in terms of records and fields. The data about each student is a record while the column headings such as name of student, roll number, etc. denote fields. A set of related records is a file. A set of related files constitutes a database. When a database satisfies certain conditions, it is called relational database. In a relational database, a common key links different databases for ease of access and changes. For example, basic information like name, address and contact particulars are part of one database useful for all communications developed by the front office. Second database could deal with case history containing the problem; the diagnosis and the treatment plan depending upon the type of services availed by the client. The frontline worker or counselor could fill this in. The third deals with a chronological record of programs and services provided for the client by the agency. Similarly, SIN as a common key can help access a variety of databases of Health Canada, Revenue Canada and Citizenship and Immigration Canada. Here SIN works as a primary identification variable to connect different databases. Similarly, in an agency, services for a particular client could be brought together with the help of a common key like SIN. Last name could be difficult to use as more than one person could have the same last name. The participants could be asked to share examples in their day-to-day life to stimulate a discussion. The facilitator could offer clarifications. PART 3 Planning of a client database (Overheads, flipchart, discussion, 20 minutes) After discussing about the features of a database, the facilitator needs to motivate the group to come-up with ideas on planning a client database at the agency level. It needs to be emphasized that experience shows that often community agencies start work on databases in right earnest but the work does not always get completed due to problem of funds or lack of programming skills. Therefore the planning needs to address these aspects. ______________________________________________________________________________ 9 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge 3.1 Types of Planning Issues The facilitator explains that at the agency level there are four types of planning issues. These relate to policy making, administrative aspects, user perspective and the technical aspects. The breakdown of issues could stimulate discussion and sensitize the participants to pertinent issues at every stage of creating a client database. Concurrently it will enhance their competencies in planning of a client database. While discussing each of these, the facilitator could introduce appropriate overhead sheet for discussion. While facilitating the discussion on planning, the facilitator could underline the crucial role of board and management in addressing the critical planning issues rather leaving them to the discretion of technical personnel. In other words these issues are to be resolved by the overall team of an agency rather than computer/technical staff alone. In policy making process interaction between agency management and the Board are important as the latter needs to approve the proposal for client database. The Board needs to be informed of the benefits and costs while creating a database. 3.2 Production features of database The facilitator discusses the practical aspects of database production such as the action plan, the costing, the production team, dealing with confidentiality and software choice. These questions enable a discussion on selection of themes for database creation, the steps to be observed in collection of data and selecting appropriate software. 3.3 Sources of information for creating a database Methodological aspects of database creation to be discussed are how to choose between primary and secondary data and sources of data collection. While seeking information from immigrant groups and refugees, sensitivity to multiculturalism and gender-related aspects is vital. PART 4 Learning from Practice – Case Discussion (flipchart, markers, case illustration, discussion, 30 minutes) The facilitator invites the attention of participants to the case study titled, Skills for skills (Handout-1) included in the workshop curriculum. Allow sometime, say 5-10 minutes for the participants to go through the case illustration. The facilitator presents the case in detail for about 10 minutes focusing on how the process of creating a database was approached. ______________________________________________________________________________ 10 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge The facilitator could generate a very interesting discussion. Questions could be raised depending upon the background of the participants. Do the participants agree with the results, lessons drawn and the list of dos and don’ts enumerated in the case study? The facilitator could write on the flip chart two questions, `what would you do in this situation to complete the database?’ and `based on the case, how would you create a client database? The questions could be modified as per the situation. The participants could be divided into 2-3 groups and provided with marker pens and flip chart sheets to discuss the issue for 10 minutes and record their views and perceptions. A member each group could present the group’s thinking. The facilitator could thank the participants for the wealth of ideas generated and analyze the common aspects of the presentation and highlight the dos and don’ts in creating a client database. PART 5 Steps in creating a database (overheads, flip chart, markers, handout, computers with database software, group discussion, 45 minutes) The facilitator discusses the steps leading to creation of a database. While discussing the steps, the facilitator could distinguish the non-technical and technical aspects. In the realm of non-technical aspects, which are the most important part of creating a client database, the facilitator initiates a discussion by presenting the overhead sheet on Steps in developing a database (Non-technical). These points enable an agency executive to appreciate the role of technical and non-technical personnel within the agency, the role of Board and the role of clients in successful planning. 5.1 Non-technical aspects The first step calls for extensive interaction among the key stakeholders through an analysis of who wants what from the client database. The subsequent steps deal with the issue of hiring external expertise or using in-house talent to develop a database. These also concern allocation of resources, inputs and outputs. The technical or computer personnel come in at a later stage. They work as per the wishes of clients, staff and management. 5.2 Technical aspects The facilitator presents the overhead sheet on steps in creating a database-II Technical. These involve identification of fields, determination of data structure and development of physical tables. Participants attention needs to be drawn to the Handout – 2 titled, ‘About Designing A Database’ included in the workshop materials package. Enable the participants to go through the Handout for about 5 minutes. Highlight the importance of logical design in creating a database prior to getting to the computers. Logical design is the design component done without getting to the computer through extensive interaction ______________________________________________________________________________ 11 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge among the stakeholders on data inputs, reports required, confidentiality controls and frequency of updating. The facilitator initiates the discussion on the steps involved and moderates it. PART 6 Workshop Conclusion (5 minutes) The facilitator provides in about five minutes a quick recap of the major events in the workshop. Thank the participants for their participation and for their active participation in the discussions. Hand out the evaluation form with a request to fill it. Collect the filled-in forms. ______________________________________________________________________________ 12 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Handout - 1 SKILLS FOR SKILLS - CASE ILLUSTRATION OF SKILLS INVENTORY DATABASE AT LOW-INCOME FAMILIES TOGETHER Seetharam Mukkavilli, Ph.D. Acknowledgements The case illustration is based on a focused interview with Mr. Martin Baker, Coordinator, Community Economic Development at Low Income Families Together(LIFT), Toronto. Summary LIFT developed a database on skills inventory. Its aim is to find out skills people have and match them with people who want them. The case describes experiences of LIFT in developing and maintaining the database on skills inventory presented in three sections: description of the case, results from the effort, and lessons learnt. The section on lessons learnt is further sub-divided into Dos and Don’ts. SECTION – I: DESCRIPTION– I: DESCRIPTION Introduction LIFT, a non-profit organization interested in assisting the urban poor in the city of Toronto, searched for a suitable idea. Based on a needs assessment, LIFT decided to create a database of skills people have so that these could be bartered, exchanged or simply marketed. In this process, it hit upon the idea of surveying people to ascertain their skills. Goal Enhanced quality of life through exchanging skills for skills or selling skills. Objectives Enhance the availability of information about community members Help people to access skills from each other ______________________________________________________________________________ 13 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Assist poor to gain access to expensive skills through non-economic ways such as bartering Market skills of the poor for income generation Database creation The design of database was inspired by the work of John Kretzmann and John McKnight’s on studying your community*. These writers present a method and a tool for understanding the client system. Based on the methodology of assessing skills, LIFT developed a schedule with 180 fields to ascertain individual skills. The skills inventory was adapted from the book to suit local situation and LIFT’s requirements. The skills inventory was designed based on discussions at LIFT. Its design was marked by addition of new items to the inventory as time progressed depending on the interest of different persons – staff, management and visitors. Thus the questionnaire with 180 items evolved. In order to reduce costs in data collection, LIFT tried to collect information through telephone survey method. They found the response wanting as less than 5 % of the respondents were willing to cooperate. Features of Database at a Glanceof Database at a Glance Database Name Start Date End Date No. of Records No. of Fields Skills Inventory 1995 1996 50 180 In order to overcome the limitations of telephone interviews, mailed questionnaire method was used. The response rate increased to 50 %. Present Status The database creation was completed in 1996. Using the database, LIFT tried to assist people. The database offers potential for future programming for detailed processing and analysis. Besides change of people and priorities, a critical factor is having the programming expertise in the organization. It needs further programming and processing to ascertain the relationships among the variables. The organization finds it difficult to hire computer professionals on account of resource crunch. ______________________________________________________________________________ 14 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge SECTION - II: RESULTS As the amount of caution among respondents was not high, they listed far too many skills even without adequate competencies or training. The implications of giving inaccurate information were not adequately realized. The innovative effort succeeded in matching skills people have with those needed or required. Poor who could not afford expensive labor costs could secure services by bartering skills they possess. The fields were increased to accommodate the interests and suggestions of people. The final tally was 180 fields. As the objectives were global, huge amount of data was collected. The collection process was time consuming moving from Tele-survey with low response to other methods. A deterrent factor was the length of the questionnaire with 180 fields. The more intensive the questions, the more difficult was the creation of an authentic database. 7. The creation process threw up questions such as: * How to draw up the format for database? * How to use the results? * How to program your needs? 8. LIFT’s experience led to similar initiatives by other not-for-profit organizations to build databases with demonstration effects. SECTION – III: LESSONS Do’s The collection of data needs sensitive investigators. It could help reduce resistance and motivate the respondents to be more truthful. This will make the database more realistic and purposive. Focus on accuracy of database being developed. ______________________________________________________________________________ 15 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Have simple and clear objectives. Decide ahead how to use the results of database Constitute a small group to decide on the database Pre-test the fields for validity, simplicity and redundancy Program needs ahead. Know the costs before you start database creation. DONT’S Get too much of data – everything. Let everyone go on adding many things on what to collect. *Kretzmann, John and John McKnight Building, Communities From Inside Out , 1993 ______________________________________________________________________________ 16 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Handout - 2 ABOUT DESIGNING A DATABASE Introduction This handout is being written from the point of view of the database design people. Actual design of database by technical people has to be preceded by several steps such as: • Consultations among Board, management, staff and clients on having a database • Investigation of the options –use in-house or hire personnel, cost implications, etc. • Management’s permission and ratification of decision by Board to have a database It needs to be clarified that design process is not led by the computer programmers. The design of database must be led by the needs of the agency as seen by the Board, management and staff. Programmers must work to respect and implement this rather than impose an unsuitable computer solution because it is technically feasible or attractive. Before you use a computer package like Microsoft Access to actually build the tables, forms, and other objects that will make up your database, it is important to take time to design your database. A good database design is the keystone to creating a database that does what you want it to do effectively, accurately, and efficiently. Basic steps in designing a database are: 1 Determine the purpose of your database. 2 Determine the tables you need in the database. 3 Determine the fields you need in the tables. 4 Identify fields with unique values. 5 Determine the relationships between tables. 6 Refine your design. ______________________________________________________________________________ 17 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Add data and create other database objects. 1 Determine the purpose of your database The first step in designing a database is to determine the purpose of the database and how it's to be used. You need to know what information you want from the database. From that, you can determine what subjects you need to store facts about (the tables) and what facts you need to store about each subject (the fields in the tables). Talk to people who will use the database. Brainstorm about the questions you'd like the database to answer. Sketch out the reports you'd like it to produce. Gather the forms you currently use to record your data. Examine well-designed databases similar to the one you are designing. 2 Determine the tables you need Determining the tables can be the trickiest step in the database design process. That's because the results you want from your database — the reports you want to print, the forms you want to use, the questions you want answered — don't necessarily provide clues about the structure of the tables that produce them. It may be better to sketch out and rework your design on paper first. When you design your tables, divide up pieces of information by keeping these fundamental design principles in mind: · A table should not contain duplicate information, and information should not be duplicated between tables. When each piece of information is stored in only one table, you update it in one place. This is more efficient, and also eliminates the possibility of duplicate entries that contain different information. For example, you would want to store each customer address and phone number once, in one table. · Each table should contain information about one subject. When each table contains facts about only one subject, you can maintain information about each subject independently from other subjects. For example, you would store customer addresses in a different table from the customers' orders, so that you could delete one order and still maintain the customer information. 3 Determine the fields you need Each table contains information about the same subject, and each field in a table contains individual facts about the table's subject. For example, a customer table may include ______________________________________________________________________________ 18 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge company name, address, city, state, and phone number fields. When sketching out the fields for each table, keep these tips in mind: · Relate each field directly to the subject of the table. · Don't include derived or calculated data (data that is the result of an expression). · Include all the information you need. · Store information in its smallest logical parts (for example, First Name and Last Name, rather than Name.) 4 Identify fields with unique values In order to connect information stored in separate tables for example, to connect a customer with all the customer's orders, each table in your database must include a field or set of fields that uniquely identifies each individual record in the table. Such a field or set of fields is called a primary key. 5 Determine the relationships between tables Now that you've divided your information into tables and identified primary key fields, you need a way to bring related information back together again in meaningful ways. To do this, you define relationships between tables. 6 Refine the design After you have designed the tables, fields, and relationships you need, it's time to study the design and detect any flaws that might remain. It is easier to change your database design at the design stage, rather than after you have filled the tables with data. Use any software like Microsoft Access to create your tables, specify relationships between the tables, and enter a few records of data in each table. See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplications of data and eliminate them. 7 Enter data and create other database objects When you are satisfied that the table structures meet the design goals described here, then it's time to go ahead and add all your existing data to the tables. You can then create any queries, forms, reports, macros, and modules that you may want. Source: Adapted from the Microsoft Access help menu in Microsoft Office 97. ______________________________________________________________________________ 19 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead Sheet-1 WHAT ARE THE ADVANTAGES OF A DATABASE? Integrated database gives an agency “centralized control” of its operational data (instead of each unit/application having its own private files, that too widely dispersed) Capacity to meet overall requirements of organization Ability to have a person with identifiable responsibility- a database administrator Control of Data Redundancy Redundancy can be reduced/controlled with centralised control as private files duplicate data causing wastage of storage space. Disadvantages of data redundancy are: • Collecting the same data again(like client’s SIN,address) • Wastage of storage space(separate files for each intervention) • Wastage of processing time as more data is handled • Possibility of inconsistencies creeping in(by setting limits) • Other operational costs could increase Maintenance of Integrity As a corollary of redundancy, inconsistency can be avoided as integrated database is incapable of supplying incorrect or conflicting information. While updating, one entry will update all items ______________________________________________________________________________ 20 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Helps in correctness, consistency and interrelationship of the data in relation to an application Data items will only take a restricted set of values Sharing of Data Many applications can share the data New applications can use existing data Control over Security Database provides scope for security restrictions - user codes and passwords - controlled access Processing Speeds Importance of speed in converting ‘raw’ data into ‘information’ Significance of ‘ease’ in processing information Hardware independence Flexibility for use across hardware platforms and operating systems ______________________________________________________________________________ 21 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead sheet - 2 PLANNING OF A CLIENT DATABASE TYPES OF ISSUES (4) 1. Policy and long-term planning issues/Board Issues • Agency’s long term policy on database creation and use • Purpose and role of database in agency’s programs and services • Agency’ accountability to its stakeholders • Agency’s policy on client confidentiality • Board’s expectations from database in terms of reports • Source of funds for additional costs to set-up and maintain database ___________________________________________________________________________ 22 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead sheet - 3 Administrative Issues • Planning, recruiting and supervision of specialized personnel • Re-organization of staff tasks and resultant changes in responsibilities • Ensuring quality of work at development stage • Types and frequency of reports desired by agency management • Types of reports and their frequency as desired by the funders • Production and distribution of reports • Gathering and communicating feed back on performance of client database ___________________________________________________________________________ 23 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead sheet - 4 User (front-line staff and clients) Issues • Availability of the desired information • Willingness of people to share information • Does the information sought of clients too demanding on their time • Is the information sought adversely impacting on workload of front line workers • Does the database make the job easier or difficult • What will the clients gain from the reports in order for them to cooperate in providing information ___________________________________________________________________________ 24 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead sheet - 5 Technical issues • Hiring of computer and technical staff • Enhancements needed for system resources in the agency • Programming and documentation • Design and production of reports • Programming to build in security features of data and its access • Arrangements for regular backup of data at fixed intervals • Ensuring maintenance of the system for low breakdown • Addressing needs of staff for debugging, support and training ___________________________________________________________________________ 25 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead sheet - 6 STEPS IN CREATING CLIENT DATABASE - I NON-TECHNICAL • Preparation of briefing materials - Brief note on creating a client database - Possible advantages and costs involved • Consult with all levels – Board, management, staff and clients to assess feasibility and to: - find-out views, expectations and suggestions to create a client database - need for information, reports and outputs from database • Management’s recommendation to the Board on all aspects including hiring, confidentiality and resource implications • Board approval of the decision to have a client database ___________________________________________________________________________ 26 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge • Management’s hiring of expertise as needed and problem analysis • Preparation of an implementation plan for client database • Consultations with staff and clients on inputs and outputs with management’s participation • Technical people take over design of client database with: - on-going supervisory role of management providing clear direction to the technical people on outputs desired, input availability and long-term resource implications ___________________________________________________________________________ 27 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead sheet - 7 STEPS IN CREATING A CLIENT DATABASE-II TECHNICAL • Identify required data (what information is needed and for what objective) • Collecting identified fields into tables(arrange fields identified into logical tables) • Identification of primary key fields • Draw simple data diagram( draw tables with key identifier) • Normalize the data (remove redundant data from database) ___________________________________________________________________________ 28 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge • Identifying field specific information* • Create field and table names • Identify data types for the fields(numeric, text, etc.) • Determine need for validation rules • Creating physical tables (creating physical database tables) ___________________________________________________________________________ 29 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Overhead Sheet - 8 COMMON DATABASES RELATING TO CLIENT/COMMUNITY READILY AVAILABLE FROM MS ACCESS • Membership • Asset Tracking • Donations • Book Collection • Contact Management • Event Management • Expenses • Household Inventory • Students and classes ___________________________________________________________________________ 30 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge ADDITIONAL COURSE NOTES USED IN PREVIOUS WORKSHOPS Sheet-1-1 WHAT ARE THE ADVANTAGES OF A DATABASE? Integrated database gives an agency “centralized control” of its operational data (instead of each unit/application having its own private files, that are too widely dispersed) Ability to have a person with identifiable responsibility- a database administrator Redundancy can be reduced/controlled with centralized control as private files duplicate data causing wastage of storage space. As a corollary of redundancy, inconsistency can be avoided as integrated database is incapable of supplying incorrect or conflicting information. While updating, one entry will update all items. The data can be shared. Using existing data, new applications can be developed. Enforceability of standards Scope for security restrictions Capacity to meet overall requirements of organization/enterprise ___________________________________________________________________________ 31 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Sheet-2 WHY A DATABASE MANAGEMENT SYSTEM ? • Realization of value of information as a resource by organizations • Importance of speed in converting ‘raw’ data into ‘information’ • Significance of ‘ease’ in processing information • Investment towards collection of data and on keeping it up to date. • A rapidly growing area in information technology ___________________________________________________________________________ 32 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Sheet - 3 BASIC CONCEPTS IN DATABASE RECORD A record is a collection of related items or fields of data treated as a unit. FIELD In a record, the smallest meaningful collection of one or more related characters treated as a unit is called field. FILE File is a set of related records. DATABASE Database is a set of related files. DATABASE MANAGEMENT SYSTEM (DBMS) DBMS is a set of software programs that controls the relationship, storage and retrieval of data (fields, records and files) in a database. It also controls the security and integrity of the database. CONCEPT OF RELATION A relation is a collection of tuples, each of which contains values for a fixed number of attributes. ___________________________________________________________________________ 33 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge ATTRIBUTE One “column” of a relation is an attribute. Each row, has a value for the attribute. The attribute generally describes some feature or property of the objects presented by the relation in which it occurs. DOMAIN The set of permissible values for each column or attribute is called the ‘domain’ for that attribute. TUPLE One ‘row’ of a relation is called a ‘tuple’. It consists of values for each attribute of the relation. ___________________________________________________________________________ 34 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Sheet-4 CHARACTERISTICS OF DBMS Control of Data Redundancy Disadvantages of data redundancy are: • Collecting the same data again • Wastage of storage space • Wastage of processing time as more data is handled • Possibility of inconsistencies creeping in • Other operational costs could increase Sharing of Data Many applications can share the data New applications can use existing data Maintenance of Integrity Helps in correctness, consistency and interrelationship of the data in relation to an application Data items will only take a restricted set of values Support for Transaction Control and Recovery Enables multiple updates to the database. Physical updates take place only when the logical transaction is complete. ___________________________________________________________________________ 35 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Data Independence Availability of Productivity Tools Tools are query languages, screen and report painters and other GL tools Enhanced programming productivity Enhanced end-user computing Tools enable queries, query-replies, formatting replies and printing reports Control over Security user codes and passwords controlled access Processing Speeds Hardware independence Flexibility for use across hardware platforms and operating systems ___________________________________________________________________________ 36 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Sheet-5 PLANNING OF A CLIENT DATABASE Some questions to answer Rationale for database Why create the database? What are the antecedent conditions? What are the objectives? Whom will the database benefit most? B. Description of database. What is the title? What does it describe? What type of data do we need? How should the data be used? ___________________________________________________________________________ 37 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Production features of database Who will produce it? How much will the production cost? When will it be produced? What is the software to be used and its compatibility with your internal computer system? Who will be responsible and for how long? What will be the security requirements for access/use of database? Would confidentiality be an issue? Whom should the users contact for information on the database? Do you plan to link the database to any other on-line database? Would you like to post it on your home page or web and link to search engines? Sources of creation Where would the data come from? What is the required level of validity and reliability for the data? What will be the degree to which the sample adequately represents the universe? ______________________________________________________________________________ 38 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge How often will the data be updated for changes in client community? Is the data being gathered sensitive to multi-culturalism and gender related factors? How much of it will originate from primary sources and how much from secondary sources? How will the data be collected, processed and analyzed? Can this data feed into other databases? Will it meet standards/compatibility requirements? Would other constituents such as other agencies, funders and research agencies be involved in database design? What will be the reference period for the data to be collected? ______________________________________________________________________________ 39 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Sheet - 6 PROPERTIES OF RELATIONAL DATA STRUCTURES There are six properties to be satisfied for a table to be classified as `relational’. • Entries of attributes are single valued • Entries of attributes are of the same kind • No two rows are identical • The order of attributes is unimportant • The order of rows is unimportant • Every column can be uniquely identified ______________________________________________________________________________ 40 How to Create a Client Database CERIS –PAC Research Training Project Knowledge For Action – Action For Knowledge Sheet-7 STEPS TO CREATING A RELATIONAL DATABASE Identify required data (what information is needed and for what objective) Collecting identified fields into tables (arrange fields identified into logical tables) Identification of primary key fields Draw simple data diagram (draw tables with key identifier) Normalize the data (remove redundant data from database) Identifying field specific information* Create field and table names Identify data types for the fields (numeric, text, etc.) Determine need for validation rules Creating physical tables (creating physical database tables) ______________________________________________________________________________ 41 How to Create a Client Database