Database Design: An Example

Previously, I wrote that I would post things I learned in my database training so that others can use them as they see fit. Considering my two days worth of inability to fix the loop in PHP in my performance management information support system, allow me to take a moment to stop coding and write about things I learned and how I applied it to the project I am working on. This way, I think I can initiate my intent of sharing my knowledge in learning while reviewing why I designed the database for my project that way.

First and foremost, information system development is and should be a deliberate and systematic process. I would not talk about that process as it is a combination of some system development things and management and organizational requirement, with the two needing to coordinate (and I mean TALK) closely if the organization intends to make the system really responsive to their requirements. If the two do not, don't expect the system to be helpful (i.e., easy to use, will do what the organization needs, and will be a SOLUTION INSTEAD OF BEING AN ADDITIONAL BURDEN).

Nonetheless, in the interest of sharing basic knowledge (which is my current level on the matter), here are the basic phases as proposed by Hoffer et al in their book, “Modern Database Management” 11th edition:

Project Development Phase	Purpose
Project ID and Selection	Develop a preliminary understanding of the business situation that has caused the request for a new or enhanced IS
Project Initiation and Planning	State business solution and how IS might help solve a problem or make an opportunity possible
Analysis	Analyze the business situation thoroughly to determine requirements, to structure those requirements, and to select among them competing system features
Logical Design	Elicit and structure all info requirements
Physical Design	Develop all technology and organizational specifications
Implementation	Write programs, build data files, test and install the new system, train users and finalize documentation
Maintenance	Monitor the operation and usefulness of a system, and repair and enhance the system

My project was personal so it did not go through phases 1-4 before going to phase 5. Rather, based on my understanding of the process (which, in case you missed it in the first paragraph, was about performance management), I dived into coding immediately. I knew that the project would be big and my basic requirements were not complete when I started it. And those considerations affected my database design, as I wanted it to be robust and scalable.

And with that, let's go now to data modeling.

A data model is, for our purpose now, how an organization views, uses, and processes data. As such, it is a biased view of how the organization sees things, as things are represented by data. Data modeling, then, “establishes the range and general contents of the database,” to “to create an overall picture or explanation of organizational data,” as Hoffer et al explained in their book.

For a clearer (and nuanced) example, let's take a look at performance management in the Philippine bureaucracy.

All government agencies report to many oversight agencies, and in performance management, this is not only to one agency. For those who do not know, below are three oversight agencies that any other agency reports to, with their corresponding reporting arrangements:

• Department of Budget and Management (DBM) – for organizational performance
• Civil Service Commission (CSC) – for individual performance of rank-and-file
• Career Executive Service Board (CESB) – for individual performance of managers

As you can see, performance of any one person will need to be aligned with all of these, somehow.

• DBM, through the Organizational Performance Information Framework, or OPIF, looks at the agency-level organizational performance, which is measured by how much an agency performs against its set Major Final Outputs, which are, among others, an agency's mandated goods and services. These MFOs are clearly identified in the national budget, as enacted by Congress (through the Performance Informed Budget, or PIB) through the General Appropriations Act (as such, each MFO has a budget, similar to marketing programs in the private sector).
• CSC looks at the performance management of the rank-and-file, which is currently measured in terms of accomplishment of each individual's performance contract, or PC. The PC, which sets the target (similar to the agency's MFOs), will be the basis of scoring their accomplishment during performance evaluation period. Currently, this performance management framework is called the Strategic Performance Management System (SPMS).
• CESB, through the Career Executive Service Performance Evaluation System, or CESPES, looks at the performance management of the bureaucracy's managers (i.e., Assistant Director and higher). Similar to the CSC's SPMS, the bureaucracy's managers craft a performance contract with their supervisors for performance targets, which will serve as basis for evaluating their performance at the end of the rating period, which is currently one calendar year (Compared to the CSC's SPMS, the period is usually six months, although an agency can have a shorter period of minimum 3 months or longer, up to a maximum of one year).

Here's a summary of the basic aspects of the three performance reporting systems:

System	Coverage	Performance Report Prioritized	Rating Period
DBM - OPIF	Organization	Accomplishment of agency-level, agency-nominated MFOs	Quarterly
CSC - SPMS	Rank-and-file employees	Accomplishment of agreed upon performance targets, which should be aligned with agency MFOs	Semestral, with provision for shorter (minimum of 3 months) or longer (maximum of 12 months)
CESB - CESPES	Managers	Accomplishment of agreed upon performance targets, which should be aligned with agency MFOs, with additional consideration for leadership and innovative outcomes	One calendar year

