Database Design Best Practices

Database Design: Common Practices for Database Developers and Application Developers

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 based 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 unnecessary indexes.
  • You should not auto-number data type as a PRIMARY KEY to join to 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 long data-type columns from base 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 based on external meaningful data. Surrogate Primary key are keys based on 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 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.
Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of