Chapter Seven. Database Design Theory

The goal of data independence has the direct implication that logical and physical database design are different disciplines: logical design is concerned with what the database looks like to the user, and physical design is concerned with how the logical design maps to physical storage. The primary focus of this chapter is on logical design, and I'll use the unqualified term design to mean logical design specifically, until further notice.

One point I want to stress right away is this. Recall that "the" relvar constraint for relvar R can be regarded as the system's approximation to the predicate for R; recall too that the predicate for R is the intended interpretation, or meaning, for R. It follows that constraints and predicates are highly relevant to the business of logical design! Indeed, logical design is, in essence, precisely a process of pinning down the predicates as carefully as possible and then mapping those predicates to relvars and constraints. Of course, those predicates are necessarily somewhat informal (they're what some people like to call "business rules"); by contrast, the relvar and constraint definitions are necessarily formal.

Incidentally, the foregoing state of affairs explains why I'm not much of a fan of entity/relationship (E/R) modeling and similar pictorial methodologies. The problem with E/R diagrams and similar pictures is that they're completely incapable of representing all but a few rather specialized constraints. Thus, although it might be OK to use such diagrams to explicate the overall design at a high level of abstraction, it's misleading, and in some respects quite dangerous, to think of such a diagram as actually being the design in its entirety. Au contraire: the design is the relvars, which the diagrams do show, together with the constraints, which they don't.

There's another general point I need to make up front. Recall from Chapter 4 that views are supposed to look and feel just like base relvars (I don't mean views defined as mere shorthands, I mean views that insulate the user from the "real" database in some way). In general, in fact, any given user interacts not with a database that contains only base relvars (the "real" database), but rather with what might be called a user database that contains some mixture of base relvars and views. Of course, that user database is supposed to look and feel like the real database to that user . . . and so it follows that all of the design principles to be discussed in this chapter apply equally well to such user databases, not just to the real database.

I feel compelled to make one further introductory remark. Several reviewers of earlier drafts of this chapter seemed to assume that what I was trying to do was teach elementary database design. But I wasn't. You're a database professional, so you're supposed to be familiar with design basics already. So the purpose of the chapter is not to explain the design process as it's actually carried out in practice; rather, the purpose is to reinforce certain aspects of design that you already know, by looking at them from a possibly unfamiliar perspective, and to explore certain other aspects that you might not already know. I don't plan to spend a lot of time covering what should be familiar territory. Thus, for example, I deliberately won't go into a lot of detail on second and third normal forms, because they're part of conventional design wisdom and shouldn't need any elaboration in a book of this nature (in any case, they're not all that important in themselves except as a stepping-stone to Boyce/Codd normal form, which I will discuss in this chapter).