Based on this, you could see that if we are going to create an performance management information system, it should incorporate these considerations.

Additionally, the CESB created a hierarchy of performance objectives, as follows (based on Section 3 of CESB's Resolution 1136):

• Organizational [Strategic] Objective – broad statement of goals of the organization achievable in the medium term (3-5 years) in order to achieve its mission and vision.
• Office Performance Objective – enumerates the specific area/s of performance that is/are critical in accomplishing the strategic objective/s of the organization. Multiple Office Performance Objectives may contribute to an Organizational Objective.
• Ratee Performance Commitment – refers to the Ratee’s strategy or plan of action that he/she personally commits in order to contribute to the performance objectives.

In the Presidential Management Staff, we harmonized the three, in terms of performance objectives, by assigning its MFOs as CESPES' Organizational Objectives, and the CESPES' individual performance targets in the performance contract as the Ratee Performance Commitment. The Office Performance Objective is derived from clustering of related individual performance commitments, which are organized by groups or units to achieve common and related outputs or outcomes.

This harmonization was accomplished through the Results-Oriented Performance Management System, or ROPMS (the system which the PMIS will provide information support).

In terms of organizing the hierarchy of performance objectives, one can see that:

• The agency must have at least one MFO (for OPIF) / Organizational Objective (for CESPES).
• An MFO / Organizational Objective may have one or more Office Performance Objective.
• An Office Performance Objective should be aligned with only one MFO / Organizational Objective (this is for purpose of performance evaluation: we do not want one action to be rated more than once if it will belong to different MFOs).
• Each of a Ratee's (both rank-and-file and managers) performance commitments should be anchored on one (and only one) Office Performance Objective.

From a database management perspective, it would look like this:

Organizational Objective table

Organizational Objective ID	VARCHAR* 15**
Long Description	VARCHAR 500

*VARCHAR is one of the types of data used in programming and database design. It means variety of characters, I think. Among the other types are DATE, INTEGER (INT), and BOOLEAN.

**Number of characters are only for sample. For the Organizational Objective ID, it is only 15 because it is only an acronym of the long name indicated in the Long Description field.

Office Performance Objective table:

Office Performance Objective ID	VARCHAR, 15
Long Description	VARCHAR, 500
Organizational Objective ID (Foreign Key)*	VARCHAR, 15

*The foreign key is a “restraint,” which is setting a control in the system that in order for this office performance objective to be added, it must be connected to an existing organizational objective.

Ratee Performance Commitment table:

Ratee Performance Commitment ID	INT
Long Description	VARCHAR 500
Office Performance Objective ID (Foreign Key)	VARCHAR 15

As I mentioned before, when I dived into coding, I was aware that the system will have to be able to scale, meaning it should be able to accommodate additional requirements or conditions, and for more than the reasonably medium-term set of assumptions. Meaning, while the PMS' current MFO on decision inputs focuses on final briefing kits (eg., documents for the President's events or meetings) and action on requests from the public, these may change in the future, and the system should be able to accommodate it (just by changing the type of output to be counted).

As such, I have to modify the tables as follows:

Organizational Objective table:

Organizational Objective ID	VARCHAR 15
Long Description	VARCHAR 500
Date Start	DATE*
Date End	DATE*

*This is to indicate that an organizational objective may cease, however, for purpose of recording, we would keep it in the system instead of deleting it (which would corrupt the relationship structure as that would mean that office performance objectives may exist without organizational objectives).

Office Performance Objective table:

Office Performance Objective ID	INT, AUTO-INCREMENT*
Long Description	VARCHAR, 500
Organizational Objective ID (Foreign Key)	VARCHAR, 15

*Changed to INTEGER, meaning the system will assign the number. This will ensure that, upon nomination of an office performance objective, it would be unique. The AUTO-INCREMENT indicates that the system will increase the numerical value automatically.

It may also be noted that the office performance objective does not have time consideration. This is because its effective period is the same as that of its parent organizational objective.

Ratee Performance Commitment table:

Ratee Performance Commitment ID	INT
Long Description	VARCHAR 500
Office Performance Objective ID (Foreign Key)	INT*

*Changed the data type into integer, as it must be the same as the one it references.

The above tables, which are called physical database design, should be able to cope with changes in an agency's MFOs. Also, considering that the office objectives are separated from the office themselves, an organizational restructuring would not require changing of the code (assuming the user interface sets   up a facility of reassigning office objectives, which I did for my project). Below image shows how the three tables relate:

However, if the DBM or CESB changes the way it looks at performance and budgeting (i.e., changes the OPIF altogether), it would require a major review and updating of the system and the database structure.

