Denormalizing MySQL Database

Denormalizing the Database

Here we will denormalize this model to improve performance. Current design is:

First, we could Denormalize to Maintain History

For example your company requires your system to maintain a customer’s name from the time that you started a project for a customer. A customer can later change the name. How would you achieve this requirement?

Answer

In the Customers table, you maintain the current name only. Therefore, you have to add the customer name to the Projects table.

In addition, it is always a good practice to have the information about the date when the name was valid. Therefore, you should add the start date in the Projects table as well. Note that you would probably already have the start date of a project in a real-life design. Your improved design should look similar to the following. (The denormalized attributes are StartDate and CustomerName.)

Second, we could denormalize for Performance

You have performance problems with a report that calculates total time spent on a project. Because you are tracking multiple projects, you run this report multiple times a day. How would you improve report performance?

Answer

The problem is that you have to aggregate project detail rows, in which you have the information about time spent or each activity on a project. You have to add a column to hold the total time spent in the Projects table.

Your improved design should look similar to the following. (Denormalized attribute that you should add in this exercise is TotalTimeSpent.)

Hope this helpled.

More on database denormalization:

http://www.siue.edu/~dbock/cmis564/denormal.htm

Share on FacebookShare on Google+Email this to someoneShare on RedditShare on LinkedInShare on TumblrTweet about this on TwitterShare on StumbleUpon

Leave a Reply

Your email address will not be published.