The Physical Design.

The Physical Design

A physical design is a specification for database implementation. At this phase of development, you must know the database platform you're going to use--perhaps SQL Server 7.0 on Windows NT Server 4.0, Microsoft Access on the desktop, Oracle on the mainframe, or some other platform. To create a physical design, you pull together all the specifications and models you've created so far, then modify and optimize them for your target platform. For example, you need to modify all column properties, including data types, so that they're specific to your target environment. You can add extra columns that don't appear in the conceptual or logical models, such as flag columns and timestamp columns that facilitate data processing. You also need to size the database, analyze data volume and use, incorporate any replication or distribution plans, and select candidate columns for indexing. If you're thinking ahead, you'll also determine user roles (groups), logins, and security permissions; requirements for data retention (archiving plans); and failover and backup and recovery plans.

Creating a Physical Design

The physical design is a composite of models that, taken together, form a complete or near-complete specification for implementing a database. So what does a physical design look like? Part of it, the piece we ordinarily call the "physical model," looks like an expanded ERD, as Figure 1, page 8, shows. Other parts, such as the Data Volume Analysis model and the Security Matrix, also look familiar.

The physical model in Figure 1 is an expanded version of the conceptual model (or ERD) that was created. First carefully review the logical model, and then present it to colleagues, critics, and customers for feedback. When discrepancies between the conceptual and the logical models or if the logical model does not support the requirements, then adjustments to the...