A Database Prototype

This is prototype of the ACO Application, which is an app in the Coalition's workflow management software. This database contains fields within the current ACO Application. This prototype is built using PostgreSQL.

This repository houses the SQL code used to build the database.

Download and Install PostgreSQL

To download PostgreSQL, please see their download page.

To install, please see the detailed installation guides on the PostgreSQL wiki.

pgAdmin III

pgAdmin is development and administration GUI that supports all of PostgreSQL's features. It usually comes installed with PostgreSQL.

Create the database in PostgreSQL

In pgAdmin III, open the PSQL Console. Run the following SQL code to create your database using the default database server:

CREATE DATABASE aco;

Run the SQL code

To run the SQL code in the PSQL Console, use the following command:

\i [filename].sql;

Use the forward slash / if the location of the SQL code is in a different directory. You can also add the network drive, if necessary (C:/ or Y:/, for instance):

\i [directory]/[filename].sql;

R Scripts

• utilization.R Removes duplicate Admission IDs
• match.R Filters Client Track records (work in progress)
• rename_csv_col_name.R Rename columns to match column names in the Postgres database (work in progress)

Notes

• The data type of all columns had to be changed to varchar, usually from numeric or date, to reflect challenges of importing data for this prototype Postgres database.
• Some editing of the CSV files used to import into the database had to be done manually into Excel.
• In a perfect world:
  • Data types will remain as originally intended
  • Data cleaning/manipulation will be done using an automated script