Tables

1. Please take the following into consideration when creating your table to adhere to Echo Vantage development standards. It is also highly recommended to use the Form DesignEHR SQL templates that are provided by Echo when creating tables. 
   • The table name should start with your agencies pre-determined preface. For example if the agency name is The Echo Group then you may start the table with TEG.
     • TEG_TableName
   • Follow Upper Camel Case convention – capitalize each new word in the table name and in column names. 
     • ClientMedications - Correct method.
     • client_medications
     • clientMedications
   • The table’s name should be plural where applicable.
     • ClientMedications vs. ClientMedication
   • Every table requires a primary key field
     • Can be defined inline as id CHAR(36) CONSTRAINT PK_TableName PRIMARY KEY CONSTRAINT DF_[TableName]_id DEFAULT dbo.NewSmartGuid().
     • Name the primary key: id (lower case only) , type is char(36) and the default is dbo.NewSmartGuid()
   • The table should also include the default Create and Update fields.

   • CreateDate DATETIME NOT NULL CONSTRAINT DF_[TableName]_CreateDate DEFAULT CURRENT_TIMESTAMP, UpdateDate DATETIME NOT NULL CONSTRAINT DF_[TableName]_UpdateDate DEFAULT CURRENT_TIMESTAMP, CreateUser CHAR(36) NOT NULL CONSTRAINT DF_[TableName]_CreateUser DEFAULT SYSTEM_USER, UpdateUser CHAR(36) NOT NULL CONSTRAINT DF_[TableName]_UpdateUser DEFAULT SYSTEM_USER,

Columns

1. Follow Upper Camel Case Convention.
   1. AcknowledgeDate - Correct method.
   2. acknowledge_date
   3. acknowledgeDate
2. Should use complete words such as Medication instead of med.
3. Keep columns to less than 30 characters whenever possible. 
4. Check Box fields will use Y or N as their values and should be defined as CHAR(1) DEFAULT 'N'
5. Define reasonable defaults where possible, especially on NOT NULL columns.
6. Use the most accurate data type for the data being stored. For example if you can do math on a value it should be a numeric type such as INT or DECIMAL instead of a text type like CHAR or VARCHAR. Don't use DATETIME when you do not need to track the time, use DATE instead.
7. Naming columns with common abbreviations (such as NPI) is fine if they are abundantly obvious in the context.
8. Be aware of SQL reserved words when creating columns in your table. Columns should be typed in without the square brackets [ ] when creating the table. If the column name should be black in color or it shouldn't be used as it's reserved. Example [ShutDown] would work, but it should never be used. Type in ShutDown and notice how it turns blue, indicating it's a reserved word.

Foreign Keys

1. Any column that references a record in another table should use a foreign key constraint.
2. Foreign keys should always link to a table's primary key column. For example link to Clients.id not Clients.clientCode.
3. Foreign key naming convention is FK_SourceTableName_SourceColumnName_TargetTableName_TargetColumnName. For example a foreign key on from a ClientAlergies table pointing back to the Clients table would be named FK_ClientAllergies_Client_Clients_id.
4. Foreign key columns have the same datatype as the target column, usually CHAR(36).
5. Foreign key columns should be named with the singular version of the target table. For example a column that references back to Clients would be named Client.
6. Sometimes a table references another table multiple times. In such cases it is fine to name neither after the target table and find something else descriptive.

Using Templates

1. There are ten form locations in EchoVantage v3.25 and below. An 11th location of FAMILY was included in the release of v3.26.
   • 
   • Timeline – VHR Forms specific to Clients – required fields:
     • [Client] char(36)
       • Required for all FD forms that will be displayed on the VHR.
       • May be used in the client translation at the top of forms.
     • [VHRStartDate] datetime
       • Required for all FD forms that will be displayed on the VHR as a depiction. Mark this field as required within the Form during creation. If the Form should display a Bar then create the VRHStartDate and an VHREndDate within the table and within the Form.
     • [Revision] int
       • Required for future modifications to the form if the form is using FD Signatures.
       • Set a default for the day the revision took place i.e. 1
       • See section below on maintaining within Category Depiction Manager.
   • Group Note - (v3.19 and earlier this was Event Template Note) Group Progress Notes
     • [Event] char(36) – foreign key to the Events table.
   • Progress Note – Individual Progress Notes
     • [Service] char(36) – foreign key to the Services table.
   • Treatment Plan – Cover Page, Summary Page or Review Page.
     • [TxPlanClientPlan] char(36) – foreign key to the TxPlanClientPlans table.
   • Menu – All other forms and can be used to track further details about Staff or any other data that is not client-based. Mapping forms for custom billing or state reporting Acorns often use this location.
   • Occupancy Client - Required for a form to be accessible from the Placements menu. 
     • [Space] char(36) - foreign key to the Spaces table. 
   • Informs - (previously Device) Required for a form to be available to share to a Device User via the Share Forms icon in the Client Header.
   • Client Tab - Required for a form to be available to add to a client tab.
     • [Client] char(36) - foreign key to the Clients table. 
     • Do not use the dbo.Clients table as the main table in a Client Tab form as it will not work properly. 
   • Remote Form - Required for forms to be available for use Offline in the Offline Forms application.
2. The remaining columns should be specific to the name of the fields where data is entered. Labels and Signatures do not require a field in the table.
   • As an example, when creating a field to track the Presenting Problem, the field name could be PresentingProblem.
3. There are 6 main data types used when creating fields within a table.
   • CHAR – character – used for primary/foreign keys char(36) and checkboxes char(1) store Y or N DEFAULT 'N'.
   • VARCHAR – variable character - used for system validated fields, memo fields or any field where you would track a name, address or some other variable length of data. Anything over 200 characters should be a varchar(4000) field and use a memo field in the Form. More characters can be added later if needed. Also used for system fields such as StaffCode varchar(15) – if you’re not sure what the size of the system field is then look it up in SQL Management Studio. We use varchar instead of char system field validations because Form DesignEHR trims the extra spaces at the end of a value by default.
   • DATETIME – used for all fields where you want to track the Date and the Time or just the Time.
   • DATE – used for date only fields.
   • NUMERIC(x,y) – used for monetary or decimal values. X is the total number of digits and y is digits after the decimal.
   • INT – integer – used for whole numbers

Common data objects

1. Checkboxes (Yes/No, True/False) should use Y or N as the values and should be a CHAR(1).
2. Radiobuttons should use the same value as what is displayed to make reporting easier and NA should be included as the selected item. In the case of Yes/No/NA, the values would be Yes/No/NA as well as the display.
3. Use the most accurate data type for the data being stored. For examples if you can do math on a value it should be a numeric type such as INT or DECIMAL instead of a text type like CHAR or VARCHAR. Don’t use DATETIME when you do not need to track the time, use DATE instead.
4. If your field is already collected in the database its best to use the same datatype and length i.e. Address1 varchar(100), Address2 varchar(50), City varchar(50), State char(2), Zip char(5), ZipExtension char(4), County varchar(30).