Technical Stuff

About The Database Design

We developed custom software in order to thoroughly document our database design. This includes plain-English descriptions of every data table and field. It also includes details about the types of data stored as well as all of the logical relationships between tables.

Our database design method is based upon recommendations presented in Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (3rd Edition), by Michael J. Hernandez.

The data requirements and emerging organizational policies and practices are based upon the guidelines presented in The New World of Police Accountability by Sam Walker and Carol Archbold. The San Jose Independent Police Auditor (IPA) 2013 Year End Report, which is the gold standard for government transparency regarding police misconduct complaints, provided essential guidance. We also reviewed numerous Department of Justice (DOJ) reports on police-civilian contacts. The most important of these was the DOJ Investigation of the Ferguson Police Department. We also receive ongoing guidance, from some of the top national experts in the field of police oversight and accountability.

Software Specifications

The database design software we built exports Laravel data models and migration files in order to quickly auto-load these data structures to run The code was written using PHP, MySQL, AJAX, JS, and CSS. It was built atop the open source Laravel coding framework (also see: How To Install Laravel). The full Open Police source code still needs better documentation, but is up on GitHub for wider collaboration with additional open-source programmers.

It's like TurboTax — but for police complaints.

In order to provide complainants with a dynamic user experience, we custom built a branching tree engine. Most nodes in this branching tree represent either a single form field or a page, which wraps multiple fields. After defining the full tree, and mapping each form field to its correct field in the database, the tree is recursively traversed in order to create a sequence of prompts, which reveal the unique DNA of an individual police complaint.

Every node on the tree can check for prerequisites in earlier responses, and some areas of the tree are set to be looped through. (For example, when adding multiple witnesses, officers, or victims.) This approach to form generation means that most data prompts and storage are the default automated behavior of a generalized engine. This also allows for A/B testing of any individual field for quality control.