Database Design: Common Practices for Database Developers and Application Developers

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing the list of common practices for Database Designing which should follow by Database Developers and Application Developers.

• Foreign keys should have indexes on them, and it should often cover multiple columns base on the queries you need to execute.
• You should make sure about the referential integrity by enforcing the Foreign Keys.
  Choose right column for CLUSTER Index.
• You should apply proper indexes and do not create duplicate indexes and avoid creation of unnecessary indexes.
• You should not put auto-number data type as a PRIMARY KEY to join a FOREIGN KEY in a parent/child table relationship.
• If your system is running large number of INSERT/DELETE, you should avoid frequent update of database Statistics.
• You should choose the write data-type for your data columns.
• Please separate big size of columns from main table and store in a different table.
• You should apply proper column name.
• Use proper data types for Latitude, Longitude, IP-Address, Network Address, Mac Address.
• Use Natural Primary key rather than Surrogate Primary key. Natural Primary keys are keys base on an external meaningful data. Surrogate Primary key are keys base on an auto-increment number or values which have no meaning outside the system.
• You must use Stored Procedures or Stored Functions.
• Please avoid too much database normalization and try to design your database to achieve Database Normalization + Database Denormalization. You should design this combination to achieve the performance.
• Avoid inappropriate or excessive use of cursors.
• Avoid inappropriate use of Database Transactions (BEGIN…COMMIT).
• Try to avoid the use of OR condition because an AND condition restricts the data set, whereas an OR condition grows it.
• You should do detailed performance analysis on queries.
• Try to use the Prepared SQL Statement which caches the compiled query and the execution plan for better performance. It will also better protect you against SQL injection attacks.
• Please do not simplify your complex queries through the views.
• Please do not use DISTINCT frequently. Many developers are using DISTINCT to hide duplicate data, but you should stop and remove the duplicate data.
• Try to avoid Correlated Subqueries.
• Do not store comma delimited list in a table.
• Do not store JSON data in TEXT COLUMN, you should use JSON data type for it.
• Please do not use comma separated table names in SELECT query, use explicit JOINs conditions to avoid internal Cartesian products.

 
The list is not final yet, you guys can add/share a comment and make it final…