Cagdas Basaraner

About Cagdas Basaraner

Cagdas Basaraner is a software engineer graduated from Hacettepe University Computer Engineering department (Turkey), having 5 years professional experience. He is working on JEE web technologies, and also a former developer of information systems using Microsoft technologies and Command & Control (C4I) systems with Java technologies.

20 Database Design Best Practices

  1. Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID …).
  2. Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names.
  3. Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you’ll write “Student Course”. StudentCourse is much better).
  4. Don’t use unnecessary prefixes or suffixes for table names (i.e. use School instead of TblSchool, SchoolTable etc.).
  5. Keep passwords as encrypted for security. Decrypt them in application when required.
  6. Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing …).
  7. Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.
  8. Use bit fields for boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
  9. Provide authentication for database access. Don’t give admin role to each user.
  10. Avoid “select *” queries until it is really needed. Use “select [required_columns_list]” for better performance.
  11. Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis …) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.
  12. Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
  13. For big, sensitive and mission critic database systems, use disaster recovery and security services like failover clustering, auto backups, replication etc.
  14. Use constraints (foreign key, check, not null …) for data integrity. Don’t give whole control to application code.
  15. Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.
  16. Use indexes for frequently used queries on big tables. Analyser tools can be used to determine where indexes will be defined. For queries retrieving a range of rows, clustered indexes are usually better. For point queries, non-clustered indexes are usually better.
  17. Database server and the web server must be placed in different machines. This will provide more security (attackers can’t access data directly) and server CPU and memory performance will be better because of reduced request number and process usage.
  18. Image and blob data columns must not be defined in frequently queried tables because of performance issues. These data must be placed in separate tables and their pointer can be used in queried tables.
  19. Normalization must be used as required, to optimize the performance. Under-normalization will cause excessive repetition of data, over-normalization will cause excessive joins across too many tables. Both of them will get worse performance.
  20. Spend time for database modeling and design as much as required. Otherwise saved(!) design time will cause (saved(!) design time) * 10/100/1000 maintenance and re-design time.

Reference: 20 Database Design Best Practices from our JCG partner Cagdas Basaraner at the CodeBuild blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

11 Responses to "20 Database Design Best Practices"

  1. José Luís Sanchez says:

    While i do agree on most points, i do not on point 2.

    From a abstract point of view, a table is a collection of entities.

    So, USERS will store a collection of USER entities, so it will have USERS.
    The same applies to CUSTOMERS, ORDERS and so on. There will be ( hopefully ) more than 1 row of data, so PLURAL is a must. That’s why databases were created to, to store more than 1 row of data, otherwise, properties will be enough, IMHO.

    Anyway, great post !

    • Richard GUITTER says:

      Another way to think about this, is to say that one row represent one User entity. So looking at an ER diagram you will say one User has 0..n Privilege.
      Same thing for joint table which express the relation between one entity to another : UserPrivilege for example

  2. PavelRudensky says:

    Yes!!! Good rules…  Point 2 i use like José Luís Sanchez… I naming primary key columns like IDs… CourseID – foreign key on table Course.

  3. 麥當勞 says:

    I doubt point 4. If you have a big schema, the first question is whether an object a table, view, materialized view, synonym or otherwise something atypical. The content of database object is the next thing.

    • Point 4 is a good practice because the today’s table can be a view tomorrow. Using  a table, a view, a materialized view or a synonym doesn’t matter for a program that does a “Select”. The technical prefixes restrict databases evolution more that they help. If i want to know if it’s a synonym, a table or something else, I look at the database dictionary. 

  4. Pavel Tavoda says:

    I disagree with 11. Go for persistent framework specially for small and middle sized projects. For big projects (e.g. more than 400 tables) be careful about planing and query optimization specially for big tables (like more than 3Mil rows).
    Also some points don’t apply for all databases. You have to look for specific DB features.
    Point 12 is senseless. If tables are unused than remove them, if rarely used it will bring no advantage to move them to different physical storage. Moving BIG tables and partition it on different physical storage will bring performance advantage. 

    • Point 11 mainly depends on your development team strengths. For a mid range development team i think that point 11 is well formulated and, if i want to distinguish between big and small projects i would say the reverse:

      You can go by hand specially for small and middle sized projects but never for big ones.

      On small projects you can afford some architectural refactoring to correct defects. On big projects, you can’t afford it. Persistence frameworks gives an opportunity to correct lot of architectural programming defects without  breaking every thing and always allow you to fix critical queries by hand if required.

      Minimizing data flows between programs and database is the key point to success. The tuning of queries is just a final refinement to reach maximal performance.

      With persistence framework you can change the data flows later without breaking the programs and you can also tune specific queries if required.

      With queries coded by hand, to change data flows, you have to break all the program architecture. So, when you are near the deadline, you can only tune queries and, it’s not always enough to success.

  5. Neal says:

    point 6 and 19 ..you say use surrogate keys in 6, but then back out in 19
    Normalize well for posterity, imo.

  6. Rodger says:

    Hello, 

    I’ve written a whole series on Database Design Mistakes To Avoid
    http://rodgersnotes.wordpress.com/category/database-design-mistakes-to-avoid/

    As for point 20, see:
    http://rodgersnotes.wordpress.com/2010/09/14/database-design-mistakes-to-avoid/

    Concerning triggers and performance:
    http://rodgersnotes.wordpress.com/2010/09/18/referential-integrity-and-performance/

    May I add another “Best Practice”.

    # 1: Hire some experienced and serious database expertise who really knows what they are doing. No amount of java can compensate for a really bad DB put together by beginners.  

    HTH

  7. I disagree with point 10. Not using “select *” is not a matter of performance (or not only), it’s a matter of reliability. I would say:

     Never use “select *” queries, always use “select [required_columns_list]“.

    “Select *” is unreliable because if a new column is inserted in the table, your program will not be able to handle the new results. 

  8. EvilGuy says:

    Objection on Point 15

Leave a Reply


× 7 = forty nine



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close