IN THIS CHAPTER
Creating the database files
Creating primary and foreign keys
Creating the user data columns
Documenting the database schema
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, ...