170
Part II: Building Databases and Working with Data
As an example of a ternary relationship, consider a manufacturing process that involves an
operator, a machine, and a bill of materials. From one point of view, this could be an opera-
tion entity with three foreign keys. Alternatively, it could be thought of as a ternary rela-
tionship with additional attributes.Just like a two-entity many-to-many relationship, a ternary relationship requires a resolu-
tion entity in the physical schema design to resolve the many-to-many relationship into
multiple artifi cial one-to-many relationships; but in this case the resolution entity has
three or more foreign keys.In such a complex relationship, the fi fth normal form requires that each entity, if separated
from the ternary relationship, remains a proper entity without any loss of data.It’s commonly stated that a third normal form is enough. Boyce-Codd, fourth, and fi fth nor-
mal forms may be complex, but violating them can cause severe problems. It’s not a matter
of more entities versus fewer entities; it’s a matter of properly aligned attributes and keys.Strategy Considerations
When you have fi nalized all the design patterns that will be used throughout your data-
base, a pivotal decision about the physical location of each table (entity) must be made.
Following are three schools of thought:Store all tables in one database and one schema.Store subject-specifi c tables in separate databases.Store subject-specifi c tables in one database and separate schemas.Traditionally, most database designers using SQL Server have followed the pattern to store
each table in the same database under the dbo schema. However, this approach does limit the
scalability of the database. Deciding whether to store the tables in the same database with dif-
ferent schemas or in different databases affects the scalability and fl exibility of the database.When to Separate into Different Databases
If you anticipate the need to scale you database, you may need to consider storing related
tables in separate databases. For example, you may have tables specifi c to Human Resources
and another set of tables specifi c to Sales. In the Human Resources database you may store
Employee information. On the other hand, you may track orders and products in the Sales
database.c07.indd 170c07.indd 170 7/30/2012 4:18:18 PM7/30/2012 4:18:18 PM
http://www.it-ebooks.info