Lesson 5 | Implementing logical database performance design |
Objective | Describe Characteristics of high-performing database |
Logical Database Design
Compare Database Design using Real-life example
To illustrate the implications that logical design has for performance, let's consider a third normal form database design for placing orders for products. Management wants to know the counts of "State flower" for all orders that have been placed for "sunflower seeds." The output would look something like this:
State_flower count(*)
------------ --------
Begonia 433,233
Pansy 121,455
Tulip 44,233
Zenia 3,245
The Slide Show below will allow you to compare how different database designs will result in different SQL statements.

1) Database Design 1

2) Database Design 2

3) Database Design 3

4) Database Design 4
❮
❯
The extreme in redundant database design is commonly used with data warehouse tables.
See the paragraph below for more information.
Data warehouse table design
In data warehouses, "wide" tables are created that contain a huge amount of redundant information. Because of the huge amount of data redundancy, these wide tables are very cumbersome to update, but
they can be queried with remarkable speed. In our example, all of the other tables could be combined into the QUANTITY table, creating a single, very wide table that contains all of the order, customer, salesperson, and item
information. From an Oracle perspective, a single-row fetch would access all of the line item information for each order, and complex queries would no longer require table joins.
Now that we have reviewed logical design, let's look at how physical design will impact performance.
Denormalizing Database - Exercise