SLATE 1.0’s current scope and schema

Why it sucks

This and any subsequent or linked posts are living documents and are subject to change often.

To let it sink in ¹:

# todo: (1) not accessible (2) missing table (e.g., demographics)

Current Slate schema

I am sure that this is not how a well planned relational database schema should look like but then again I could be wrong, I’ll learn as I go. My concerns:

Authorization numbers are required for EVERY entry

This has some disadvantages because 
(1) unlike CORE, SIP does not use them and
(2) sometimes the paperwork from DOR lags behind but a student needs to start at a given time which means doing an intake and creating a service entry.

# link intake when that page is ready

(2) can easily be worked around by creating a dummy entry with “temp” as authorization number (“TEMP authorization”).

(1) is more cumbersome and there is no right solution in the current system because of how fundamentally differently CORE and SIP delivers services.

— “SOLUTION” 1 : Note dumping

Currently employed since maybe 2003.

It uses the Notes text field on the Intakes page for every update (contact note, progress report, lesson note etc.). “Entries” are usually copied on the top but sometimes to the bottom but I found some inserted mid-sentence. The issues with this approach is self-explanatory but there’s nothing to be done about it at this point. Migration is going to be fun.

To use the Lesson Notes and Progress Reports functionality they would have to use

— “SOLUTION” 2 : dummy authorization numbers

A new numbering scheme wouldn’t have been needed because uniqueness or a specific format is not enforced (see “TEMP authorizations”).

Unfortunately lesson notes and progress reports are specifically tailored for CORE:
 * billing for DOR
 
* non-adjustable instructional units
 
* without a numbering scheme there is no way to query entries ²

EMPLOYEE table <-> MEMBER table

EMPLOYEE table contains DOR counselors only and the MEMBER table all the SLATE 1.0 users. Let’s move on to the next inconsistency.

CONTACT table

It is not normalized at all and contains information that should go to the DEMOGRAPHICS table.

The Deceased column is useless because it cannot be queried from the front end.

INTAKE table

It is the (con)fusion of multiple contact types (volunteers, clients, donors) with a pinch of demographics information.


  1. ^ Had to post the image in full size otherwise Medium would degrade the image quality even if opened in a new window. (Tried it but maybe I am wrong.)
  2. ^ I am pretty sure at this point that it is impossible to do a normal query it at all…