ATI Database Planning & Design - User Perspective

 ATI Database Planning & Design - User Perspective


Note: This tutorial is a highly condensed version of multiple tutorials
written for non technical users who are stakeholders of an information 
system that is being developed to help them provide their contribution
and input at the conceptual design phase in order to ensure
that the application developers have all the critical information
to create an efficient and effective web portal for content management.


This website/blog tutorial has been written to help users who do not have a lot of ICT technical experience, but are familiar with computers and use them to complete the day to day work. In addition this tutorial intends to provide users with the concepts involved with the creation of an automated information management system such as a records information management system or knowledge portal via the Internet.



As the schematic shows the Application Developer needs input from the client. The better feedback and information the developer gets the better will be the final product. From the client side input has to be provided by all members of the organization that uses or has impact on the application. This includes executives, user staff, and technical support teams that help in the operations and maintenance of the application. The larger and more complex the system, the larger and more diverse should the client team be. It is important that everyone is cognizant of the goals and objects of the application and provides their input as needed.

There has never been failure because of too much input at this stage of the application development, but there has been numerous and costly failures because of the lack of user input.

How Information is Shared

Until the invention of the printing press in the 1400s information was predominantly shared orally by our ancestors. The printing press started the mass dissemination of information  through books and later on newspapers. These books and papers could be shipped globally but its distribution took a lot of time. 

Around the 1700s the invention of the steam engine started the industrial revolution allowing products to be massed produced cheaply, in addition it allowed for the invention of steamships and the creation of rail networks, which help the distribution of goods more efficiently. 

Around 1830 the invention of the telegraph allowed news to be instantly transmitted around the world. In 1876 Alexander Graham Bell patented the telephone. This allowed mass audio communication between everyone within their phone network.

How Office Work is Processed

Until the typewriter was invented and used more commonly around the 1880s, clerks meticulously hand copied documents and reports for distribution and filing. The typewriter speeded up the office process significantly. 

However this process did not change until the personal computer was invented and became popular in the 1980s. Automated word processing machines were the first office computers that companies purchased and used (Wang, 1972). This invention allowed secretaries to efficiently create, store, edit and replicate and print large and complicated documents.

In 1986 the IBM PC was launched. This drastically changed the way offices operated. Now it was feasible to have a personal computer on every desk. The Ethernet 10Base-T local area networking system was also invented around the same time. This allowed offices to connect their desktop computers on a network and share data on a peer-to-peer basis until a server is added.

This means that by the 1990s many businesses and organizations have replaced hard copy paper files with digital ones. In Alberta around the 1990s the provincial government was able to replace the storage of hard copy documents in warehouses to digital ones on networked servers, which made it much easier to search and retrieve archived material.

Internet and the World Wide Web (www) 

The evolution of ARPANET to Internet allowed the world community to connect  through the World Wide Web. This allowed the world community to completely change the way we create, use and share information. 

The origins of the Internet date back to the development of packet switching and research commissioned by the United States Department of Defense in the 1960s to enable time-sharing of computers.[2] The primary precursor network, the ARPANET, initially served as a backbone for interconnection of regional academic and military networks in the 1970s. The funding of the National Science Foundation Network as a new backbone in the 1980s, as well as private funding for other commercial extensions, led to worldwide participation in the development of new networking technologies, and the merger of many networks.[3] The linking of commercial networks and enterprises by the early 1990s marked the beginning of the transition to the modern Internet,[4] and generated a sustained exponential growth as generations of institutional, personal, and mobile computers were connected to the network. Although the Internet was widely used by academia in the 1980s, commercialization incorporated its services and technologies into virtually every aspect of modern life. (Wikipedia)

How does the Evolution of Technology affect Information

Over the last 30 years information in the private and public sector has evolved from hard copy files to digital ones. From banker boxes of paper files in the store room to hard disk drives of digital information on desktops and servers. 


