jonmelvin
commented
5 years ago -- The client whose tickets are to be dismissed -- Note 1: instead of checkboxes for status when actions are done, dates are to be entered; that way time taken can be tracked -- Note 2: The field ID is a unique integer key; the other ... fields are tracking fields for who edited and when, subject to modification -- Note 3: The structure of this table is subject to change as we refine the work flow with Gina -- Note 4: The fields [Picture], [_file_Picture], and [_typePicture] are specific to the CSharp framework - they trigger generation of file upload controls for, -- in this case, a photo of the user CREATE TABLE [dbo].[Client]( [_ID] [int] NOT NULL, [ID] varchar NULL, [Date_Record_Created] [datetime] NULL, [First_Name] varchar NULL, [Middle_Name] varchar NULL, [Last_Name] varchar NULL, [Drive's License] varchar NULL, [SSN] varchar NULL, [Date_of_Birth] [datetime] NULL, [AKA_1] varchar NULL, [AKA_2] varchar NULL, [AKA_3] varchar NULL, [Phone_1] varchar NULL, [Phone_2] varchar NULL, [Email_1] varchar NULL, [Email_2] varchar NULL, [Clinic_Attended] varchar NULL, [Date Closed - No Obligation Form] [datetime] NULL, [Date Closed - Client has Warrant] [datetime] NULL, [Date Closed - Client has no Tickets] varchar NULL, [Date Obligation Form Completed] varchar NULL, [Date Waiting for background check] varchar NULL, [Date of Attorney Review] [datetime] NULL, [Date Sent to Court] [datetime] NULL, [Date Received from Court] [datetime] NULL, [Date Report Sent to Client] [datetime] NULL, [Date Closed - Complete] [datetime] NULL, [Picture] [image] NULL, [_file_Picture] varchar NULL, [_type_Picture] varchar NULL, [_User] varchar NULL, [_ModifyUser] varchar NULL, [_CreateDate] [datetime] NULL, [_ModifyDate] [datetime] NULL )
-- One or more citations per client (CLIENTID is foreign key into [CLIENT].[ID] - this is many to one related to client) CREATE TABLE [dbo].[Citation]( [_ID] [int] NOT NULL, [Client_ID] varchar NULL, [ID] varchar NULL, [Citation_Number] varchar NULL, [Court_Code] varchar NULL, [Violation_Number] varchar NULL, [Citation_Status] varchar NULL, [_User] varchar NULL, [_ModifyUser] varchar NULL, [_CreateDate] [datetime] NULL, [_ModifyDate] [datetime] NULL )
-- One or more services carried out by the client to dismiss citations (CLIENTID is foreign key into [CLIENT].[ID] - this is many to one related to client) CREATE TABLE [dbo].[Service]( [_ID] [int] NOT NULL, [Client_ID] varchar NULL, [ID] varchar NULL, [Service] varchar NULL, [Date_Completed] [datetime] NULL, [Notes] varchar NULL, [_User] varchar NULL, [_ModifyUser] varchar NULL, [_CreateDate] [datetime] NULL, [_ModifyDate] [datetime] NULL)
-- database admins and users CREATE TABLE [dbo].[User_]( [ID] [int] NOT NULL, [Username] varchar NULL, [Password_] varchar NULL, [Confirm_Password] varchar NULL, [Disable_select] char NULL, [Group_lookup] varchar NULL, [First_Name] varchar NULL, [Middle_Name] varchar NULL, [Last_Name] varchar NULL, [Phone_1] varchar NULL, [Phone2] varchar NULL, [Number] varchar NULL, [Street_] varchar NULL, [ApartmentNumber] varchar NULL, [City] varchar NULL, [State] char NULL, [Zipcode] varchar NULL, [Email_1] varchar NULL, [Email2] varchar NULL, [Notes] varchar NULL, [Picture] [image] NULL, [_file_Picture] varchar NULL, [_type_Picture] varchar NULL, [_User] varchar NULL, [_ModifyUser] varchar NULL, [_CreateDate] [datetime] NULL, [_ModifyDate] [datetime] NULL, )
-- other planned tables: Citation codes, Service descriptions, Clinics attended, Court document templates to merge (these last items can also -- just be files residing in the server folder)
tienyuan
Goal
Design & document a sql database schema based on the information architecture.
Desired Result
A diagram like those pictured here so we can put it into the Wiki/Discuss/Implement
Resources/Content
Information Architecture from 11/26/18 Design Sprint is: https://github.com/hackforla/heart/wiki/Information-Architecture
Please also review the 12/3/18 Mockup Discussion notes: https://hackforla.slack.com/files/U9TEHKS8M/FEHVD4B33/notes_from_design_review_12_3_19.txt
Ordering Needs "What happens with the urgent rush people? Would it prioritize them or allow us to sort it?" People should be sorted by clinic attended date except when "urgent rush", then move to top in order of urgent rush.
Aka Gina and her team may opt to prioritize certain participants because of special circumstances. For example, someone may need their record expunged to pass a background check for a job application. All urgent-rush people should be before non-urgent-rush people.
For the sake of our future selves/volunteers, we're trying to document the intent/thoughts/assumptions/decisions that lead up to the code.
So this is an extra wordy issue description 😃