Area Overview

In progress

Overview

Area refers to any lab, room, or other physical space assigned a database entry. Barring any human errors or oversights every single space owned by the University of Kentucky is recorded into a comprehensive and extremely granular set of tables located in the UKSpace database. Large open labs are actually groups of smaller spaces (usually tables) each considered its own space.

This database is maintained by the Physical Plant department and updated on a regular basis. Each space is assigned a unique bar-code. To help identify a space, stickers containing the bar-code in UPC and human readable format are placed somewhere within the space. Typically this sticker will be located in the door frame of a space's primary entrance, or in the case of open labs with individual table spaces, just under the surface of a lab table.

Areas are of maximum importance to the UK inspection process, and by extension Inspector Blair, albeit not required. Inspections are self identified - they may be created and updated without an attached area. Moreover multiple areas may be assigned per inspection (this functionality is currently hidden). Note however that omitting areas removes one of the essential points of an inspection and primary query attribute for reporting.

Because of their high importance and mercurial nature, areas present several challenges to overcome.

Self-Contained Vs. External

Although the UKSpace database is comprehensive, some of the information relevant to inspections is either omitted or inaccurate. Of particular concern are room usage and laboratory attributes. This would necessitate a repository of areas local to Inspector Blair. However, at UK, maintaining a fully self-contained space inventory within Inspector Blair is not considered a viable solution. The sheer scope of data involved is essentially a full time position on its own.

Importing a copy of the space database would solve the initial entry gateway, but would do nothing to ameliorate the need for constant updates. Intermittent import scripts have proven time and again to be unreliable without also requiring untenable levels of human intervention, and do not present a real time, living space inventory. In any case, given that a space database already exists and is actively maintained, replicating the same data locally would be adding pointless task redundancy.

Should Inspector Blair be adopted by entities without an existing space inventory, a self-contained space inventory obviously becomes essential. Even so, the typical solutions outlined above are still implausible. The space process must be fully integrated and automated at all possible points. The purpose of Inspector Blair is to enable a seamless, streamlined, inspection process - not create new administration positions.

Solution

The solution invoked by Inspector Blair is a multifaceted approach designed to accommodate any desired workflow structure. This layout is also designed to allow falling back into a fully self-contained solution should the application be adopted by entities without an existing space inventory.

A local table is combined with the external UKSpace at stored procedure level.

Use Cases

1. Inspection.
   1. When creating inspections, a bar-code field will accept direct input from scan or type.
   2. Should the use be unable to locate a scan enabled bar-code label, a search mechanism is available to allow selection from human readable drop lists. Because the area may not yet exist in local area table, the drop lists are sourced directly from UKSpace database.
   3. Once an area bar-code is entered, Inspector Blair will will query combined sources and display all pertinent information currently available.
2. Area List
   1. Areas in use uses a combination query to retrieve areas that already exist in the local area table, AND bar-codes that have been entered into inspections but do not yet have local areas entries.
   2. Areas that do not yet have a local entry are highlighted, and areas of both origins may be filtered at will.
3. Area Entry
   1. Because of the external area sourcing of bar-codes when entering inspections, it is not required for areas to exist in the local table before hand. This allows users to specify their own unique procedures.
      1. Enter area information locally before inspections are made.
      2. Enter area information after inspections are made.
      3. Enter all areas.
      4. Enter only known inspection areas.
      5. Omit area for one or more inspections.
      6. Any combination of procedures.

Structure

UK Space

Note UK Space is an external database and subject to structural changes. Access via local stored procedures provide an adaptable DAL should updates break compatibility.

buildings

rooms *-UnitID-RoomUsage-8 usage_codes

Local

tbl_master *-id_key-8 tbl_area

tbl_area *-id_key-8 tbl_area_biological_agent

tbl_area *-id_key-8 tbl_area_type

tbl_area *-id_key-8 tbl_area_usage