Johnson
Army Human Resources Customer Interactive Database
Adam Johnson
Table of Contents
Project Description Pg 2
Project Overview Pg 2
System Methodology/Database Design Life Cycle Pg 4
Data Model Pg 6
Entity Relationship Diagram Pg 6
Entity Descriptions Pg 7
Data Flow Diagram Pg 10
Data Flow Diagram Description Pg 11
Data Dictionary Pg 12
Prototype Pg 13
Customer Interface Pg 13
Employee Interface Pg 21
Database Administration Pg 25
Future of the Database Pg 26
Project Description
Project Overview
Historical Perspective and Business Problem
The United States Army was founded on June 14, 1775, and currently employs over 1,200,000 people, including servicemembers and civilian employees. This makes it one of the largest employers in the world. The Adjutant General Branch, effectively the human resources department of the Army, was founded on June 16, 1775, and is the second oldest branch in the Army after the Infantry. This shows the historical importance that human resources management has carried in the Army. To this day, human resources is a vital function of this large organization and plays an important role which usually involves weekly or bi-weekly interaction with each individual employee.
The Army Human Resources enterprise as a whole has adopted standard forms which standardize service and information requirements needed in order to process the large variety of human resources transactions that an Army HR employee may execute. At the center of this HR enterprise is the Battalion S-1. The Battalion S1 is the term for the HR office that services the lowest echelon of Army unit containing its own internal staff, the Battalion.These HR offices are typically manned by 2-10 soldiers, with the most experienced soldiers normally having about 10 years of service, and the least experienced having little-to-no experience. These offices serve anywhere from 200-1,000 individuals, providing services which include evaluation management, employee awards, mail, total compensation, onboarding and offboarding, legal services, personal information management, soldier accountability, and other miscellaneous services which vary from unit to unit. Further complicating this situation is the non-HR requirement levied on the HR soldiers who support these units; soldiers are soldiers first and HR specialists second, meaning that events like rifle ranges, deployments, and other soldier-general events take precedence over the execution of HR tasks. This takes the HR to employee ratio, which normally hovers between 0.75 and 2 (below industry standard) and forces it to almost 0 depending on the frequency of these events.
One would assume that Army HR must operate on the highest levels of efficiency, given that it is generally understaffed, overworked, and has an outsized effect on soldiers’ livelihoods. Yet, it is not; most HR offices limp along with outdated Army human resources management systems that do not share data amongst themselves. Some standardized forms are still filled out by hand, by HR specialists with little training, and little time for additional training. In the past five years, Army HR has increased its use of technology to improve strategic level HR decisions, such as recruiting goals and manning strategies for Divisions (two organizational echelons above the Battalion), but there still remains a lack of technological innovation at the Battalion level, where the real action of Army HR occurs.
Proposed Solution
To assuage this issue, this project proposes the creation of an Army HR Customer
Interactive Database. The goal for this project is to create a database which houses soldier
information, standard forms, and transactional data. It would (1) allow a soldier to interact with
the database in order to determine what standard form would be required for their request, (2)
autofill this standard form using the soldier’s stored information, and (3) route this form through
its approval chain, while maintaining a transaction ID, allowing the soldier to track the progress
of their request. The intent would be to distribute this database across the Army HR enterprise,
with the Battalion S-1 (HR office) as its primary target. Ideally an HR office who implements this
database system would see the following benefits: (1) standard forms would be filled out in a
standard manner, (2) HR specialists would have more time to devote to one-off or unique
requests, as opposed to assisting soldiers in filling out standard forms for basic requests, and
(3) HR offices would have the ability to conduct audits, using the transaction ID, of various
transactions to ensure completion and accuracy.
Users
The users of this database would fall into two categories: (1) application users, and (2) database users. Application users are those users who would only interact with the application side of the database, and would not interact with any sql commands or database structure. Application users are then further broken up into three groups: (1) HR specialists, (2) soldier customers, and (3) a soldier’s commander. HR specialists would interact with the system when assisting soldiers in completing transactions, explaining data fields, pulling completed forms out of the system, or marking a transaction as complete. Soldier customers would interact with the application in order to open transactions on their own behalf, or to inquire about the status of a previously started transaction. A soldier’s commander would interact with the application in order to inquire about the status of a transaction, or to begin a transaction on someone’s behalf. Database users, on the other hand, would interact with the SQL database in order to update table structure as needed, or to load data. This level of access would be limited to one or two people in the Battalion S1, likely being the S1 Officer, who would have more technical training.
Hardware and Software Requirements
Ideally, this application would operate on a standalone computer or tablet located in the customer service area of a Battalion S1 office (normally towards the front). This would allow the soldier customers to interact with the application without requiring interaction with HR specialists, allowing these specialists to continue their tasks at hand. Given the Army’s computer procurement process, which involves (generally) yearly purchasing of new computer systems, there tend to be some computers that are three to four cycles out of their purchasing cycle (about three to four years old) that are not off-network, but are also not in use because of their limited capacity, storage space, or some other issue. These computers could be used to host the application, given that its storage space would mostly exist in network storage, and the database structure itself would not be too large to store on these older computers.
On the software side, the Army tends to prefer the Microsoft Access database for any user generated databases. Some Army HR systems operate off of Oracle database structure, however they do not always use SQL, and are always locked systems, meaning that the only users granted access to the backend operating side of the database are Oracle software engineers. All Army end users, including those working in the Battalion S1, would be prevented from having this backend access. Even though this current project exists in MySQL, when it is eventually ready for roll-out to Army customers, the database will best work on Access. All Army computer systems have Access pre-loaded, which would allow for easy sharing of the application and its data between systems. The goal then would be to store this database on some sort of shared network. The Army uses both a network drive space and Sharepoint to share files between computer systems. The preference for shared network storage will depend on the echelons at which this system is used. If the system is solely to be used at a Battalion level (where 75% of HR transactions start and end), the shared network space would be the preferred storage method. If the system is used at echelons above Battalion (such as Brigade [one echelon above], Division [two echelons above], or Corps [three echelons above]), the application should be stored on a Sharepoint site, to allow shared use across units that may not share the same network drive space.
System Methodology/Database Design Life Cycle
Preliminary Planning/Feasibility Study
For this stage of the project, I sat down with the HR specialists in my Battalion S1 to understand the problems they encountered on a daily basis, how long they spent serving each customer, and how they thought they would be able to improve the HR processes we used. Through this informal survey, I learned of the inordinate amount of time that my HR specialists were spending helping customers fill out forms that they themselves could research and fill out, or in fixing simple problems with forms that required over-writing or scanning and editing using an Adobe Pro type application. I also learned how little time my HR specialists had to talk to outside agencies, especially our Fort Bragg finance office, about individual cases and soldier pay issues; meaning that my HR team was spending more time fixing minor formatting issues, and less time engaging with the local offices that could fix complex soldier pay and HR issues.
For the feasibility study, I talked with my Battalion automations section (essentially our IT department). They informed me of the capacities we had to store and share data, both through network space and through Sharepoint, and of the number of available, not-in-use computers we had. I also spoke with our Brigade [one echelon above Battalion] S1, and specifically to the HR technician there. Army HR technicians are the people responsible for running and maintaining the various Army HR systems we use. They monitor access, report system issues to contracted software engineers, and ensure data quality. From this conversation, I gleaned the information about the Army’s use of SQL and non-SQL databases, and how our Army HR systems were closed to end users, to include the technicians.
Requirements Definition
After gathering information about the problem statement, and the feasibility of fixing this problem, I sat back down with my Battalion S1 team, and the Brigade HR technician. Together, we determined what capabilities we would like the system to have, given our own technical abilities and the software we had. This also led me to design the initial database around one form: the DA 5960. It would not have been feasible to design a prototype database that would have been able to accomplish or accommodate all the possible Army HR actions; there are at least 100 actions, which are completed on over 50 different Department of the Army (DA) and Department of Defense (DD) forms. Instead, I decided to focus my efforts on one form, the DA 5960, and its requisite actions: to start, change, or stop Basic Allowance for Housing (BAH). BAH is the entitlement paid to all eligible soldiers to maintain a household. Eligible soldiers are those with a dependent (either child or adult), or who are in the rank of Staff Sergeant or above. All non-eligible soldiers live in the barracks, and thus do not need a stipend to pay rent or a mortgage. The DA 5960 whereupon soldiers can start, stop, or change their current BAH is the perfect form for this database prototype for a couple of reasons. First, it is fairly standardized; there is no portion for remarks, meaning that there is little variance in two completed and accepted DA 5960s. Second, it is fairly complex, with multiple data fields not just about the soldier themselves, but also about their dependents. Lastly, it takes up a disproportionate amount of my Battalion S1’s customer service time. While BAH start/stop/change requests make up less than 5% of our requested transactions, the filling out and processing of the form takes up at least 10% of my HR specialists’ available customer service time. Thus, I wanted to design a database prototype that would gather and display the necessary data to complete one of these forms.
Conceptual Design
Now that I knew the form I wanted to design my prototype around, I began the design process. I pulled all of the data fields out of the DA5960, and added in additional data fields that would be required in order to execute transaction search functions. I then placed these into normalized entities in an Entity Relationship Diagram which will be shown in the next section. Concurrently, I sat down once again with my Battalion S1 team to determine how we wanted data, reports, and forms to flow in and out of this application, and back and forth between user groups. I then compiled this and transferred it into a Data Flow Diagram, which is also discussed in the next section.
Physical Design
During the physical design portion, I selected MySQL as my database management system (this was done for personal learning purposes, the database will be transferred to Access for finalization of the project). I then converted my entities into tables, and loaded them into MySQL. I then generated sample data (the actual data this database will use is protected/confidential information), and loaded that into the tables. Finally, I tested the sample data and the table structure by executing multiple queries to ensure anomalies and errors were minimized. This was the last phase of the DDLC I accomplished. Ultimately, this database will be moved over to an Access database that can accommodate an application interface that will support the forms and reports mentioned in later sections. Once in the proper format, the database will have the actual soldier data loaded into it, and it will be secured. Once that stage is complete, I will test it using my Battalion S1, before fielding it with other Battalion S1s, and eventually (hopefully), a larger crowd within the US Army.
Data Model
Entity Relationship Diagram
The entity relationship diagram for this project is shown below.
Entity Descriptions
Soldier Describing Entities
SOLDIER
This reflects individual soldier’s information, and includes their Social Security Number (primary key), their Department of Defense ID number (alternate key), their first, last, and middle names, their grade (which is a foreign key of GRADE), their Military Occupational Specialty code (which is a foreign key of OCCUPATION), their marital status, their number of dependents, and the Unit Identification Code of the company they belong to (which is a foreign key of COMPANY). Given that this database is designed to simplify the creation of financial and HR forms, the SOLDIER entity is the main entity, through which all other entities are either directly or indirectly related.
COMPANY
COMPANY stores information on Army companies, which are the smallest units of organization which have an assigned Unit Identification Code. Company commanders are responsible for certifying soldier information and requests for various HR or financial processes. A soldier has one and only one company, and all companies have commanders. The COMPANY entity contains the following fields: Unit Identification Code (primary key), the Commander’s first, last, and middle name (alternate keys 1.1, 1.2, and 1.3), the Commander’s rank, and the Company name (the standard name which it is known by, i.e., Alpha Company). Companies can have many soldiers, soldiers can only belong to one company.
GRADE
The GRADE entity contains information about ranks and grades. Army forms flip back and forth between grade, expressed as E-, W-, or O-, which reflect enlisted, warrant officer, and officer ranks (i.e. O-3 is an Army Captain), and rank, which is written as a three letter all capital abbreviation (i.e. CPT is an Army Captain). The grade is stored in SOLDIER as it is alphanumeric and shorter than rank, but the GRADE entity allows ranks to be associated to a soldier’s record using Grade as a foreign key in SOLDIER.
OCCUPATION
The OCCUPATION entity contains information about Army occupations. All soldiers have one military occupation specialty, which is expressed as a three letter code, two numbers, and one letter. For instance, my MOS is 42B, which is the MOS for a human resources officer. Each code uniquely identifies one occupation. 42B, for instance, only identifies human resources officers. The entity contains the following fields: military occupational specialty (primary key), the career field (for example, the career field for 42B is Adjutant General’s Corp; this is sometimes known as branch, and some Army forms ask for this as opposed to MOS), and the long description of the occupation (i.e. human resources officer for 42B). A soldier must have an occupational specialty.
Family Describing Entities:
SPOUSE
The SPOUSE entity contains information about a soldier’s spouse, if they have one. Spousal information is necessary because some forms require spousal information in order to be processed, especially financial requests. A soldier can only have one spouse at a time, and a spouse is only recorded when in relation to a soldier. If a soldier gets divorced or separated, their spouse is moved to the FORMERSPOUSE entity. This entity has the following fields: the spouse’s social security number (primary key), the associated soldier’s social security number (foreign key of SOLDIER), spouse’s duty station (this is specifically for soldiers who are married to another soldier, and is a required field for some financial forms), address, and the spouse’s first, last, and middle name.
MARRIAGE
This is an association entity between SOLDIER and SPOUSE. It records necessary information, date of marriage, between a soldier and their associated spouse, if they have one. The date of marriage is generally used as an effective date for some financial entitlements which soldiers and their families are entitled to if they are married. The fields for this entity are the soldier’s SSN (first part of primary key), the spouse’s SSN (second part of the primary key), and the date of marriage.
FORMERSPOUSE
This entity acts much like the SPOUSE entity. Former spouse information is required because certain financial forms require former spouse information in order to calculate benefits, or the end date to benefits. For instance, a soldier who gains housing benefits from having a spouse, but then is divorced, will have their housing benefits terminated if they are eligible to stay in the barracks. This entity is derived from the SPOUSE entity if a soldier becomes divorced, or is populated if the soldier is already in this state. A soldier cannot have both a spouse and former spouse recorded, so whichever is the most recent is the kept record. This entity has the following fields: former spouse SSN (key), associated soldier SSN (foreign key to SOLDIER), former spouse duty station (for soldiers married to other soldiers), address, and former spouse first, last, and middle names.
DIVORCESEPARATION
This entity acts much like the MARRIAGE entity. It records necessary association information between SOLDIER and FORMERSPOUSE. It contains the soldier’s SSN (first part of primary key), the former spouse’s SSN (second part of primary key), whether or not the relationship is divorce or separation (these are the only two choices), and then the date of divorce or separation. The date of divorce or separation, much like the date of marriage, is a critical piece of information for some financial forms the Army uses.
DEPENDENT
The DEPENDENT entity reflects information about a soldier’s dependents. A soldier can have multiple dependents, but a dependent must be associated with a soldier. This entity contains the following fields: dependent ID (this is the primary key and is a surrogate key; the Army generally avoids maintaining records of children’s SSN outside of required medical and legal databases, hence why this entity uses a surrogate key); the soldier’s SSN, which is a foreign key; the dependent’s first, middle, and last names; the dependent’s address; and their date of birth. Some soldiers are unmarried, and would thus not qualify for housing allowance, except for the fact that they have a dependent child. This entity allows soldiers to maintain relationships with their dependents in the database in order to establish their rights to certain financial entitlements.
Transactional Entities
ACTION
This entity stores relationships about the various actions which can be processed by an Army HR office. Examples of actions are start basic allowance for housing, stop basic allowance for housing, and request reassignment. Each action is assigned an action ID, which is the primary key, and is a surrogate key. Each action has an associated action name, like those mentioned above. Each action then has an associated form. While an HR action can have multiple pieces of supporting documents, such as marriage certificates, birth certificates, and other such paperwork, each HR action is requested and completed on a single form. This entity interacts with the TRANSACTION entity, wherein each action can be associated to multiple transactions (multiple soldiers can request reassignment), but each transaction is associated to an individual action (for instance, if a soldier wanted to request reassignment and start their basic allowance for housing, they would execute two transactions).
TRANSACTION
This entity is an association table between SOLDIER and ACTION. While each soldier can have multiple transactions, and each action can have multiple transactions, each transaction must be associated only with one soldier (the requestor) and one action (the requested action). This entity contains the following fields: the transaction ID (the primary key, and a surrogate key), the action ID of the requested action (foreign key to ACTION), the requesting soldier’s SSN (foreign key to SOLDIER), the date the transaction was started, the completion status (which is restricted to incomplete, complete, or canceled), and the completion date (a calculated field which records the date the transaction’s status was changed to complete). The TRANSACTION entity is the lifeblood of this HR system; it allows individual soldiers to follow up on the statuses of their requested actions, it allows commanders to check in on what actions their soldiers are requesting, and how quickly they are being completed, and it allows HR specialists to audit their own processes, determine their efficiency, and prioritize actions.
Data Flow Diagram
The data flow diagram for this project is shown below.
Data Flow Diagram Description
Entities/Individuals
Customer Interface
This is the customer facing interface that allows soldiers and their commanders to interact with the database. Through this customer interface soldiers can submit transaction requests, request status updates on their transactions, and commanders can request individual or bulk status updates.
Customer Soldier
The customer soldier represents any soldier who requests an action through the customer interface. The customer soldier requests an action through a transaction request, and receives confirmation of this transaction through a transaction receipt, and/or the prepared form that the database creates. The customer soldier can also re-interact with the database, requesting a status update on their submitted transactions through a status update request. The customer soldier also interacts with their commander by providing forms for signature (outside of the database) and providing completed and signed forms to the HR soldiers for processing (also outside of the database).
HR Soldier
The HR soldier interacts with the database through the employee interface. There, the HR soldier can input transaction status updates which updates the individual transaction in the database. These status updates are pushed by the HR soldier’s interaction with the customer soldier when they receive completed forms (and thus a transaction is ready for completion), though this interaction happens outside the database.
HR Officer/Data Manager
The HR Officer/Data Manager is the only individual with write access on the database. This helps to control the database’s security, and ensures that no unwanted over-writes or deletions occur. The HR Officer/Data Manager can also interact with the employee interface, updating and changing soldier data through a password-enabled portion of the employee interface.
Interfaces/Applications
Customer Interface
The customer interface is an application that allows customer soldiers or commanders to directly interact with the database. Through this interface, soldiers can request a variety of transactions. These transactions are receipted using either a system generated receipt, or a form which requires further outside-the-database action by the soldier. The interface also allows soldiers and their commanders to query transaction statuses, providing valuable information and transparency in the Army HR process.
Employee Interface
The employee interface is the backend, password-protected interface which allows HR soldiers and data managers to interact with the database without directly SQL coding into it. Through this interface HR soldiers can update transactions statuses to reflect current information. This transaction occurs using a form which contains information regarding the transaction which needs to be updated.
Data Dictionary
The data dictionary for this project is shown below.
Prototype
Customer Interface
This is the first page customers arrive at when using the interface. They can either start a transaction, or request a status on a transaction.
Start Transaction Pages
SSN/DODID Input Page
If the customer chooses to start a transaction, they are taken to this page. On this page, they input their SSN/DOD, which is then used in subsequent WHERE clauses to populate their information. If a customer clicks the go back button, they are taken back to the Customer Interface Splash Page.
Action Selection Page
Once the customer has input their SSN/DODID, they can choose which action they would like to begin. For now, the system will only be able to produce transactions related to basic allowance for housing. Once the system is fully operational, a customer would be able to select from a variety of actions related to various HR actions they would be able to complete with a HR service representative. If a customer clicks the go back button, they are taken back to the Customer Interface Splash Page.
Housing Allowance Splash Page
This database will be using housing allowance requests as the sample transaction. Once a customer has selected ‘update/change my basic allowance for housing’ they are directed to this page. On this page, they can choose whether they need to start, change, or stop basic allowance for housing. If a customer clicks the go back button, they are taken back to the Action Selection Page.
Housing Allowance Input Form
Once a customer has chosen whether they need to start, change, or stop basic allowance for housing, they are taken to this page. The title of this page will change with what specific subaction they have chosen (start, change, stop). On this page, the customer will verify and correct any information displayed. This information is queried using the following SQL query:
SELECT SoldierFirst, SoldierMiddle, SoldierLast, SSN, SOLDIER.Grade, DutyLocation,
MaritalStatus, NumOfDependents
FROM SOLDIER JOIN COMPANY
ON SOLDIER.UIC = COMPANY.UIC
WHERE SOLDIER.SSN = 'provided value';
The where clause is passed the Soldier’s SSN from the system to filter to the specific soldier’s information. If NumOfDependents returns greater than 1, than the radial button is automatically selected as yes. If a customer changes their information, it is not changed in the database, and only overwrites the information on the final form. If a customer clicks the go back button, they are taken back to the Action Selection Page.
Housing Allowance Dependent Form
If a customer reports that they are married, divorced, or separated, and/or chooses dependents ‘yes’ on the Housing Allowance Input Page, they will be shown this page prior to being shown their completed form. Here, the customer verifies information regarding their spouse (first table) dependents (second table) and former spouse (third table). Non-applicable tables are displayed as N/A. If a customer changes their information, it is not changed in the database, and only overwrites the information on the final form. If a customer clicks the go back button, they are taken back to the Housing Allowance Input Page.
Department of the Army Form 5960
Once the customer has verified all of their information, the database produces a pre-filled DA Form 5960, Authorization to Start, Stop, or Change Basic Allowance For Quarters And/Or Variable Housing Allowance. The customer is able to print this form for verification and signature by their commander. Form fields 1-7, 8a-e, 8(1)-(6), and 10 are automatically filled in by the database inputs completed in the system. All other fields are not required, or can be completed on a printed copy. The DA 5960 is shown on the next page.
Request Status Pages
Request Status Splash Page/Form
If a customer chooses ‘request status’ on the Customer Interface Splash Page, they are directed to this page. Here, an individual customer or the commander of a unit can request status on transactions related to an individual, group of individuals, or a company (searched by the unit identification code). The customer can choose to display complete, incomplete, canceled, or all requests. If a customer clicks the go back button, they are taken back to the Customer Interface Splash Page.
Request Status Report
This report is generated by the database off of the form completed in the Request Status Splash Page, using the following SQL query:
For selecting based on SSN:
SELECT TransactionID, ActionName, SoldierFirst, SoldierMiddle, SoldierLast, TransactionDate, CompletionStatus, CompletionDate
FROM TRANSACTION
JOIN ACTION
ON TRANSACTION.ActionID = ACTION.ActionID
JOIN SOLDIER
ON TRANSACTION.SoldierSSN = SOLDIER.SSN
WHERE SoldierSSN = ‘provided value’;
For selecting based on UIC:
SELECT TransactionID, ActionName, SoldierFirst, SoldierMiddle, SoldierLast, TransactionDate, CompletionStatus, CompletionDate
FROM TRANSACTION
JOIN ACTION
ON TRANSACTION.ActionID = ACTION.ActionID
JOIN SOLDIER
ON TRANSACTION.SoldierSSN = SOLDIER.SSN
WHERE SoldierSSN IN (
SELECT SSN
FROM SOLDIER
WHERE UIC = 'WAZFT0');
This report will show each individual listed in the SSN/DODID input box, or each individual with a transaction in the system who is listed as belonging to the company specified. The report shows basic information about the status of transactions, including their ID number, the associated action, the name of the requestor, the date the transaction began, the completion status, and the completion date. The report will be filtered by completion status based on the inputs from the previous page’s form, completion status will be filtered using an AND clause after the WHERE clause in the above SQL queries.
Employee Interface
This is the splash page for the employee interface. Here, employees can choose to request a status on a transaction, or update a completion status. Because the request status process is the same between both customer and employee interfaces, this section will only walk through the update completion status transaction process.
Completion Status Update Splash Page
If an employee chooses to update a completion status, they are taken to this page. Here, an employee can search for the transaction they wish to update using either the transaction ID, the SSN/DODID of the particular soldier, or by filtering using the Unit Identification Code.
Completion Status Report
The completion status report looks the exact same as the transaction request report, and the SQL queries used to arrive here are the same. The SQL query to find specific transactions is listed below:
SELECT TransactionID, ActionName, SoldierFirst, SoldierMiddle, SoldierLast, TransactionDate, CompletionStatus, CompletionDate
FROM TRANSACTION
WHERE TransactionID = ‘provided value’;
The only difference with this view is that each transaction is a linked value, and if the employee clicks on the transaction ID, they are taken to the update completion status page.
Update Completion Status Page
Once an employee chooses a transaction, they are directed to this page. Here, they can select the new status of the transaction (only the two non-used statuses of transactions are shown), and the effective date of this change (default date is the current system date). If the employee selects back, they are taken back to the completion status report page. The selection made in this window overwrites data in the actual customer database. This is done through the following SQL code:
UPDATE TRANSACTION
SET CompletionStatus = ‘provided value’, CompletionDate = ‘provided value’
WHERE TransactionID = ‘provided value’;
Completion Status Update Confirmation Page
Once the employee selects update on the completion status update page, they are directed to this confirmation page. From this page, the employee can return to the employee interface splash page.
Database Administration
Security
Because this database should live in Access, and most likely in a shared network drive within the network architecture of an Army unit, this database will be access controlled. Only certain individuals are given access into the network architecture, based on requests through a unit automations section. This access is granted in the mandatory access control style, assigning access to users based on their need-to-know within the organization. To further protect this database, it will be password protected within this shared network space. This will allow only certain users to access the database. Additionally, each Army network user is assigned a unique user ID based on their Department of Defense Identification Number (DODID). This ID number is passed to applications and files when they are opened and changed. Levels of access will be given to user groups based on their DODID number. HR specialists will be granted read access for all data, and write access to update transaction statuses. HR officers, or data managers, will be given all access. The computer system used for customers to start or query the status of transactions will be given read access to all data, and write access to transaction data.
Transaction Management
There are four transactions executed in this database: (1) new transaction created by soldier customer, (2) transaction completion status updated by HR specialist, (3) transaction status queried by customer, commander, or HR specialist, and (4) soldier data updated by HR officer/data manager. Because this database will be housed in a shared file space, only one HR specialist, customer, or HR officer will be able to access it at one time. This is because the shared network space only allows one DODID number to be passed into a shared file at one time. Given this information, there is no issue with concurrency, boundaries, or hotspots, given that only one transaction will occur at one time. On the practical side, this should not limit the actual customer service of a Battalion S1 severely; as it stands, most customers are served one at a time, with one HR specialist serving as the ‘customer service representative’ while the other HR specialists execute non-customer service tasks. Additionally, customers who simply desire a status on their already submitted transactions, or commanders who want to audit their soldiers’ transactions, should be able to accomplish this with a read-only Access file, which the Army shared network drive allows for while another DODID writes on the shared file.
Data loss or data corruption would be handled in this database using the recovery via rollforward method. A second copy of the database, with a change log, would be saved in a more durable location. In the case of data loss or corruption, these changes would then be rolled forward onto the living database using after images. This method is the preferred method because it is easier, given the volume of daily transactions (less than 50 per day, on average) than to try and undo partially complete transactions with rollback, or to attempt to reprocess transactions using reprocessing.
Future of the Database
Tasks for Database Rollout
As the construction of this database continues, it will attempt to reach the following milestones in order to be an operable, durable database that an Army Battalion S1 can use on a daily basis:
- Move sample data to Access database on Army system
- Build application interface on Access
- Expand action lists to include all applicable Army HR actions
- Create a backup database and build a transaction log for rollforward operations
- Build data loading code to load .csv files from Army HR programs
- Test data loading code with real data
- Build test database for one Army Battalion S1
- Test database with real customers for a period of 10-20 days, logging any errors or failed transactions
- Roll out database in a beta phase to all Fort Bragg Battalion S1s, to further test database and gather errors and inconsistencies from the field
- Roll out tested database
Conclusion
As the US Army ramps up its use of technology in order to make the modern soldier more deadly and effective, it must also implement technology which enriches the livelihoods of its soldiers, and make the administrative processes which allows soldiers to fight and win more effective. Hopefully, this database will provide one way in which the Army can modernize the way it handles administrative processes, leading to a more effective organization as a whole.