Database Design Methodologies for Microsoft SQL ServerBuck WoodySenior Microsoft SQL Server Specialist

SESSION CODE: DAT210

buck.woody@microsoft.com http://blogs.msdn.com/buckwoody

http://www.informit.com/guides/guide.aspx?g=sqlserver http://edge.technet.com/People/BuckWoody/

http://facebook.com/buckwoody http://twitter.com/buckwoody

Session Description and Objectives

Database design has the greatest impact on performance, reliability and security. Yet many IT professionals focus only on the physical model of the database. This session takes a simple set of requirements and decomposes the design process, using basic methodologies such as Entity Relationship Diagrams (ERD), Object-Role Modeling (ORM) and Unified Modeling Language (UML) to create an effective design. It covers the basic tools you can use for each of these approaches for SQL Server.

Objective #1 Learn the process for decomposing requirements to a logical design that can be communicated to Business and Development teams.

Objective #2 Understand the principals of Normalization and a simple process to Normalize.

Objective #3 Understand various tools available to design and implement a database.

Importance of Good Data Design

Impacts Performance, Security, Storage and Integrity

Design can be attributed to 50-75% of Project Issues

Clear Communications with Business and Developers

Who Designs (and Why)? May be a Single Person Or Group

Business Person or Business Analyst

Developer

DBA or Data Professional

• The Organization has strong talent in this area• Data viewed as a business asset• Data viewed as a “flow” pattern

• Mature development shop• No dedicated data staff• Formal ALM processes used

• Full-time Data Professional available• Data viewed holistically

Design Process

1. Create / Refine Business Requirements

2. Break out Nouns

3. Refine Nouns

4. Define Relationships

5. Define Datatypes

Normalization

A b C d 1 2 3 $ % # ^ :

Design Methodologies

• Object Role Modeling (Mostly Business Analysts)

• Unified Markup Language (Mostly Developers)

• Entity Relationship Diagram(Mostly Data Professionals)

Model Types

1.Conceptual• Platform Independent, Business Oriented, “Information Model”

2.Logical• Platform Independent, Flow and Developer Oriented

3.Physical• Platform Dependent, Performance, Sizing and Security

Tools

Note: Almost all modeling is not kept current

• Basic Shapes

• Visio / 3rd Party Tools

• Entity Framework / LINQ

References

Link List: tinyurl.com/ycoqlvu

© 2009 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

JUNE 7-10, 2010 | NEW ORLEANS, LA