Database Design Strategy

A sound design strategy moves from business need to a stable, efficient schema in stages, the same design stages introduced in the previous lesson:

1. Requirements. Capture entities, relationships, business rules, data volume, and the queries the business actually needs answered.
2. Conceptual model. Build an ER model of entities, attributes, keys, and relationships, independent of any specific DBMS.
3. Logical model. Map the conceptual model to relations, normalize (typically to third normal form or BCNF) to remove redundancy and update anomalies, and define keys, constraints, and integrity rules.
4. Physical model. Choose the DBMS and add indexes, partitions, storage decisions, and datatypes, denormalizing only where an actual query or load demands it.
5. Validate and iterate. Check the design against real workloads, security requirements, backup needs, and expected growth. Tune after real use is observed, not before.

The goal throughout is the same: correct data, minimal redundancy, acceptable performance, and a schema that can change without breaking the applications built on top of it. As you move through the rest of this course, it will become clear how good design at each of these stages directly affects the robustness, speed, and efficiency of the finished database.

Each stage builds on the one before it, and skipping ahead tends to cost more time than it saves. Jumping straight to a physical model, choosing datatypes and indexes, before the requirements and conceptual stages are settled, usually means redoing that physical work once a missed business rule or an overlooked entity surfaces later. The Stories on CD, Inc. project from Module 1 walked through the requirements and conceptual stages in miniature: identifying that customers place orders, that orders contain CDs, and that CDs come from distributors, before a single column name or datatype was chosen. That ordering is not incidental; it is the strategy itself.

In designing a relational database, there are a number of objectives to keep in mind. Some the database designer controls directly; others depend, to varying degrees, on other factors in the database environment, the RDBMS itself, the network, and the hardware it runs on.

Three important database-design objectives that the designer controls directly are:

1. Ensuring that information is delivered in a consistent manner.
2. Eliminating data redundancy, duplicate data stored in more than one place.
3. Preserving data that still has value when removing data that does not, so that a cleanup or deletion operation aimed at genuinely obsolete records does not inadvertently destroy related data that is still needed, for example a historical order record that should survive even after the customer account tied to it is closed.

Each of these is squarely a design decision, not something the RDBMS imposes on its own. Consistent information delivery, for example, means every report pulling CustState from the Stories on CD database gets the same two-letter abbreviation format, because the design specified exactly one place that value lives and exactly one format it is stored in, not because any database engine enforces consistent formatting by default. Eliminating redundancy is the direct payoff of normalization, covered in the second course in this series; a design that stores a customer's city on both the CUSTOMER table and, redundantly, on every ORDER row, has already failed this objective regardless of which RDBMS eventually runs it. And preserving still-valuable data when deleting the unwanted kind is a question the designer answers through foreign key behavior, deciding, for instance, whether deleting a CUSTOMER record should cascade to delete that customer's ORDER history or should be blocked until the orders are handled some other way. Other important design objectives are either influenced or controlled by the RDBMS, the network operating system and network configuration, or the computer hardware. These include:

1. Maximizing the speed of database operations.
2. Enforcing data integrity^[1].
3. Achieving database security.

Maximizing speed, for example, certainly depends on database design, but it also depends on the RDBMS, the network configuration, and the computer hardware the database runs on. Data integrity works the same way from the opposite direction: the rules that define it are imposed early in the design process, but actually enforcing those rules at runtime relies on the capabilities of the RDBMS chosen later. Database security, similarly, is controlled by both the RDBMS and the surrounding network security, not by the schema design alone.

From this point on in the course, the term "database" refers to a relational database, unless otherwise indicated.

It is worth pausing on why this lesson bothers to separate these six objectives into two groups at all, rather than simply listing six things a good database has. The split is practical: it tells you where to focus your effort as a designer, and where effort would be wasted. Spending design time trying to guarantee fast query performance independent of any RDBMS or hardware choice is effort misapplied; speed is only partly yours to control, and the rest depends on decisions, DBMS selection, server hardware, network configuration, that belong to later stages of the database life cycle, not to the schema itself. Spending design time eliminating redundancy and ensuring consistent delivery, on the other hand, is effort that pays off regardless of which RDBMS eventually runs the design, since those two objectives live entirely in the structure you create.

This is also why data integrity sits in the second group despite being, in a real sense, decided during design. The rules exist because the designer wrote them into the schema, constraints, foreign keys, required fields, but whether those rules actually get enforced, consistently, every time, without a workaround, depends on the RDBMS respecting them. A perfectly designed integrity rule that the chosen database engine does not support is not actually enforced at all.

As introduced in the previous lesson, there are two ways to approach database design: focusing on the subject matter the database is concerned with, or focusing on the specific application in which the database will be created and used. Both approaches can, in principle, satisfy the three designer-controlled objectives covered above, but they tend to satisfy them differently: a subject-oriented design built around CUSTOMER, ORDER, and CD as stable business concepts naturally avoids redundancy across applications, while an application-oriented design has to work harder to achieve the same consistency once a second or third application needs the same underlying data. The next lesson looks more closely at the first of these: the subject approach.

A short recap of this lesson's strategy:

• Database design moves through five stages: requirements, conceptual model, logical model, physical model, and validation, each building on the one before it.
• Three design objectives belong to the designer alone: consistent information delivery, eliminating redundancy, and preserving still-valuable data during deletion.
• Three more objectives, speed, data integrity enforcement, and security, are shaped by design but ultimately depend on the RDBMS, network, and hardware the design runs on.
• Knowing which objectives are fully yours to control, and which are shared with the rest of the environment, is what tells you where design effort actually pays off.

[1] Data integrity: a term describing the quality, in terms of accuracy, consistency, and validity, of the data in a database, in the sense that the values required to enforce a data relationship actually exist. Problems with data integrity occur when a value in one table that is supposed to relate to a value in another cannot, because that second value has either been deleted or was never entered in the first place.