171
Chapter 7: Relational Database Design and Creating the Physical Database Schema
7
This approach lends itself directly to scalability and fl exibility. If all the objects for a par-
ticular database were all contained within one database and the need to move a specifi c set
of objects to a different server arose, how would this be handled? If the related tables were
stored in individual databases, moving the tables would require only backing up and restor-
ing the database to the new server.In addition to scalability, different databases enable you to secure objects based on their
location. Security can be aligned directly with the specifi c databases. For example, if there
were a group of individuals that needed permission to Human Resource information and not
sales information, you can quickly specify security in that database holistically instead of
identifying each individual table and granting permissions for each object.When to Use Which Schema
Separating tables into different schemas is similar to locating the objects in separate data-
bases. From the DBA’s perspective, managing security is simpler. Security can be applied at
a schema level as opposed to an object level, and the objects will be grouped according to
the schema they are assigned.Further, if you need to move the tables to another database, you can quickly identify and
move the tables based on the schema. However, this requires a little more effort than the
aforementioned approach to store in separate databases. You need to either back up the
individual tables and move them or script the tables and then copy the data from one loca-
tion to the next.Summary
Smart database design, covered in Chapter 2, “Data Architecture,” showed why the data-
base physical schema is critical to the database’s performance. This chapter looked at the
theory behind the logical correctness of the database design and the many patterns used to
assemble a database schema.■ The three phases in database design are the conceptual (diagramming) phase, the
SQL DDL (create table) phase, and the physical layer (partition and fi le location)
phase. Databases designed with only the conceptual phase perform poorly.■ (^) Normalization can be summed up as the three “Rules of One”: one group of
items = one table; one item = one row; and one fact = one column.
■ (^) Generalization is the buffer against normalization over-complexity.
c07.indd 171c07.indd 171 7/30/2012 4:18:18 PM7/30/2012 4:18:18 PM
http://www.it-ebooks.info