Databases Reference
In-Depth Information
If we have many reports in the OLTP application, it wouldn't matter if reports were from the
previous day's data. Against this limitation, which in many cases can be acceptable, the
performance gain in the report preparation is often significant.
Optimizing performance with schema
denormalization
In this recipe, we will see how schema denormalization can help improve database
performance, and what should be done before executing this operation.
Getting ready
We will implement a database schema representing a group of friends and their phone
numbers. The following are the requirements for the database:
F
For each friend, we want to store the name, surname, and gender
F
Each friend may have multiple phone numbers
F
For each phone number, we want to know its type of usage (home, work, mobile,
and so on)
F
A phone number can be shared by more than one friend, for example, Mrs. and Mr.
Smith will share the same home number—at least until they get divorced
F
For each phone number we want to store, we need to know its availability, that is,
working hours, evening, afternoon, weekend only, and so on
The following is the logic schema that we will implement to satisfy the requirements
mentioned earlier:
Friend
PK
i
d
Friend_Phone
firstname
lastname
PK,FK1
PK,FK2
f
friendid
phoneid
gender
Phone
PK i
d
F 2 phonekindid
phonenumber
K
FK1 availabilityid
Availability
PhoneKind
PK
i
d
PK
i
d
description
whenavailable
