Beginning Database Design Solutions, 2nd Edition

9Using Common Design Patterns

The previous chapters described general techniques for building database designs. For example, Chapter 5, “Translating User Needs into Data Models,” explained how to build semantic object models and entity-relationship diagrams for a database, and how to convert those models into relational designs. Chapter 7, “Normalizing Data,” explained how to transform those designs to normalize the database.

This chapter takes a different approach. It focuses on data design scenarios and describes methods for building them in a relational model.

In this chapter, you will learn techniques for:

• Providing different kinds of associations between objects
• Storing data hierarchies and networks
• Handling time-related data
• Logging user actions

This chapter does not provide designs for specific situations such as order tracking or employee payroll. Appendix B, “Sample Relational Designs,” contains those sorts of examples.

This chapter focuses on a more detailed level to give you the techniques that you need to build the pieces that make up a design. You can use these techniques as the beginning of a database design toolbox that you can apply to your problems.

The following sections group these patterns into three broad categories: associations, temporal data, and logging and locking.

ASSOCIATIONS

Association patterns represent relationships among various data objects. For example, an association can represent the relationship between a rugby team and its opponents ...