24
Part I: Laying the Foundations
FIGURE 2-2
Smart Database Design is the premise that an elegant physical schema makes the data intui-
tively obvious and enables writing great set-based queries that respond well to indexing.
This in turn creates short, tight transactions, which improves concurrency and scalability,
while reducing the aggregate workload of the database. This fl ow from layer to layer
becomes a methodology for designing and optimizing databases.SchemaSet-BasedIndexingConcurrencyAdv ScalabilityEnablesEnablesEnablesEnablesPhysical Schema
The base layer of Smart Database Design is the database’s physical schema. The physical
schema includes the database’s tables, columns, primary and foreign keys, and constraints.
Basically, the “physical” schema is what the server creates when you run Data Defi nition
Language (DDL) commands. Designing an elegant, high-performance physical schema typi-
cally involves a team effort and requires numerous design iterations and reviews.Well-designed physical schemas avoid over-complexity by generalizing similar types of
objects, thereby creating a schema with fewer entities. While designing the physical
schema, make the data obvious to the developer and easy to query. The prime consideration
when converting the logical database design into a physical schema is how much work is
required for a query to navigate the data structures while maintaining a correctly normal-
ized design. Not only is the schema then a joy to use, but it also makes it easier to code
against, reducing the chance of data integrity errors caused by faulty queries.Conversely, a poorly designed (either non-normalized or overly complex) physical schema
encourages developers to write iterative code, code that uses temporary buckets to manipu-
late data, or code that will be diffi cult to debug or maintain.c02.indd 24c02.indd 24 7/30/2012 4:07:52 PM7/30/2012 4:07:52 PM
http://www.it-ebooks.info