The above narrative, hopefully, demonstrates sufficiently how database design works (Note that this is only for one aspect of the process. Your own process would most probably have more than one, each of which you have to consider for relationship with other tables, databases, processes, and user requirements.) It requires an understanding of the business' requirements and look at its operations, including the information and data required for and gathered from these operations, not just a pure technical appreciation of database design or programming. As such, managers should also take the time to understand this (not the technical level, but an appreciation for the opportunities and limitations) to make informed decisions and not be limited by the technical people's say so.

Final Note: Above narrative is a simplified version of the tables. For security reasons, some additional columns, reflecting additional organizational requirements, have been removed.

Quick Updates and Random Thoughts

Web-Based Performance Management Information System

I have to abandon the Google Fusion Tables-based performance information system as it is limited. Of course, that is not the only reason. The other, and real reason is that I got to actually make my own PHP-MySQL-based system. Although it is only at its initial stage (my most important achievement is a username-password protection system that ensures no page is accessible without proper login), I feel confident that I can achieve my intended system.... although when is another question.

Running and Performance Management 

Running is really a good exercise. But it does not only help one in physical fitness. It also helps me in approaching personal performance management.

When I run, I do not run alone. I do not run with a group, but the place I run is an ideal running area, with vehicles' speed in check, paved road, and less vehicles due to the security setup of the area.

Nonetheless, while I see other people run, I am reminded of the following to help me keep my pace, and ensure sustainability in my performance:

1. Keep my own comfortable pace. Focus on the distance of the run without incurring injuries.
2. Focus on my own performance. Do not compete against other runners. Other runners may have different reasons for running, which drives the way they run. I know why I run, and I will try to sustain it. 
3. Focus on the run. Do not think about other things. When I do, I slow down, and my mental activity increases fatigue.
4. Know how to prioritize. If you want to finish a big thing, you may need to abandon or remove secondary objectives. For example, as I hope to finish a 42-kilometer run in October, I am less inclined to target a sub-2 hour 21-kilometer. It's okay for me to finish it in 2 hours and 15 minutes, just five minutes faster than my last record. 
5. If you want to achieve something, do not compete with someone else. Compete against your own limitations, so that you improve on the measures that you need to and not based on some else's measures.

These are just some of my thoughts on comparing performance management with running.

New Spreadsheet Function Learned

So I learned how to use spreadsheet functions INDEX and MATCH to do two-dimensional or two-factor lookup. I will write about it in my next post in Technology for Teachers blog.

Introduction to Database and Database Management

I'm thinking of organizing a brief knowledge-sharing session about database next week. I hope to share what are the opportunities in having a "real database." Below my preliminary syllabus:

Learning Outcome: At the end of this 2-hour session, participants are expected to have a working knowledge and appreciation of database, and database design and management, to help them in efficient data collection, storage, reporting, and ultimately, decision-making.
                     
Among others, the session will allow the participant to answer the following questions and apply that knowledge to their own situation/requirements:

1. What is a database?
2. What are the uses of database (aside from getting stored data)?
3. How should we design databases (and why so)?
4. What are the available tools and techniques in processing data in a database?

During the session, participants will analyze available data forms or table reports to design a data collection tool that will allow the user use and “re-use” data.

Methods of Instruction: Guided discussion, paper form analysis, and tool application

Logistical Requirements:

• Projector and laptop with internet connection and spreadsheet application
• Sample report forms for analysis
• Participants may bring their own laptop for exercises

Session Participants: To maximize the learning experience, it is recommended that participants be limited to those who:

• Use database-like applications (Microsoft Excel) on a regular basis; or
• Use matrix reports from agencies that want to reuse data.

Session Size: Due to the interactive nature of this session, a maximum size of 10 is recommended.

Database Design for Non-IT People

As a member of the Quality Assurance Team and a member auditor of our Internal Audit Committee (both of which are component entities of our Quality Management System), I observed that there are a lot of offices which maintain some form of database. And that is good.

However, as someone who was trained in database systems (however, this is by no means saying that I am an expert on the subject), I also observed that their databases are mostly 'designed' to respond to only one particular concern, which is storage of data and ensuring that they can find a particular type or instance of data for each particular record.

That in itself is not a bad thing, however, I find myself wishing that they would enhance their database so that it can be more robust, more flexible, and has opportunity for scaling up and integration.

