Analyze Logical 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:

The logical design of a database can have a significant impact on its performance, as it determines how data is organized and accessed within the system. Here are some ways in which the logical design of a database can impact performance:

1. Data normalization: Normalization is the process of organizing data in a way that minimizes redundancy and dependency. While normalization can improve data integrity and consistency, it can also result in more complex queries and slower performance, particularly when working with large datasets.
2. Indexing: Indexing is the process of creating data structures that allow for faster retrieval of data. Properly indexing tables and columns can significantly improve query performance, while improperly indexed tables or columns can result in slow performance and increased storage requirements.
3. Joins: Joins are used to combine data from multiple tables in a query. However, complex joins involving many tables can be resource-intensive and slow down query performance, particularly when working with large datasets.
4. Data types: The choice of data types for fields can impact performance, particularly when working with large datasets. For example, using a larger data type than necessary can result in slower query performance and increased storage requirements.
5. Query complexity: Complex queries involving multiple joins, subqueries, or aggregation functions can be resource-intensive and slow down query performance. Optimizing queries and breaking them down into smaller, simpler queries can improve performance.
6. Table Structure: The structure of tables, such as the number of columns, the size of the rows, and the use of NULL values, can impact performance. Keeping table structures lean and optimized can improve query performance and reduce storage requirements.

State_flower   count(*)
------------   --------
Begonia        433,233
Pansy          121,455
Tulip           44,233
Zenia            3,245

The following series of images below will allow you to compare how different database designs will result in different SQL statements.

select state_flower, count(*)
from
    state,
    city,
    customer,
    order,
    quantity,
    item
where
    item_name = 'Flower Seed'
    and item.item_nbr = quantity.item_nbr
    and quantity.order_nbr = order.order_nbr
    and order.cust_nbr = customer.cust_nbr
    and customer.cust_city = city.cust_city
    and city.state_name = state.state_name
group by state_flower;

"Extreme redundant database design" is commonly used with data warehouse tables. 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. The logical design of a database plays a critical role in determining its performance. By carefully considering 1) normalization, 2) indexing, 3) joins, 4) data types, 5) query complexity and 6) table structure, designers can create a database that is optimized for performance and able to handle large datasets with efficiency and speed.

Click the exercise link below to try your hand at redesigning a database to optimize performance.
Denormalizing Database - Exercise