At a conference in California in 2010, Eric Schmidt, past Chairman of Google Alphabet told the audience that every two days something like five exabytes of data (1018), which is as much information as we have from the dawn of civilization up until 2003. 

Organizations that are still working with hard copy files will have a greater challenge to catch up with the global community if they do not make the effort to join the digital community.

Information Data Storage & Retrieval

Before 1990 if you wanted to access archived information from the Alberta government you would have needed to process your request through the relevant ministry or agency which would then need to locate the hard copy information, which is normally stored in a warehouse off-site. If it is a report it would have to be replicated as a paper copy. 

Today older paper files will still have to be located at the warehouse, but you will have the option of getting it scanned into a digital PDF file and have it emailed to you. New archival information is generally stored digitally and can be retrieved by downloading from a server. The process is much more efficient and can normally be completed online.

In the beginning of the digital revolution there was limited digital storage options. Floppy drives had limited capacity and were cumbersome to handle. The early hard disk drives (HDD) were large devices with limited storage. My first HDD had only 10 Megabytes of storage. Today my USB drive has 16 plus Gigabytes of storage capacity. 

As the cost of HDDs were reduced as capacity increased it became possible for organizations to store their entire information system on their HDDs in-house. This was made easier with networking and the installation of dedicated servers which allowed office staff to access and share information on a central server. However an in-house system required highly trained ICT staff to operate and maintain the server system.

The popularity and expansion of the WWW with increasing bandwidth through fibre optic networks allowed users of the internet to share digital data globally and efficiently. This meant that there was an opportunity for larger advanced technical private sector companies to establish large server farms as "cloud" storage for users around the world. Google Drive and Dropbox are good examples of cloud storage on the internet.

In countries where the internet access is cheap and abundant the use of thin client access to servers in the cloud has become a normal activity and users have become reliant on this automated process. Unfortunately this becomes a problem when the user travels to a country that has limited internet connectivity and the user finds that the data he/she needs is no longer readily available.

Thin Clients, Servers and Websites

In the beginning most automated systems were server based connected through thick clients. Thick clients are applications that run on a user's desktop that accesses a database on a server and transactions are processed. This has been superseded by thin client applications running through browsers using internet protocols. Essentially they are web based applications running off a server probably in the cloud via internet.

Today most database transactions are web based thin client applications. For example when you check your bank account balance or use a web service to buy a product online, these are web based thin clients. It is pretty common today for a website type interface to be the front end to a complex database system, such as a local government tax collection and payment system, or an inquiry into access to information.

Automated Information Management Example

As an example you could use the open source Wordpress framework as the basis of your information management. Wordpress is a powerful content management system (CMS) framework that uses plug in architecture and template system. It allows users to build  anything from simple websites to complex content management systems and data portals. The ATI Information Management system could be built on top of Wordpress.


A simple schematic representation of a thin client access via the Internet with Wordpress

A more detailed schematic of the CMS capability of Wordpress

How do I migrate from a Hard Copy to Soft Copy

Convert Paper Forms to Digital Records

If you are using paper applications forms you will need to convert them to digital fields and records so that they can be added to a computerized database.


Computerized database management systems started in the 1980s, about the same time as the personal computer. It was the personal computer (PC) that allowed for the development of a flat file database. Visicalc was the first spreadsheet application that allowed users to create and manipulate flat files on a PC.

A single sheet on your spreadsheet file is an example of a flat file. A flat file database stores digital data on the cells of the sheet. Each column on the sheet can be viewed as a field in a database. Each field is assigned a designated piece of information such as a person's name could be assigned field 1, mobile phone number as field 2, email as field 3, etc. This would be similar to the contact information you would store in your mobile phone contacts. Each following row down is a new record of the next contact.


The above example is a flat file spreadsheet contact database.

ATI Request for Information Application form

The first step in the process to create an automated ATI system is to convert the paper forms from hard copy to digital ones. Let's start with the first form Form 1 from the Schedule.
 




