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 :

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/

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

You may also like...

Common SQL Tasks I Do and Scripts

SQL Server IO Stall Statistics

Looking Back at 2016 !!

Leave a Reply Cancel reply