Database Practices

Database Best Practices for Developers

Database code must be organized, clean and as managable as your application code.

In my experience I’ve noticed many developers manage their database code with a lot less attention, and care than their c#/java applications that the databases are tied to. Ultimatlely this builds up to point where it becomes very hard to manage.

For a quick list of guidelines see my post at
Database best practices

With regard to guidelines for databases:

Database Standards

  • Coding conventions for T-Sql code.
  • Naming conventions for tables.
  • Naming conventions for stored procs & functions.

Sample naming convention for stored procs
I typically name my stored procedures with the following conventions:
<applicationPrefix>_<module>_<crudOperation_or_other_action>

The applicationPrefix is used so that I can easily associate stored procedures that are part of my application versus other ones, such as asp.net membership stored procs.

e.g.

Database object Example
Tables kd_Articles
 
Stored Procedures kd_Articles_Create
kd_Articles_Update
aspnet_Users_Create
 

   

Database scripts:

Create_Schema.sql Creates schema ( tables ) for your app
Create_Schema_Code.sql Creates stored procs for your app
Create_Schema_3rdParty.sql Creates schema ( tables ) that are used by 3rd party compents that you use in your application.
Create_Schema_3rdParty_Code.sql Creates stored procs that are provided / used by 3rd party components that you use in your app
Delete_Schema.sql Deletes schema for your app
Delete_Schema_Code.sql Deletes stored procs/funcs for your app
Delete_Schema_3rdParty.sql Deletes schema for 3rd party tables
Delete_Schema_3rdParty_Code.sql Deletes stored procs for 3rd party stored procs
Load_Data_Core.sql Loads all the reference data
Load_Data_Test.sql Loads test data
Clear_AllData.sql Clears all data for your app. ( Only for DEV/QA )

   

Database schema upgrade scripts:

Always upgrade your schema via sql scripts.
A schema upgrade script should be created for each release that contains a database change.

For example:
upgrade_release_0.91.sql
upgrade_release_0.92.sql

— Add isTravelRequired field to Events table & set existing records to false
alter table myapp_Events add [IsTravelRequired] [bit] NULL
go

update myapp_Events set IsTravelRequired = 0
go

   

Backup Database schema after each release:

Always backup your database schema ( if it has changed ) after each release.
This schema is stored in the files mentioned above.
Create_Schema.sql
Create_Schema_Code.sql

 

Make effective use of ORM where appropriate

Object Relational Mapping can greatly improve productivity by reducing your development time on the database storage/retrieval ( persistance ) code. ORM libraries are used to map the database tables/columns to your business / domain objects.
You should consider and test the performance impact of using ORM.
This is because ORM libraries use reflection for determine how to map the database data.

Historically, I’ve used strong combination of both ORM and custom/typical use of stored procedures in most of my apps.

Examples of ORM:
(Note that there are several frameworks and libraries for ORM)

1. NHibernate
2. IBatis
3. .NET Linq
4. ADO Entity Framework

   

Common Database Guidelines

– Create indexes for tables columns that are heavily searched against.
  create nonclusted index CategoryIndex on kd_Events( CategoryId )

– Handle errors/exceptions in stored procedures and return error codes where appropriate

   

Testing the database

Testing stored procedures can be more difficult than testing application code but it can still be done.

NOTE: The only catch is to always put your database back into a valid state for testing.
What happens if your stored proc fails and makes a change to the database that affects future tests ? What you can do is clear out certain data before each test is run.

NOTE: Also you have to make sure that you always connect to the right environment!! Yes as silly as it sounds, it can happen by mistake.

Unit testing your data access code
The easiest way to test your stored procs is by testing your data access code which is calling your stored procedures.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: