17 Relational Database Best Practices

There are no silver bullets to database bottle neck, these 17 best practices can help you design a secure and fast database as they have helped me in the past, it should help you too.

1. List column names on a SELECT, INSERT, and "INSERT INTO SELECT FROM .."  SQL statements.
2. A table MUST have a Primary Key.
3. Grant a minimum Role/Privilege to a user.
4. Data should not live on database forever therefore, plan to purge or archive it.
5. Drop Index, load data and create Index for faster performance.
6. Drop table with PURGE command if you don't need the table and data at all.
7. Minimize the use of Trigger and never use it to implement a business logic. Trigger are notorious for slowing database down.
8. Substitute Literals with Bind Variables where possible. Bind variables make the query execution faster by soft parsing.
9. Avoid UNION and use UNION ALL.
10. Avoid Public SYNONYM and DB LINK. Security audit team hates them.
11. Use Stored procedure to manipulate data, do not use SQL query from the script. Stored Procedures are secure and fast.
12. Use VARCHAR2 instead of CHAR. This saves disk space.
13. Don't Index all table columns, this degrades the performance.
14. Use Table Partition on a big table. Great for performance and maintenance.
15. Use Merge and avoid multiple IF conditions.
16. Index your JOIN and WHERE columns.
17. Perform Delete or Update in batches.

If you’ve got a question for me, you can email me at pbaniya04@gmail.com. If you liked this article and want to read more tips, articles, and tutorial related database, please subscribe to my blog and share this with your buddies.