Microsoft® SQL Server® 2008 Bible

Chapter 20. Creating the Physical Database Schema

IN THIS CHAPTER

• Creating the database files

• Creating tables

• Creating primary and foreign keys

• Configuring constraints

• Creating the user data columns

• Documenting the database schema

• Creating indexes

The longer I work with databases, the more I become convinced that the real magic is the physical schema design. No aspect of application development has more potential to derail an application or enable it to soar than the physical schema—not even indexing. (This idea is crucial to my view of Smart Database Design as expressed in Chapter 2, "Data Architecture".)

The primary features of the application are designed at the data schema level. If the data schema supports a feature, then the code will readily bring the feature to life; but if the feature is not designed in the tables, then the client application can jump through as many hoops as you can code and it will never work right.

The logical database schema, discussed in Chapter 3, "Relational Database Design," is a necessary design step to ensure that the business requirements are well understood. However, a logical design has never stored nor served up any data.

In contrast, the physical database schema is an actual data store that must meet the Information Architecture Principle's call to make information "readily available in a usable format for daily operations and analysis by individuals, groups, and processes." It's the physical design that meets the database objectives of usability, scalability, ...