Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 509

INNER JOIN BILLABLEENTITY BI
ON INV.BILLABLEENTITYID = BI.BILLABLEENTITYID
LEFT OUTER JOIN CARRIER2 CAR
ON BI.BILLABLEENTITYID = CAR.BILLABLEENTITYID
LEFT OUTER JOIN CUSTOMER2 CUST
ON BI.BILLABLEENTITYID = CUST.BILLABLEENTITYID

Performance should not be the only driving factor for eliminating multi-use column designs. These
designs also make the model ambiguous. You can't glance at the invoice table and readily understand
that the EntityId represents both the primary key for the carrier and the customer entities. This breaks the
first principle of keeping things simple. In the next section, we'll look at how having too many nullable
columnsisanotherwaymodelsgetambiguous.

EliminatingAmbiguousNull Fields
Excessive use of nullable fields may be a dead giveaway for performance problems in an OLTP database.
You'll see designs like this when developers get too lazy to implement a relational design or too greedy
and want the database design to map back to an architecture that is easier for them to implement. One
example of this would be a table designed with many fields to record dates and times for an entity that
goes through many states. Update operations are the most expensive in an OLTP database structure.
With this type of design you are guaranteeing that this table will be updated for each stage encountered
that requires a new date. A relational approach would incur an INSERT operation instead. Figure 14-3
shows the two implementations.

Excessive Nulls and
Unused Space

Uses Only What is Needed
Efficient Use of Space

ContractDateTimeType

ContractDateTime_Flat

PK ContractDateTimeTypeld

PK ContractDateTimeld

ContractDateTimeTypeDesc

Contractld
StartDateTime
MatchedDateTime
ApprovedDateTime
PostedDateTime
PaidDateTime
ArchivedDateTime

ContractDateTime_Rel

PK ContractDateTimeld

Contractld
DateTimeValue
ContractDateTimeTypeld

FK1

Figure 14-3

Here's where you get into some clear tradeoffs by using these two implementations. For read operations,
the flat structure will make your job much easier. For write operations, you have to search the table
to find the row, and then lock the page to update the dates. Here is where you need the information
from the performance requirements of the application that uses this database to help you decide the best
implementation. Table 14-5 lists some of the advantages and disadvantages.