Microsoft SQL Server 2012 Bible

Data Design Patterns

Design is all about building something new by combining existing concepts or items using patterns. The same is true for database design. The building blocks are tables, rows, and columns, and the patterns are one-to-many, many-to-many, and others. This section explains these patterns.

When the entities — nouns and verbs — are organized, the next step is to determine the relationships among the objects. Each relationship connects two entities using their primary and foreign keys.

Clients or business analysts should describe the common relationships between the objects using terms such as includes, has, or contains. For example, a customer may place (has) many orders. An order may include (contains) many items. An item may be on many orders.

Based on these relationship descriptions, you can choose the best data design pattern.

One-to-Many Pattern

By far the most common relationship is a one-to-many relationship; this is the classic parent-child relationship. Several tuples (rows) in the secondary entity relate to a single tuple in the primary entity. The relationship is between the primary entity's primary key and the secondary entity's foreign key, as illustrated in the following examples:

• In the AdventureWorks2012 sample database, each productsubcategory may contain several products. Each product belongs to only one productsubcategory, so the relationship is modeled as one productsubcategory relating to multiple products. The relationship is made between the ...