Design & document a sql database schema about heart HOT 6 CLOSED

FYI @mitchellwandrews @jonmelvin

I did a quick comparison between the IA:Intake Form fields, the IA:Profile Page fields and the designed intake form (in progress)

Mostly matches. Fields in the Intake Form exist in the Profile Page. Note, the design is missing the legal agreement fields, the electronic sig fields and implies that Clinics Attended have dates. So best to continue to go by the Info Architecture doc

from heart.

-- 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 [type_Picture] 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 (CLIENT_ID 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 (CLIENT_ID 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,
[Phone_2] varchar NULL,
[Number_] varchar NULL,
[Street_] varchar NULL,
[Apartment_Number] varchar NULL,
[City_] varchar NULL,
[State_] char NULL,
[Zipcode_] varchar NULL,
[Email_1] varchar NULL,
[Email_2] 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)

from heart.

👍 I look forward to hearing your thoughts on Monday. I'll study it a bit this weekend.

https://dataedo.com/kb/tools/ssms/create-database-diagram seems to say it's possible to generate a prettier version of the db schema so it's easier to see relationships

from heart.

Diagram from SQLServer management studio for the tables (does not include the update for having the picture in the client table - see text doc above for that).

from heart.

We discussed the database during hacknight and an updated version of the schema is in progress

from heart.

Addressed in #32

from heart.