Database Development Standards
The purpose of Database Design and Development Standards is to provide as much consistency to development work as possible.
Create an environment where sharing team resources is easier based on code consistency.
Reduce design, development, support and maintenance costs.
Institutionalize best practices.
These are guidelines not rules.
They will evolve over time with changes in technology, discovery of better methods.
Naming Conventions
Database Source Control Structure
| Structure | Definition |
|---|---|
| /DatabaseName | Root of the source code database name. |
| ../cmd | Control files used by the database build process specific to this database. |
| ../data | Data Files for Domain Data |
| ../data/Tenant | Tenant Specific Domain Data |
| ../doc | Database documentation |
| ../func | Scripts to create user defined functions. |
| ../global | Scripts for database users and database permissions. |
| ../release | Build scripts to promote database from one version to the next. |
| ../sp | Scripts to create stored procedures. |
| ../tbl | Scripts to create tables. |
| ../tbl/constraint | Scripts for table constraints |
| ../tbl/constraint/FK | Scripts for foreign key constraints. |
| ../tbl/constraint/PK | Scripts for primary key constraints. |
| ../tbl/index | Scripts for indexes and statistics on tables. |
| ../tbl/trg | Scripts for triggers on tables. |
| ../view | Scripts for views. |
| ../snonym | Scripts for creating Synonyms. |
Stored Procedures :
Typically Stored procedures perform one or more common database activities (Read, Insert, Update, and/or Delete) on a table, or another action of some kind. Since stored procedures always perform some type of operation, it makes sense to use a name that describes the operation they perform. Use a verb to describe the type of operation, followed by the table(s) the operations occur on.
Ex : “GetProductInfo”, “SetOrder” ,”DelOrder” ,”VldOrder”
| Prefix Verb | Description |
|---|---|
| Add | Insert a specific row. |
| Del | Delete a row(s) from a table.. |
| Get | Return result set consisting of at most one row; Usually based on Key Column of the Table. |
| Lst | Return result set that may have more than one row. |
| Mrg | Upsert - Update or Insert Merge. Inserts or updates row in table. |
| Set | Update a specific row or set of rows. |
| Vld | Perform data validation, typically for a row to be updated or inserted. |
| Log | Maintains audit log of changes (update, insert, delete) to a specific row or a set of related rows. |
| Srch | Search for a specific row or set of rows. See comparison to “Lst” above. |
| Trim | Trim (purge) a set of rows from a table. |
Objects Naming Guidelines :
| Object | Guildelines | Example |
|---|---|---|
| Table | Keep in mind you will most likely have to utilize the names you give your tables several times as part of other objects naming conventions. So Its Important Tables are named appropriately. The name of the table should reflect the name of the physical object it represents. Table names should be composed of a singular name. No Prefixes unless its deemed necessary. No Special Characters Follow CamelCasing Convention No Reserved Keywords which causes issues. Follow specific Structure for Independent tables , Dependent Tables , associative Tables and DomainTables. | Tenant , Student , Country , Traveler |
| Column | Column names should be based on the attribute names in the logical model. Columns are members of the table, so they do not need the any mention of the table name to be repeated. | |
| Indexes | ix[TableName]_[Qualifier] ncix - Non Clustered Included Index ncx - Non Clustered Index ucx - Unique Clustered Index uicx - Unique Clustered Index with Included Column | |
| Constraints | ||
| Default Constraints | df_[TableName]_[ColumnName] | |
| Check Constraints | ck_[TableName]_[ColumnName] | |
| Primary Key Constraints | ||
| Foreign Key Constraints | ||
| Triggers | [TableName]_ [Aft|Bfr]_[Ins|Upd|Del] | |
| Cursor Names | [TableName]_ Cursor | |
| Statistics Name | st_[TableName]_Qualifier |
Audit columns
All tables should include columns to track change history (status , who , when ). For simplicity, even if a row is never updated the Update columns should be used.
The following of columns are used:
| ColumnName | Description |
|---|---|
| Status | ENUM Values to specifiy differnt status for the record. ( Active, In-active , Disabled , Enabled ) |
| CreatedBy | Identifies the user created the record. It is preferable to require an explicit value but an acceptable default is the SQL Server suser_sname() function. |
| CreateDateTime | Identifies date and time of the last modification in the database. |
| LastUpdatedBy | Identifies user making change. It is preferable to require an explicit value but an acceptable default is the SQL Server suser_sname() function. |
| UpdateDateTime | Identifies date and time of the last modification in the database. Its preferable to use Current_Timestamp function. UpdateDate should be set inside the stored procedure modifying the table and not exposed as a parameter. |
Error Handling
Its critical to capture errors and surface it to the meaningful message to user as well as error logs. The developer must identify what conditions constitute an error condition and provide output signaling an error condition. It is best to capture @@ERROR after all critical actions so feedback can be generated on success or failure. .
Do not use the WITH_LOG option in RAISERROR since this requires “sa” privileges.
Security
Secure databases doesn’t happen overnight. It needs careful design and dedication to follow through operations.
Applications should always use least privileged logins to access the database.
Application should never use SA Login and password to login to the SQL Server.
By Default grant no permissions and explicitly grant permissions to required objects and schemas.
It would be ideal to have just EXEC permissions to Application Logins.
Code Review
Never release anything to production till it passes through code-review process. Its very easy to overlook simple details which might cause havoc in production environment
References:
CodePlex MetaData Based Deployment
https://md3.codeplex.com/SourceControl/latest
MSSQLTips Blog Post on Stored Procedure Naming Conventions
https://www.mssqltips.com/sqlservertutorial/169/naming-conventions-for-sql-server-stored-procedures/