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…
