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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.