Database design considerations for a financial system

Louie Bao
 Date
over Datetime
• Most financial data such as prices, portfolio
holdings, etc are date based and not concerned
with the time component.
• The separate Date data type introduced since
SQL 2008 significantly reduces date comparison
and calculation mistakes.
 Decimal
over Float
• Decimal is a precise numerical type whereas
Float is an approximate numerical type.
 Decimal
over Money
• Money data type suffers from integer division.
 Read
Committed or RCSI over NOLOCK
NOLOCK is bad because:
• Uncommitted rows might be returned.
• Same record might be accounted twice.
• Previously committed rows might be missed.
• Query might fail with an error: Could not
continue scan with NOLOCK due to data
 HashBytes
over Checksum
• When an MD5 hash algorithm is specified, the
probability of HashBytes returning the same
result for two different inputs is much lower than
that of CHECKSUM.
 Pass
as many IDs as far downstream as
• FileId
• AccountId
• OrderId
• TradeId
• SecurityId
• etc
 Process Event Log
• Info
• Warning
• Error
 Process Progress Log
• Started
• Running
• Completed
 Process Exception Log
• Invalid/unknown data
 Audit
 History Tables
Keep track of who changed what when how.
 Group
permission over User permission
• Add and remove users with ease.
 Align
permissions with business
• Testers should not have write access in prod.
Throw Your MONEY Away
Previously committed rows might be missed if NOLOCK hint is used
Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

similar documents