SQL Server: Tuning Database Design
- Logical and Physical Models
- Database Application Types
- Using Efficient Datatypes
- Partitioning Large Tables
- Bigger, Better, Faster
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Introduction
The first article of this series provided an overview of what's involved in performance tuning. This article covers logical and physical database design issues. I'll talk about the differences between the logical and physical data models as well as various database application types, and give you some valuable tuning advice.
Logical and Physical Models
Most developers are familiar with relational modeling concepts, and most consider themselves experts in modeling. Unfortunately, their perception of what's involved in modeling is not always realistic. Most IT folks get the concept of a table: the collection of rows and columns where you store data. What columns you put in each table and how you relate tables to each other is what makes up your model. But just because you can stick data in a table, that doesn't necessarily make you a grand modeler. In fact, many developers (and database administrators) can't even tell the difference between the logical and physical data models.
The logical data model should be constructed after you've identified all the business entities involved in the scope of your application. Presenting the entity relationship diagram(s) to the business owners is a useful exercise that helps you validate your business knowledge. In a nutshell, the logical data model shows how the data about business entities should reside in the database. The logical data model doesn't take into consideration the specifics of the relational database management system (RDBMS) in use; to construct a logical data model, I don't need to be an Oracle, Informix, or SQL Server guru. I just need to understand the business, know the type of application I'm modeling, and be well versed in relational theory.
The physical data model should be created after the logical model has been reviewed and discussed thoroughly with business owners and analysts. The physical model includes the primary and foreign keys, triggers, other ways of enforcing referential integrity, datatypes, indexes, database files and filegroups, and more. The physical data model is specific to the RDBMS used for a particular project; therefore, it's best to ask the SQL Server expert to design the physical data model for the database that will be implemented with SQL Server.