Author: P.V.Neir

Being developer we usually see clients complaining about database performance. Performance problems arise when we ignore the good practices. In this article I am listing the best practices you must follow while working with SQL.

1. The database design should be simple and clear. It should be user friendly. It should have flexible structure so that depending on the future requirements the database can easily be expanded.
   
   
2. Store relevant and necessary information in the database.
   
   
3. Use normalized tables in the database. It is better to use small multiple tables instead of using one large table.
   
   
4. Use denormalization as the last option for your database.
   
   
5. Use database look up tables. It will help you maintain data integrity of your database.
   
   
6. Use numeric or small width primary key for your database tables. Processing small data takes less time.
   
   
7. Do not store images in the database. Store URLs of images instead.
   
   
8. Do not display SQL errors to users. Most of the attackers get benefit of error messages.
   
   
9. Make an ERROR table in the database. Storing errors in the ERROR table will help you keep track of bugs.
   
   
10. Assign proper data types to the fields in the database. It is better to use TINYINT for age rather than VARCHAR (45).
    
    
11. Use database field names in SELECT, INSERT, UPDATE commands. Avoid using SELECT * or INSERT INTO TABLE VALUES (value1, value2…).
    
    
12. Use LIKE clause properly. Improper usage may cause performance problems in your database.
    
    
13. Use database server's timestamp instead of web server's timestamp.
    
    
14. Use alias in the SQL statements which require multiple tables.
    
    
15. Write SQL commands in capital letters. It helps in readability.
    
    
16. Use nested queries as minimum as possible. Use JOIN statement instead.
    
    
17. Avoid query running in the loop.
    
    
18. Use stored procedures. They are fast and help in maintainability and security of the database.
    
    
19. Avoid CURSOR, GOTO and IF statements. Prefer SWITCH CASES.
    
    
20. Use comments.
    
    
21. Do proper indexing. It will improve the speed of operations in the database tables. Improper or too much indexing affects performance of the database.
    
    
22. Use inet_aton() function to store IPs as integers in the database. The inet_aton() function converts the string, in the Internet standard dot notation, to a network address, and stores the address in the structure provided.
    
    
23. Retrieve the IPs with inet_ntoa() function. The inet_ntoa() function converts the specified Internet host address to a string in the Internet standard dot notation.
    
    
24. Always encrypt the sensitive data.
    
    
25. Do not rely on server configurations. Always write the most portable code possible.
    
    
26. Do proper documentation of your application.
    
    
27. Always do testing of your database application. Make cases based on requirements and maintain a checklist of the tests.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle – SQL Guide for Parallel Stored Procedures
   Great Tips on Oracle Applications Security!
   Great Tips for Test Data Generation in Oracle!!
   Oracle Guide for Disk I/O tuning!!