Some of the staff who thought of creating an electronic database used Excel, a powerful spreadsheet application, are conversant with advanced knowledge of the application, which means they know more than simply typing values there and using basic arithmetic formulas using absolute and relative references.  However, choosing Excel, or other spreadsheet application like LibreOffice Sheets, to use as a database management system is an indication that the choice did not consider the long-term opportunities of making other management decisions using the same existing data by merely organizing the data properly.

Still, I think that advanced skills in spreadsheet applications is an indicator that they can easily understand the basic concepts of database management.

Considering, this, I really want to share what I know about database management to these people so that they can be more conversant opportunities in proper database design so that they can easily find the information and knowledge they want from the data already in their computers and files.

I might post some future articles on the matter so that they may be able to read it, in case they want to learn and we just don't have the time to do it during office hours.

Update on the Google-based Performance Information System

So after going through three different options (LibreOffice Base/Microsoft Access, Google Fusion Tables, and developing my own local system) in creating our performance information system, I finally settled in using Google Fusion Tables.

Previously, I was afraid that the system would be riddled with information integrity and access restriction problems. For example, I was afraid that one unit can enter data for another unit, which should not be the case. To mitigate that problem, I created an internal key reference system. What it was supposed to do was to present to the oversight office his own reference key and the submitter's secret key. If the keys are the same, then the oversight office should consider the record valid and he or she can rate the output recorded. However, if the keys are different, he or she can check with the supposed unit why the keys are different before giving a rating.

