App Support: Data Flows

Introduction

The purpose of this chapter is to look at the bigger picture of how a database interacts with an application. How is data requested from the database? How is the data sent back? How do records get inserted and updated from an application interface? What is the role of the database in supporting the application?

The Application Framework

Applications are built for the desktop, phones, web sites, ATM's, kiosks, and many other environments. The "framework" for each of these is often different. The framework describes the enviroment (desktop, for example), the network (none, internal, Internet, intranet), as well as how data is shared and exchanged between components.

We will concentrate on one type of framework, a device and application able to communicate over the Internet. The device might be a phone, web browser or other device, such as an IOT (Internet of Things) temperature gauge. The network is the Internet.

Devices on the Internet communicate using protocols such as TCP/IP and HTTP (email), using various types of data formats. For devices such as web browsers and phones, web pages are sent in HTML format, which may also include JavaScript and style files written in CSS. These are all text-based files using specialized markup languages

The most common data formats are also text-based files, using the markup languages JSON and XML. Within the process of a web page being delivered (as HTML), data is retrieved from a database and converted to JSON or XML.

The Front End Connected to the Back End

An Internet device (whether the client is a browser, phone or something else) is only one component of an overall application. The user interface, such as a web or phone page, communicates with a web server. The web server is usually a third part of the process we have not discussed until now. This is referred to as "middleware". In other words, something in the middle of the process that accepts requests and does whatever is necessary to answer the request. The web server often hosts an application that delivers web pages. When it receives a request, it determines how to answer, and then sends an HTML file back to the requestor.

Where does the database fit in? Some applications are written so that the web application sends data requests to a database server and then incorporates the query results into the HTML file that is returned to the requestor. For example, take an example of a web page that lists the major cities for a country. The browser would request a web page such as http://sampleprojects.xyz/cities/CAN

The data flow for this process would be:

  1. Browser requests web page
  2. Middleware (server app) evaluates parameters ("CAN" country code)
  3. Middleware submits a query to database server for list of cities for country=CAN
  4. Database engine processes query and returns results to middleware app
  5. The middleware app integrates query results into HTML page: creating an HTML table,, for instance
  6. HTML file is returned to browser
  7. The browser displays the HTML file with help of JavaScript and CSS

This is common data flow, where the web server handles the database request.

Another Data Flow Path: Using an API

Another possibility for a data flow to support a web page is for the browser client to call an API after the HTML page has been returned from the web server. This framework relies on another type of middleware application that is an API (Application Programming Interface). This data flow is a little different:

  1. Browser requests web page
  2. Middleware (server app) returns an HTML file with the page layout and markup for data to be added later
  3. HTML file is returned to browser
  4. The bowser displays the HTML file with help of JavaScript and CSS
  5. Once the HTML is displayed
    • JavaScript methods are called to submit a query request to a web server running middleware as an API
    • For example, the request might be http://sampleprojects.xyz/API/cities/CAN
    • The API middleware evaluates parameters ("CAN" country code)
    • The API submits a query to database server for list of cities for country=CAN
    • The database engine processes the query and returns results to middleware app
    • The middleware converts the results to JSON or XML and returns them to the browser as a JSON or XML document
    • The browser reads the JSON/XML document and "binds" the data to the existing HTML code
    • The updated HTML data sections are re-displayed in the browser

Database Support for an Application

One or more databases may support an application in many different ways. For example:

  • Security and permissions
  • Web page and formatting content, including menus, lists, titles, images, audio, ads, colors, etc.
  • Web data content: articles, lists, reports, graphs, images, ads, etc.
  • Maintaining application information: Creating, Reading, Updating and Deleting (CRUD) data
  • Storage for uploads, downloads, importing, exporting, etc.

CRUD

CRUD refers to operations that are needed for many applications to Create, Read, Update and Delete information. From a database perspective, this mean INSERT, SELECT, UPDATE and DELETE operations. For example, a simple application for an Volunteer app might need to do the following:

  • Maintain organizations wanting volunteers, including all CRUD operations on a organizations table
  • Maintain a list of people applying to be volunteers, including at least CRUD operations to add new records to a volunteers table
  • Maintain supporting tables, such as volunteer job types, including CRUD operations on a jobtypes table

As with queries to retrieve data, there are multiple ways to structure how CRUD operations are performed in a web application. Two of the most common ways are:

    1. Present the user with an HTML form, which is then sent to the web server for processing when it is submitted, and 2. Package HTML form data as JSON or XML and submit it to an API for processing

In both cases, the middleware submits INSERT and UPDATE statements, OR calls stored procedures (an SQL mini-program) to make changes to the appropriate tables.

As has been discussed before, there must be strong coordination between development teams working on the front end, middleware and back end components of an application to make sure things work smoothly. The front-end team must know how fields (columns) will be named, so they can properly reference the data in HTML. The back-end team must understand what is expected by the middleware and front-end teams, so that the data can be structured and delivered properly. And the middleware team must know how to convert and translate data between the database, JXON, XML and JSON, in order to properly support all types of CRUD operations.