Top 30+ database design & security best practices

Creating a web application usually need a database to hold data in order to get the application work smoothly. However, poor database design may lead to many problems such as performance and security issues.

Thus, there are do’s and don’ts in designing your databases.

10 Common database design mistakes by Louis Davidson.

1. Poor design/planning
2. Ignoring normalization
3. Poor naming standards
4. Lack of documentation
5. One table to hold all domain values
6. Using identity/guid columns as your only key
7. Not using SQL facilities to protect data integrity
8. Not using stored procedures to access data
9. Trying to build generic objects
10. Lack of testing

20+ MySQL best practices on NetTuts+

1. Optimize Your Queries For the Query Cache
2. EXPLAIN Your SELECT Queries
3. LIMIT 1 When Getting a Unique Row
4. Index the Search Fields
5. Index and Use Same Column Types for Joins
6. Do Not ORDER BY RAND()
7. Avoid SELECT *
8. Almost Always Have an id Field
9. Use ENUM over VARCHAR
10. Get Suggestions with PROCEDURE ANALYSE()
11. se NOT NULL If You Can
12. Prepared Statements
13. Unbuffered Queries
14. Store IP Addresses as UNSIGNED INT
15. Fixed-length (Static) Tables are Faster
16. Vertical Partitioning
17. Split the Big DELETE or INSERT Queries
18. Smaller Columns Are Faster
19. Choose the Right Storage Engine
20. Use an Object Relational Mapper
21. Be Careful with Persistent Connections

MySQL security best practices by GreenSQL

1. Secure your server
2. Disable or restrict remote access
3. Disable the use of LOCAL INFILE
4. Change root username and password
5. Remove the “test” database
6. Remove Anonymous and obsolete accounts
7. Lower system privileges
8. Lower database privileges
9. Enable Logging
10. Change the root directory
11. Remove History
12. Patch your system

To make your live easier in designing your database, i would like to suggest MySQL Workbench. It is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system.

Happy coding!