Whenever we develop a new application, how to design and access the database is always one of the most significant parts we need to consider. In fact, there are many best practices for us to optimize the performance of our databases. However, if you compare these best practices, you may find that these best practices are not consistent with each other. In other words, you cannot apply all the best practices in one database. I will show two examples to explain this:
The first example: choosing between JDBC(iBATIS) and ORM framework.
In the recent years, ORM framework, such as Hibernate and JDO, is more and more popular in the J2EE field. No matter how popular it is, ORM framework still has some drawbacks. The performance of using ORM tools to access the database is not as good as using JDBC, furthermore, the technology of JDBC is more mature. However, using ORM tools get us out of the complexity of the SQL, and it also makes the application more portable. Therefore, in most cases, ORM framework is our first choice, but it not absolute. Of course, when you decide to use a ORM tool, it means you need to study it firstly, which would prevent some programmer from using the ORM tool, because they know JDBC and SQL pretty well and it seems that they are wasting their time to study another technology to access the database, in addition, ORM framework is not good enough!
The second example: find balance between normalization and denormalization
As we all know, when we design our database system, the process normalization helps us to reduce the redundant data in our database. However, the disadvantage of normalization is seprating the related data into differenct tables, which means that when you search those data, you need use a lot of “join” manipulations. That will influences the performance of the application. Therefore, in order to make up the performance, sometimes, we have to take the step of denormalization. The problem is that normalization and denormalization are totally contrast to each other. Each time when we design our databses, there is no doubt that we should use 1NF, 2NF and 3NF to normalize the database. But when should we take the process of denormalization? I cannot answer this question, because I think that depends on your experience.
In a word. each technology has its own pros and cons. Do not stick to one, and choose them according to the specific problem.