Database Development Standards

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

StructureDefinition
/DatabaseNameRoot of the source code database name.
../cmdControl files used by the database build process specific to this database.
../dataData Files for Domain Data
../data/TenantTenant Specific Domain Data
../docDatabase documentation
../funcScripts to create user defined functions.
../globalScripts for database users and database permissions.
../releaseBuild scripts to promote database from one version to the next.
../spScripts to create stored procedures.
../tblScripts to create tables.
../tbl/constraintScripts for table constraints
../tbl/constraint/FKScripts for foreign key constraints.
../tbl/constraint/PKScripts for primary key constraints.
../tbl/indexScripts for indexes and statistics on tables.
../tbl/trgScripts for triggers on tables.
../viewScripts for views.
../snonymScripts 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 VerbDescription
AddInsert a specific row.
DelDelete a row(s) from a table..
GetReturn result set consisting of at most one row; Usually based on Key Column of the Table.
LstReturn result set that may have more than one row.
MrgUpsert - Update or Insert
Merge. Inserts or updates row in table.
SetUpdate a specific row or set of rows.
VldPerform data validation, typically for a row to be updated or inserted.
LogMaintains audit log of changes (update, insert, delete) to a specific row or a set of related rows.
SrchSearch for a specific row or set of rows. See comparison to “Lst” above.
TrimTrim (purge) a set of rows from a table.

Objects Naming Guidelines :

ObjectGuildelinesExample
TableKeep 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
ColumnColumn 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.
Indexesix[TableName]_[Qualifier]
ncix - Non Clustered Included Index
ncx - Non Clustered Index
ucx - Unique Clustered Index
uicx - Unique Clustered Index with Included Column

Constraints
Default Constraintsdf_[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:

ColumnNameDescription
StatusENUM Values to specifiy differnt status for the record. ( Active, In-active , Disabled , Enabled )
CreatedByIdentifies the user created the record. It is preferable to require an explicit value but an acceptable default is the SQL Server suser_sname() function.
CreateDateTimeIdentifies date and time of the last modification in the database.
LastUpdatedByIdentifies user making change. It is preferable to require an explicit value but an acceptable default is the SQL Server suser_sname() function.
UpdateDateTimeIdentifies 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/

http://www.isbe.net/ILDS/pdf/SQL_server_standards.pdf

One comment on “Database Development Standards
  1. T-Enterprise says:

    If you need help developing custom software and databases or a cloud-based B2B app for your company, the experienced team at Tentacle Solutions is happy to assist. If you want to read more about this:
    https://tentacle.solutions/articles/tentacle_glassdoor_reviews.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

*