Exercise 1: Take Form 1 and create a spreadsheet flat file table with the input information.

  1. Create a blank Excel spreadsheet and save it as ATI_Form_1.xlsx.
  2. Starting with the first item: Title of Public Authority, enter that in Row 1, Column A
  3. Enter Name of Applicant in Row 2, Column B. Note there are three entries you may consider using three fields. Discuss and decide.
  4. Next is the Address. Note there are two options one Mailing (home), two Business. Each has three lines for address and each has telephone and fax and there is one email line and an other line. A total of 12 columns possibly.
  5. Description of Document includes three fields. Name/Type of Document, Reference/File No and Other.
  6. I would like to includes four options for selection. Inspect, listen to, view the document and have a copy made available as a photocopy, compact disc, diskette, transcript, other, and number of copies.
  7. Signature of applicant
  8. Date of application
Review output and discussion.

Note: In addition to the fields from the hard copy form you will need to add a field for the applicant to upload additional files which could be PDF or audio files. You will need to consider an audio version of the application form to accomodate the visually impaired, etc.

Database Field Naming Conventions

In a spreadsheet it is acceptable to have multi word descriptive field names as the column heading. In a database that convention is not acceptable because the field names are used multiple times in the database programming functions (like in SQL statements). It would be very easy to make a typo mistake that would affect the resultant procedure process.

A field name in a database would generally not have spaces, be an abbreviation or acronym. If multi words need to be used then they are separated by an underscore or follow the camel case convention.

Database Data Types

Users of spreadsheets also do not normally have to be too careful about data types in a spreadsheet. The main thing is to make sure to differentiate between numbers and strings. In a spreadsheet a column of numbers could be entered as string values. You will know this when you try to add or sum the column. You could readily fix the problem in the sheet. It is much harder if you mix field types in a database. 

Data types need to be precise in a database otherwise big problems could arise when you apply procedures to it. A database procedure is like a programming function in a database system which operates on the data in it to generate an output result. Procedures could be written in SQL (structured query language) statements. 

Exercise 1: Take Form 1 fields you have listed and decide on what field types you would assign to each field. You can add the field type to Row 2. Row 3 on will be the actual records to be entered.

    To keep things simple let's use a limited field set:
    1. ID
    2. Integer
    3. Float
    4. String (text)
    5. Date
    6. Blob (includes PDF attachment)
    Review output and discussion.

    This should provide a basic start in understanding the conceptual requirements that a user should understand in the design of an automated database system.

    The same process above should be applied to all the paper forms used under the ATI Act. Once all the forms have completed the preliminary translation to digital database tables they will need to be reviewed and refined with the ICT technical members to ensure they conform to the standards required in a relational database. The individual tables need to be normalized and fields exchanged between the tables for efficiency of operation. Finally the database relationships need to be established between the normalized tables.

    Process Flow for an ATI Application

    1. Applicant submits a digital application form
    2. ATI portal assigns a date and reference number and forwards to Responsible Authority
    3. Responsible Officer submits confirmation to Applicant
    4. Undertakes library/archive search for information
    5. Finds information
    6. Notifies Applicant search completed and information found.
    7. Notifies Applicant of duplication costs and seeks acceptance of payment requirement. Can be combined with item 6.
    8. File closed.
    The example of a rough brainstorm sketch of the workflow process of submitting an application could be refined using Microsoft Word or LibreOffice Draw. A sample of that is shown below:


    Under the Guidelines on the Discharge of Functions by Public Authorities Under the Access to Information Act 2002 there are 11 processes defined starting on page 21 of the scanned document and 3 section on notes to be reviewed and mapped for the design of the ATI Information Portal. 




    Note: the mapping of the application processes are critical to the ATI Information Portal conceptual design because it will help the website developers to develop the proper database model back end that can implement the process with procedures that work efficiently. If this is not done then as the system is used changes will be needed to be made to both the procedures and the database and patches will have to be added making the system cumbersome and difficult to update in the future.








    Comments

    Post a Comment