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

The reason that this design is not a good idea from a performance perspective is that the combined ''For
Convenience'' model tables are going to be hotspots in the database. Every need to translate a type table
will need to go through these tables. This design is also not optimal when you need to scale out. In the
performance design, only the status type table is used for translation lookups of status types. This table
is not affected by requests for a different type table like the contract type. If you find later that you are
getting too many requests for the status type, we can scale out by moving this type table onto another
file resource. The ''For Convenience'' design will become the bottleneck for requests for type description
translations. If you are convinced that your user scale will only be single user based, this may never
be a problem. However, if your user scale is known to be corporate or infinite, starting with a known
bottleneck pattern like this is asking for trouble.

Single-UseColumns
Design columns with the same single-responsibility principle. Reject designs that attempt to reuse a
column in a model design. These designs result in complicated queries that can't be easily optimized.
Take for example a simple invoice model where two entity types can be invoiced. Figure 14-2 shows two
different approaches to resolving this modeling problem.

Multi-Use Column Single-Use Column

Invoice

Invoice

EntityType

PK Invoiceld PK Invoiceld

EntityTypeld

PK

FK1, FK2
FK3

Entityld
EntityTypeld

FK1 BillableEntityld

BillableEntity

BillableEntityld

Customer Carrier PK

Customerld Carrierld

PK PK

BillableEntityDesc

CustomerDesc CarrierDesc

Carrier Customer

Carrierld Customerld

PK PK

FK1 BillableEntityld FK1 BillableEntityld

Figure 14-2

The approach that violates the single-use column rule uses a general EntityId attribute in the invoice
model to represent either the Carrier or the Customer entity identifier. In order to be able to interpret the
EntityId field, an entity type table has to be added with two entries: Customer and Carrier. To provide a
listing of all the invoices with a description of the entity, the query would look like this:

SELECT INV.*, CAR.CARRIERDESC AS BILLABLEENTITYDESC
FROM INVOICE INV
INNER JOIN CARRIER CAR
ON CAR.CARRIERID = INV.ENTITYID
AND INV.ENTITYTYPEID = 100000
UNION