#3: Going overboard with normalization

Debates around normalization often center on query performance, forgetting that normalization serves additional purposes, particularly with regard to data integrity. In addition, these discussions often fail to consider other performance-related issues, such as indexing, contention, bandwidth, and system resources. It’s easy to play the blame game with normalization because it’s an abstract enough concept to warrant contempt, but in many cases, it’s the wrong target.

Even so, the debate brings up an important point: Database design should take into account a number of considerations, including the queries accessing the data. If you have to join 20 tables to retrieve a single customer name, you might have taken normalization a step too far. Yet even that might be okay under certain circumstances.

#4 : Lack of documentation

Not only will a well-designed data model adhere to a solid naming standard, it will also contain definitions on its tables, columns, relationships, and even default and check constraints, so that it is clear to everyone how they are intended to be used. In many cases, you may want to include sample values, where the need arose for the object, and anything else that you may want to know in a year or two when “future you” has to go back and make changes to the code.

#5 : Not using stored procedures to access data

Stored procedures are your friend. Use them whenever possible as a method to insulate the database layer from the users of the data. Do they take a bit more effort? Sure, initially, but what good thing doesn’t take a bit more time? Stored procedures make database development much cleaner, and encourage collaborative development between your database and functional programmers.

A few of the other interesting reasons that stored procedures are important include the following.

• Maintainability

Stored procedures provide a known interface to the data, and to me, this is probably the largest draw. When code that accesses the database is compiled into a different layer, performance tweaks cannot be made without a functional programmer’s involvement. Stored procedures give the database professional the power to change characteristics of the database code without additional resource involvement, making small changes, or large upgrades (for example changes to SQL syntax) easier to do.

• Encapsulation

Stored procedures allow you to “encapsulate” any structural changes that you need to make to the database so that the knock on effect on user interfaces is minimized. For example, say you originally modeled one phone number, but now want an unlimited number of phone numbers. You could leave the single phone number in the procedure call, but store it in a different table as a stopgap measure, or even permanently if you have a “primary” number of some sort that you always want to display. Then a stored proc could be built to handle the other phone numbers. In this manner the impact to the user interfaces could be quite small, while the code of stored procedures might change greatly.

Created by :

Siti Fidyanti N Hadju/1801448165

Marisa Karsen/D4639