Transcript 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
movement.
 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
possible
• 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
Columns
 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
functions
• Testers should not have write access in prod.



Throw Your MONEY Away http://bradsruminations.blogspot.com.au/2009/11/throw-your-money-away.html
Previously committed rows might be missed if NOLOCK hint is used
http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolockhint-is-used.aspx
Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx