This guide defines the database standards in use in EchoVantage. It addresses naming conventions for databases objects, indexing best practices, and default columns for new tables. It is recommended that these guidelines be reviewed and followed when creating a custom tables. It is also recommended to use the Form DesignEHR SQL templates that are provided by ECHO. Additional information on creating tables and columns, forms, revision history within a form, and moving forms from CDT to EV may be found clicking HERE to access the Form DesignEHR user guide.

Tables

1. The table name should start with the agency's pre-determined preface. For example if the agency name is The Echo Group then the table name may start with TEG, ie TEG_TableName
2. Follow Upper Camel Case convention – capitalize each new word in the table name and in column names. 
   • ClientMedications - Correct method.
   • client_medications - Incorrect 
   • clientMedications - Incorrect
3. The table’s name should be plural where applicable.
   • Clients vs Client
   • ClientMedications vs. ClientMedication
4. 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()
   • Field should not  be capitalized, values such as ID, Id, and iD could cause problems as Java and Javascript use case sensitive string comparisons by default.
5. 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.
   • AcknowledgeDate - Correct method.
   • acknowledge_date - Incorrect
   • acknowledgeDate - Incorrect
2. Should use complete words such as Medication instead of med.
3. Keep columns to less than 30 characters whenever possible. 
4. Boolean fields (Yes/No, True/False, etc.) fields should use Y or N values, be defined as CHAR(1) NOT NULL, have a reasonable default, and have a check constraint allowing only Y or 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. The column name should be black in color or it should not be used as it is reserved. Example [ShutDown] would work, but it should never be used. Type in ShutDown and notice how it turns blue, indicating it is 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 from a ClientAllergies 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.

Indexes

1. By default SQL Server will place a CLUSTERED INDEX on the primary key field on a table, which is usually correct.
2. Any columns with a foreign key should also have an index.
3. Index naming convention is INDX_TableName_ColumnName. For example an index on ClientAllergies.Client would be named INDX_ClientAllergies_Client. If multiple columns are part of the index they can each be added to the end, separated by an underscore.
4. Fields that are commonly filtered on, such as status fields, are also good candidates for indexes. For example if it will be very common to see queries like ... FROM Services s WHERE s.Status = ? it can be beneficial to put an index on that Status field.

Unique Constraint

Though not possible in all cases it is ideal to have at least one unique constraint on a table to prevent duplicates. Unique constraint naming convention is UN_TableName_ColumnName. Similar to the index naming, convention additional fields can be added to the end, separated by an underscore. For example, if a Client should only be tied to the same Allergy once then the constraint would be named UN_ClientAllergies_Client_AllergyCode.

A unique constraint is also an index, so if a column (or combination of columns) has a unique constraint then an additional index is unnecessary.

Check Constraint

When a column will always contain one of a known set of values a check constraint can help prevent bad data. For example, a Q should NEVER end up in a Y/N field. The naming convention is CK_TableName_ColumnName, for example CK_Clients_Archived.

Default Constraints

Default constraints should always be named using the naming convention DF_TableName_ColumnName, for example DF_ClientNames_Alias.