To illustrate how logical database design impacts performance, consider a database for managing product orders. Management wants to know the counts of "State flower" for all orders of "sunflower seeds." The output would look like this:

The logical design of a database significantly affects its performance by determining how data is organized and accessed. Key factors influencing performance include:

1. Data Normalization: Normalization reduces redundancy and improves data integrity but can lead to complex queries and slower performance with large datasets.
2. Indexing: Proper indexing speeds up data retrieval, while poor indexing can slow queries and increase storage needs.
3. Joins: Combining data from multiple tables via joins can be resource-intensive, especially with many tables or large datasets.
4. Data Types: Choosing appropriate data types optimizes performance; oversized types can slow queries and increase storage.
5. Query Complexity: Complex queries with multiple joins or subqueries can degrade performance. Simplifying queries improves efficiency.
6. Table Structure: Lean table designs with minimal columns and optimized row sizes enhance query performance and reduce storage.

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

The following comparison illustrates how different database designs result in different SQL queries to achieve this output.

SELECT s.state_flower, COUNT(*)
FROM state s
JOIN city c ON c.state_name = s.state_name
JOIN customer cu ON cu.cust_city = c.cust_city
JOIN "order" o ON o.cust_nbr = cu.cust_nbr
JOIN quantity q ON q.order_nbr = o.order_nbr
JOIN item i ON i.item_nbr = q.item_nbr
WHERE i.item_name = 'Flower Seed'
GROUP BY s.state_flower;

In data warehouses, "wide" tables with significant redundancy are common to optimize query performance. For example, combining all data from CUSTOMER, ORDER, SALESPERSON, ITEM, and QUANTITY into a single QUANTITY table creates a wide table. While updates become cumbersome, queries are faster because no joins are needed. A single-row fetch retrieves all order details, simplifying complex queries.

Having explored logical design, the next step is to examine how physical design impacts performance. By optimizing normalization, indexing, joins, data types, query complexity, and table structure, designers can create databases that handle large datasets efficiently.

Try redesigning a database to optimize performance with the exercise below.
Denormalizing Database - Exercise