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

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 :
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:

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/

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

Leave a Reply

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

*