Personally, I was not keen in that system being observed. The oversight offices are "very busy" people. They would not bother themselves with security procedures. So I thought that our unit would constantly monitor the keys by creating a merge of the key reference table and the output submission table (I know, the references might be difficult to understand. It's by design.).

However, just today, I learned a way to altogether remove the key reference system. Instead, the access would automatically identify the submitter as the user based on his or her Google login.

The process not only removed the threat of shared access rights to create records, but also removed two fields (columns) in the unit's view of the database.

It was brilliantly simple, and more secure.

Now time to go back to work.

Performance Information System using Google Fusion Tables

I finished last Friday 5 out of our 14 units' performance contracts (PCs) in our Performance Information System. Previously, 8 were entered by another staff, so there is only one unit remaining PC to be entered. After that, I have to review the records entered prior. With populating the commitments table almost finished, I have to go back to writing the concept and how-to-use document.

I have written a number of times on the performance information system, so my organization in writing is kinda messed up. I hope I would be able to fix this sooner as I hope to finish the system mid-September (revised timeline, I know). When I say "finish," I mean it should be pre-populated and there should be a ready document telling what it is, what it is for, and how to use it.

For managers, of course.

For managers who have no time to listen to how this system works, or how they should use it.

Don't get me wrong. I'm not complaining. It's just my observation and statement of my anticipation to my presentation being welcomed by either long yawns or questions on the integrity of the system, doubt about the effectiveness of the system, or question about the wisdom of using properly their executive time, or a combination of these and other scary things.

I'm just being realistic.

Since 2013, I have really thought about this. What I can only say is that I see some hope of this being used. So despite being scared, I am continuing. Studying how to make this work, without overtime compensation, of course.

The development of this information system also brought some questions on our performance management system as well. Questions on small details that accumulate and impact the things that matter the most to the units: the score.

Anyway, I will just stop here, I only wanted to introduce the document, which is still a ROUGH DRAFT (my working file).

It's here.

Performance Management Information System Development Project

I want to document my efforts to develop our performance management information system (my second attempt, after using Google Fusion). As my attempt to outsource the project does not seem to yield fruits (because student developers are not familiar with the performance demands of our organization), I have to do this on my own.

While I previously wanted to develop a web-based, database-driven, enterprise-wide information system, that plan probably won't happen. For this year, my target is to develop an inter-related database process that will mimic that enterprise-wide, web-based information system.

I will use, with much hesitation and fear of regret, Microsoft Access 2013 (the software used in the office). But for development, I will use LibreOffice Base. I hope that everything that Base can do can be replicated in Access.

My plan for this project:

Phase I - Requirements and Process Analysis (Target: End-April 2015; Weight: 25%)

1. Software purpose 
• Baseline 
• Intended Outcome
2. Stakeholder requirements identified
3. Processes (including business rules) identified

Phase II - Database Development (Target: End-June 2015; Weight: 40%)

1. Entity relationship model 
2. Database logical and physical design 
3. LibreOffice Base prototype
• Database back-end
• Version per user-type
• Reports

Phase III - Implementation Conversion (Target: End-July 2015; Weight: 25%)

1. Microsoft Access 2013 version
2. FMPS-level testing
3. System marketing, to include feedback management
• End-user offices and staff
• Process-owner offices and staff (i.e., FMPS, HRDMS)
• Management offices and staff (PMT)

Phase IV - Cascading and Maintenance (Target: August 2015; Weight 10%)

1. Software installed in all offices that indicated interest
2. Feedback continuously gathered for improvement

Resources: All open source software:

• ProjectLibre - For project monitoring (even if this is a self-monitored project, I have to monitor my progress against my targets, for reporting to my supervisors)
• LibreOffice - For the database development (Base) and project documentation (Writer)
• FreeMind - For various concept and role-mapping tasks
• Dia - for database design

S2: My Brief Experience in Cloud-Based Database Design and Google Fusion Tables

My last post was about Google Calendar, and the ones before that were about the Philippine power sector. I will not talk about either of those, but instead talk about my brief stint in designing a database (and user interface) for our staff performance evaluation and tracking system.

After our last performance evaluation exercise, I saw the need to create an information system that supports a real, learning-oriented, knowledge management (KM)-oriented staff performance evaluation system. After reading a few literature about the concept (notably, the Strategic Performance Management System and the RA 6713, or the "Code of Conduct and Ethical Standards for Public Officials and Employees"), I designed the entity relationship diagram. You can read my concept proposal here.

Obviously, it was a self-imposed proposal that I was excited about so I went ahead and spent time and money (for staying at Starbucks... oops!) to contemplate, design, correct, recreate and re-do the database backend and the individually oriented user interaces. As a learning exercise, I tried to document as much as possible, which led to the user manual here.

But that is a big jump from the proposal to the system I was able to finish (well, almost. It still lacks the policy decision on how much is the weight of each evaluator's grade, and what are the criteria of evaluation). Obviously, before that, there were a lot of choices.

I have heard of Google Fusion Tables when I was still working at UP Manila, but it seemed too technical to me. I didn't have training on database then, too, so I did not even have the conceptual appreciation then. However, when I went to PMS, I already had the appreciation of database design and management, so I was able to appreciate Google Fusion Tables.

I needed a system that would do these things:

1. Allow the staff to enter records about their outputs.
2. Allow supervisors to evaluate their staff's outputs, but only access outputs of their own staff (direct reports) and not edit details about the output. Also, supervisors cannot change the grade other supervisors give to that output of that staff.
3. Automatically calculate overall rating for each output (because each output is rated by many evaluators, and each evaluator makes an evaluation on many criteria).
4. Allow supervisors to quickly know the current standing of that staff based on given evaluations.
5. Allow staff to see evaluation (and constructive feedback) on their outputs, as well as summary calculation of their standing.
6. Download the data.
7. Make special reports, based on what the the data the system gathers. 

At first, I wanted to use Google Forms, which would then feed the data into a Google Sheet. Google Forms can be easily created, and it also allows conditional data-inputs and required fields. Google Sheets allows range-based access, meaning I can designate which areas in a spreadsheet a person can edit. For example, I can give one Supervisor A permit to change (input and edit) data in Column I only, while Supervisor B will enter the grade in Column AA, and Supervisor A cannot change any part of the sheet other than those columns. Google Sheets allows strong data crunching functions, due to its Pivot Table function. The two are almost perfect. Well, I said almost.

The problem is that the Sheet is a very big online document, which any manager, unless I were that manager, would not want to go to. It is just a perfect example of information overload. Working for an organization that attempts at every moment to lessen the load to the bosses with the belief that they have more important things to do than read your complete and comprehensive input, Google Sheets was simply not an adorable, manager-level type of information system that they would adopt.

Remembering Google Fusion Tables when I was still studying an online system for program monitoring database, I re-read the system and what it can do, and implemented the Staff Performance Information and Evaluation System with that as the technology base. 

With Google Fusion Tables, I was able to create input forms (well, actually, input tables) for each staff, some data of which will enter in the unique user interface of supervisors so that they can grade them. The supervisors only see records of outputs which they have not evaluated yet. Once they evaluate a record and close the window, and open it again, those record will no longer appear. 

On another view, the staff can see the evaluation of the supervisors (if they have already done that), but they cannot edit it. They can only see their own records, not those of other staff. And unit heads can only see records under their individual unit, not those of others. 

As it is a database, one can easily manipulate the presentation to suit the needs of managers. 

Unfortunately, it was not utilized, so I was not able to test its full operational capability - that is, simultaneous multiple users using the database. I did the "alpha" testing alone, if that would be considered as alpha testing. 

I sure hope that I would get the opportunity to implement a system like this. I had hoped to integrate this with our dashboard, but even that would probably just go to my charge-to-experience list.