Fitch & Mather Stocks: Database

Scott Stanfield
Vertigo Software, Inc.

July 1999

Summary: Describes the Fitch & Mather Stocks database design and its implementation in Microsoft® SQL Server™ 7.0. (26 printed pages)

Contents

Introduction
Creating the Data Model
Logical Database Schema
Physical Database Schema
Table and Index Details
Stored Procedures
Triggers and DRI
Conclusion
About the Author
For More Information

Introduction

This article describes the database design of the Fitch & Mather Stocks (FMStocks) database. We start with a description of the logical requirements, then discuss the actual implementation using SQL Server 7.0. Included in this document is a detailed explanation of the table schema, stored procedures, and triggers.

Please refer to "Fitch & Mather Stocks: Setup Documentation" for installation instructions.

Note   Portions of this document were taken from the Fitch & Mather Expense Report System articles that I wrote for MSDN from September 1998. For a list of these articles, see "Fitch & Mather Sample: Document List."

Creating the Data Model

MSDN's Steve Kirk described a method for building a data model from use cases. The following is a quote from his article describing database design for Duwamish Books, Phase 1, a sample application:

Statements of the business transactions to be modeled provide a starting point for the data model. The nouns in these statements can be thought of as data and the verbs as the processes that operate upon data. The nouns are developed into data entities with attributes that describe the entities and the relationships between entities. This suite of statements represents the primary business processes of the bookstore with the emphasized Nouns and Verbs forming the basic data model. Words used as NOUNS are in all caps and those used as VERBS are in italic caps. ("Designing a Simple Retail-Oriented Database")

We've adopted Kirk's use case methodology to describe, in English, the relationships between the different entities in the stock-trading scenario that we're modeling.

Think of each boldface sentence below as a statement of fact, or a simplification of how different entities relate to each other. From these statements, we should be able to derive a list of tables (or entities) and their relationships (one-to-many or many-to-many). We'll also capture business requirements at this stage. This knowledge will be used to translate the statements to a logical diagram, and then to a physical diagram or schema.

To make the test case examples realistic, we'll use explore a typical customer's interaction with the online stock system. This will enable us to test the database design against real-world scenarios.

Use Cases

A customer creates a new ACCOUNT.

Example: Alice, our first customer, opens a new account with FMStocks. As a new customer, she is assigned a unique Account Number (AccountID) and a special bonus of $10,000 cash to start her investments.

She'll use her unique e-mail address and a password to access her account.

An ACCOUNT has a position in one or more STOCKS.

Example: Alice owns stock in Microsoft (MSFT) and Starbucks (SBUX). She likes a latte while she programs.

There are times when an account is not associated with any stocks. For example, if a user liquidates his or her entire portfolio, the net cash will be credited to his or her account, less commission fees.

A STOCK can be owned by one or more ACCOUNTS.

Example: Alice, and 50 of her net-savvy friends, purchased YHOO when it went public.

It's fairly obvious that a stock can be owned by more than one account. This use case, coupled with the one above, establishes a many-to-many relationship, which will be resolved in the logical design.

All buy/sell TRANSACTIONS are logged.

Example: When Alice places an order to buy or sell a stock, the transaction must be recorded for record keeping.

Keeping a log of all transactions allows us to separate the request to buy or sell a stock from the actual execution of the order by a broker. The final state of the order will be logged in this file. 

From this use case, we can also infer that a transaction record applies to a single account record and a single stock ticker.

Customers want dynamic and historic STOCK data.

Example: Alice wants the ability to research different stocks based on some arbitrary criteria.

The FMStocks application rates stocks specifically against 8 criteria that evaluate potential small-cap growth investment opportunities. For this, we'll need a large amount of historical data, as well as current price data.

Logical Database Schema

Based on the different use cases and business requirements, we're able to come up with a simple Entity-Relationship (ER) diagram.

Figure 1. FMStocks logical database view

The nouns in capital letters above translate to entities in the ER diagram shown in Figure 1. The "crow's feet" indicate one end of a many-to-many relationship. For example, you could "read" the relationship between Accounts and Stocks as 1) a single account could own multiple stocks, and 2) a single stock can be owned by many different accounts.

In addition, an account's buy and sell transactions are stored in a table with the same name. Because each individual transaction request comes from a single account for a single stock, the account's relationships to the Accounts and Stocks table are one-to-many.

Physical Database Schema

Now that we have a rough idea what the database should look like, we start fleshing out the tables with attributes and primary keys. Along the way, any many-to-many attributes are substituted by a relationship table. This type of relationship shows up as the Positions and Transactions table.

The physical design of a database usually requires many round trips. The final schema, shown in Figure 2, was the result of many iterations, especially in the tuning phase. For example, we denormalized the Stocks table into 3 separate tables that reflected our different data sources and the way the data is accessed.

Figure 2. FMStocks physical database design

We created the schema using the graphical designer tool in Visual InterDev® 6.0. The final schema script, as described in "Fitch & Mather Stocks: Setup Documentation," generates the database for this project.

If you are familiar with the database design tools in Visual InterDev 6.0, you might notice that our relationship lines are a thatched color instead of solid. That's because we declared the relationship between each table in SQL script, but dropped the automatic enforcement. This is a feature of ANSI SQL 92 called Declarative Referential Integrity (DRI). In the section "Triggers and DRI," you'll read about how we use triggers in situations where DRI won't suffice.

Table and Index Details

Figure 2 shows the table names and fields, but not the field attributes and data types. This section describes in detail the implementation and purpose of each table and their fields. The SQL create table statements are included to illustrate the foreign key relationships.

Accounts Table

Each customer who has an account with Fitch & Mather has a single entry in the Accounts table. Each record is uniquely identified through the AccountID, which is tagged as an Identity field (or column). All entity tables in FMStocks use an Identity column to generate the primary key. This is an important architecture issue that keeps the design clean and predictable.

We could have used email as the primary key, because it must be unique as well. However, AccountID only requires 4 bytes, and is easier to pass around to stored procedures. In addition, it keeps the design clean in case the requirement for unique e-mail addresses is lifted.

Figure 3. Column properties for the Accounts table

The actual SQL statement used to create the Accounts table is shown here:

create table Accounts 
(
    AccountID   int IDENTITY (5000, 1) NOT NULL 
                constraint PK_AccountID PRIMARY KEY,
    Password    varchar (50) NOT NULL,
    FirstName   varchar (30) NOT NULL,
    LastName    varchar (30) NOT NULL,
    Balance     money NOT NULL default(10000),
    DateOpened  datetime NOT NULL default(getdate()),
    Email       varchar (50) NOT NULL 
                constraint IX_Email UNIQUE,
    Closed      bit NOT NULL default(0) 
)

The Accounts table is initially populated with 10,000 users when you follow the instructions in "Fitch & Mather Stocks: Setup Documentation."

The Accounts table delete trigger is discussed later in the section "Triggers and DRI" section.

Stocks Table

The Stocks table holds the ticker symbols and company names for the 7,740 actively traded stocks on the NYSE, NASDAQ, and AMEX exchanges in December 1998. Approximately 240 symbols are missing because we didn't have fundamental data for them.

Figure 4. Column properties for the Stocks table

create table dbo.stocks 
(
    ticker    char (6) NOT NULL 
              constraint PK_StockSymbols_Ticker PRIMARY KEY,
    company   varchar (60) NOT NULL,
    exchange  char (4) NOT NULL 
)

By default, the PRIMARY KEY constraint will build a unique, clustered index on the ticker column. No two companies share the same symbol at any given time. Over time, however, symbols change hands: after Chrysler's merger with Daimler-Benz, Citigroup recycled their defunct symbol.

CurrentPrices Table

This simple table holds the last traded price for each stock ticker. In reality, this information would come from a dynamic data-feed, because it is updated frequently throughout a trading day.

Figure 5. Column properties for the CurrentPrices table

The actual SQL statement used to create the CurrentPrices table is shown here:

create table dbo.CurrentPrices 
(
    ticker  char(6) NOT NULL
            constraint PK_CurrentPrices_Ticker PRIMARY KEY
            references stocks(ticker),
    last    money NOT NULL
)

Fundamentals Table

Fundamentals holds quarterly and yearly performance results compiled from a companies SEC 10-K and 10-Q reports. The TickerDetails.asp page uses this data to determine whether or not a company meets the requirements for a particular kind of investment, a small-cap growth stock. We have 8 special requirements that make up a screening process for finding a potential investment.

For more information on how we calculate and determine the stock screens, read about the Motley Fool's Foolish Eight stock screens at www.foolmart.com/market/product.asp?pfid=MF+5003.

We choose this particular feature area because it would allow us to have some fairly rich queries and middle-tier processing for the performance benchmarks.

Figure 6. Column properties for the Fundamentals table

CREATE TABLE dbo.Fundamentals 
(
    Ticker            char (6) NOT NULL
                      constraint PK_Fundamentals_Ticker PRIMARY KEY
                      references stocks(ticker),
    MarketCap         money NULL,
    Sales             money NULL,
    Price             money NULL,
    DailyDollarVol    money NULL,
    SalesGrowth       float NULL,
    EPSGrowth         float NULL,
    NetProfitMargin   float NULL,
    InsiderShares     float NULL,
    CashFlowPerShare  money NULL,
    PE                money NULL,
    EPS_TTM           money NULL,
    Date_Q1           datetime NULL,
    EPS_Q1            money NULL,
    Date_FY1          datetime NULL,
    EPS_FY1           money NULL,
    EPS_FY2           money NULL,
    Status            int NOT NULL default(0) 
)

Positions Table

A customer's portfolio is made up of various stock purchases. The number of shares owned in each stock is tracked in the Positions table. Each entry in the positions table is a record of a particular purchase made at a given price and commission.

Figure 7. Column properties for Positions table

CREATE TABLE dbo.Positions 
(
    PositionID     int IDENTITY (10000, 1) NOT NULL 
                   constraint PK_PositionsID PRIMARY KEY NONCLUSTERED,
    AccountID      int NOT NULL 
                   references Accounts(AccountID),
    Ticker         char (6) NOT NULL 
                   references stocks(Ticker),
    Shares         int NOT NULL,
    Price          money NOT NULL,
    Commission     money NOT NULL default(0),
    PurchaseDate   datetime NOT NULL default(getdate())
)

create clustered index IX_AccountID_Ticker on dbo.Positions(AccountID,    
    Ticker)

TransactionTypes Table

At any given time, transactions can be in one of six possible states, as shown in Figure 8. These state numbers are stored in the table TransactionTypes.

When a customer wants to buy a stock, the request is logged in the Transaction table with a TransactionTypeID of 1, to indicate a pending buy. When the order is actually executed by the broker, there are two possible outcomes: the stock was bought (3), or there were insufficient funds (5).

Similarly, for sell orders, the request starts out with a TransactionTypeID of 2, then changes to either 4, to indicate the stock was sold, or 6, to indicate the customer tried to sell more shares than he or she owns.

Figure 8. Transaction types state diagram

Figure 9. Column properties for TransactionTypes table

CREATE TABLE dbo.TransactionTypes 
(
    TransactionTypeID tinyint NOT NULL 
                      constraint PK_TxTypes PRIMARY KEY,
    Description       char (20) NOT NULL 
)

Transactions Table

One of the business requirements for FMStocks is the ability to track every buy and sell transaction. The TransactionTypes table holds the different states that a transaction can take.

This table can also be used to queue purchase and sell requests for execution at a later time.

Requests to buy a stock start here. When the request is processed successfully, a new Positions record is created and the TransactionTypeID changes to 3 and DateExecuted is filled in.

Requests to sell a stock also start with a new record here. During processing, the middle-tier logic decrements the number of shares owned in the Positions table (or deletes the record all together). The Transactions table serves as a permanent log.

Figure 10. Column properties for Transactions table

CREATE TABLE dbo.Transactions 
(
    TransactionID     int IDENTITY (1, 1) NOT NULL 
                      constraint PK_TxID PRIMARY KEY NONCLUSTERED,
    AccountID         int NOT NULL 
                      references Accounts(AccountID),
    Ticker            char (6) NOT NULL 
                      references stocks(ticker),
    Shares            char (10) NOT NULL,
    TransactionTypeID tinyint NOT NULL 
                      references TransactionTypes(TransactionTypeID),
    DateRecorded      datetime NOT NULL default(getdate()),
    DateExecuted      datetime NULL,
    PositionID        int NULL 
)

create clustered index IX_AccountID_Ticker on dbo.Transactions(AccountID, 
    Ticker)

Stored Procedures

For the uninitiated, SQL Server stored procedures wrap SQL statements into functions that support parameters and return values. They're convenient for several reasons:

Some of the stored procedures follow a simple design pattern and naming convention. Any statement that inserts, deletes, or updates a table takes the form of TableName_{Add | Update | Delete} (for example, Account_Add). Stored procedures that return a single record based on a primary key are formed like TableName_GetBy{PrimaryKey | AlternateKey} (for example, Tx_GetByID). Finally, stored procedures that return more than one record from a select statement take the form TableName_ListBy{…} (for example, Position_ListForSale and Ticker_ListByCompany).

Not all stored procedures follow this naming convention. Some stored procedures abstract a higher-level functionality, like Broker_Sell. It works with several different tables to execute a sell order.

Because stored procedure parameters can be passed by reference, they're capable of returning values. In the parameter declaration, the Output keyword allows the stored procedure to pass values back to the caller.

We use this feature heavily for stored procedures that return a single value. You'll find that we use a generically named output parameter called @retval for some of our stored procedures, allowing us to write generic client code in Visual Basic that looks for that named parameter.

Account_Add

There's more to this simple stored procedure than meets the eye. Recall that the Accounts table primary key is AccountID. Now examine the insert statement and you'll see that we're not providing this value. That's because the IDENTITY attribute on the AccountID column will automatically generate the next unique key for us to use.

Now the only problem is figuring out what that next value was, so we can return it to the caller. SQL Server extends ANSI SQL with the special variable @@IDENTITY. It holds the value of the new identity value. Oracle 8 has a similar feature called a Sequence, which is more flexible but harder to use in this scenario.

CREATE PROCEDURE Account_Add
(
    @FirstName varchar(30),
    @LastName varchar(30),
    @Password varchar(15),
    @EMail varchar(50),
    @Balance money,
    @retval int output        -- AccountID
) 
As
insert into Accounts (FirstName, LastName, Password, Balance, email)
values (@FirstName, @LastName, @Password, @Balance, @email)
    
select @retval = @@IDENTITY

Account_Summary

Given an account number, Account_Summary calculates the portfolio's market value, the total cash invested in the portfolio, and the remaining cash in the account.

Because stock prices are stored in pennies, we divide them by 100 to convert to dollars. 

CREATE PROCEDURE Account_Summary
(
    @AccountID integer
)
As
declare @bal money
select @bal = balance 
from accounts
where AccountID = @AccountID

select sum(p.last / 100.0 * shares) as MarketValue,
       sum((price/100.0 * shares) + commission) as TotalInvestment,
       @bal as CashBalance        
from positions t, CurrentPrices as p, stocks as ss
where t.AccountID = @AccountID
and t.ticker = p.ticker
and t.ticker = ss.ticker

Account_VerifyLogin

Given a valid email and Password combination, the AccountID and FullName parameters will be filled in with the corresponding account data. If the password doesn't match, or if the e-mail account doesn't exist, the select statement will return no rows and the stored procedure returns 0.

CREATE PROCEDURE Account_VerifyLogin
(
    @EMail varchar(50),
    @Password varchar(50),
    @AccountID int output,
    @FullName varchar(100) output
)
As
select @AccountID = AccountID, 
       @FullName = FirstName + ' ' + LastName
from accounts
where email = @EMail
and Password = @Password

return @@ROWCOUNT    -- 0 means not found, 1 means it matched

Broker_Buy

Broker_Buy turns a "buy pending" transaction into a "bought" order by adding a new stock to the account's portfolio (tracked by the positions table), and changing the transaction ID state to 3 (bought). The new position ID and execution date is recorded in the transaction tables.

This stored procedure is a bit different from Broker_Sell because it doesn't directly decrement the account balance to purchase the stock. It relies on a trigger attached to the Positions table to do that work. Broker_Sell credits the account balance directly.

CREATE PROCEDURE Broker_Buy
(
    @TxID integer,
    @AccountID integer,
    @Ticker char(12),
    @Shares integer,
    @Price money,
    @Commission money,
    @retval integer output        -- PositionID
)
As
insert into Positions (AccountID, Ticker, Shares, Price, Commission)
values (@AccountID, @Ticker, @Shares, @Price, @Commission)
select @retval = @@IDENTITY
        
-- update the corresponding record in Transactions
update transactions
set TransactionTypeID = 3, DateExecuted = getdate(), PositionID = @retval        
where TransactionID = @TxID

Broker_Sell

Broker_Sell is a bit more complicated than Broker_Buy. It first ensures that the account in question has enough shares in its portfolio to sell. If so, it calculates the net gain from the sell, and credits the account balance. Otherwise, it raises an error and rolls back any changes made so far.

If the sell is successful, the transaction is marked as "sold."

CREATE PROCEDURE Broker_Sell
(
    @TxID integer,
    @Price money,
    @Commission money,
    @retval int output     -- 1 if sell was successful, 0 otherwise
)
As
select @retval = 0         -- pessimistic default to failure

declare @AccountID integer
declare @PositionID integer
declare @Ticker char(12)
declare @Shares integer
declare @TotalSharesOwned integer

-- Get the transaction details
select @AccountID = AccountID, @Ticker = Ticker, @Shares = Shares
from Transactions where TransactionID = @TxID

-- See if we have enough shares to sell
select @TotalSharesOwned = sum(Shares)
from Positions
where Ticker = @Ticker
and AccountID = @AccountID

if @Shares > @TotalSharesOwned
begin
    raiserror ('Request to sell too many shares aborted.', 16, 1)
    rollback tran
    return
end

-- Calculate the gain
-- Positions uses pennies/Accounts uses Dollars
update accounts
set Balance = Balance + (@Shares * @Price / 100.0) - @Commission
where AccountID = @AccountID
       
-- Update the corresponding record in Transactions
update transactions
set TransactionTypeID = 4, DateExecuted = getdate()        
where TransactionID = @TxID

-- It's the caller's responsibility to update the appropriate position 
-- entries
select @retval = 1

Positions_ListForAdjustment

This stored procedure was added during the development of the sell business logic. We needed a way to return all the different positions an account owns in a single company. An example is the best way to illustrate why we need this simplistic query.

If a customer bought Microsoft stock (MSFT) three different times, 10 shares each time, they would have 3 entries in the positions table. This query would return three records.

During the middle-tier processing, the Visual Basic code walks through each record and adjusts the number of shares remaining, or simply deletes an entire record. So, if the customer is trying to sell 16 shares, one of the records is deleted (leaving 20 shares), and another record's shares are decremented from 10 to 4. This results in a total of 14 shares remaining after the sell.

CREATE PROCEDURE Position_ListForAdjustment
(
    @AccountID integer,
    @ticker varchar(15)
)
As
select PositionID, AccountID, Ticker, Shares
from positions
where AccountID = @AccountIDS
and ticker = @ticker

Position_ListForSale

Position_ListForSale lists the stocks a customer currently owns. Clients will use this stored procedure to show the stocks available for sale.

CREATE PROCEDURE Position_ListForSale
(
    @AccountID integer
)
As
select upper(rtrim(positions.Ticker)) as ticker, 
       sum(shares) as shares, 
       avg(last) / 100.0 as price
from positions, CurrentPrices
where accountid = @AccountID
and positions.ticker = CurrentPrices.ticker
group by positions.ticker

Position_ListSummary

The goal of this complicated stored procedure is to return a list of useful information about a portfolio. This stored procedure is complicated because it calculates the average price paid for a particular stock. It handles the case where a stock was purchased several times, for different prices and potentially different commissions.

The first select statement creates a temporary table called #PositionsList. The pound-sign in front of the table name tells SQL Server that this is a temporary table and should be dropped when the stored procedure ends. The temporary table will be unique for this particular invocation, so multiple calls to Position_ListSummary won't collide over the table name.

A list of ticker symbols, the total number of shares and total commission is inserted into #PositionsList. Now we have a table suitable for manipulation by the second statement.

This query would have been easier to write if we knew that prices and commissions never changed.

CREATE PROCEDURE Position_ListSummary
(
    @AccountID integer    
)
As
set nocount on    
  
select upper(ticker) as Ticker, sum(shares) as TotalShares,
sum(commission) as TotalCommission
into #PositionsList
from Positions
where accountid = @AccountID
group by ticker        

select upper(t.ticker) as ticker, 
    company, 
    min(TotalShares) as SharesOwned,
    convert(money, (      (min(Last)/100.0)      )) as LastPrice,
    convert(money, (      (sum((shares / convert(real, ts.TotalShares)) *
            price) / 100.0) + (min(TotalCommission / TotalShares))    )) 
            as AvgPricePaid
from positions t, CurrentPrices as p, stocks as ss, #PositionsList ts
where t.AccountID = @AccountID
and t.ticker = p.ticker
and t.ticker = ss.ticker
and t.ticker = ts.ticker
group by t.ticker, company
return

Ticker_Fundamentals

This stored procedure returns a stock's fundamental data nicely formatted for display by an ASP page.

CREATE PROCEDURE Ticker_Fundamentals
(
    @Ticker varchar(12)
)
As
select status, fundamentals.ticker, company, exchange, 
    rtrim(convert(varchar(20), MarketCap, 1)) as MarketCap,          --$M
    rtrim(convert(varchar(20), Sales, 1)) as Sales,                  --$M
    rtrim(convert(varchar(20), Price, 1)) as Price,         --$ per share
    rtrim(convert(varchar(20),DailyDollarVol, 1)) as DailyDollarVol, --$M
    ltrim(str(SalesGrowth, 9, 1)) as SalesGrowth,                     --%
    ltrim(str(EPSGrowth, 9, 1)) as EPSGrowth,                         --%
    ltrim(str(NetProfitMargin, 9, 1)) as NetProfitMargin,             --%
    ltrim(str(InsiderShares, 9, 0)) as InsiderShares,                 --%
    ltrim(str(CashFlowPerShare, 9, 2)) as CashFlowPerShare, --$ per share
    ltrim(str(pe, 9, 1)) as PE                              --$ per share
from fundamentals, stocks
where fundamentals.ticker = @Ticker
and fundamentals.ticker = stocks.ticker

Ticker_GetPrice

Ticker_GetPrice returns the current price for a stock using the @retval parameter. 

CREATE PROCEDURE Ticker_GetPrice
(
    @Ticker char(12),
    @retval money output        -- Price
)
As
--Lookup current price for this ticker
select @retval = last
from CurrentPrices
where ticker = @ticker

Ticker_ListByCompany

This stored procedure returns all the symbols that start with @Company.

CREATE PROCEDURE Ticker_ListByCompany
(
    @Company varchar(30)
)
As
select rtrim(ticker) as ticker, rtrim(company) as company, 
    rtrim(exchange) as exchange
from stocks
where company like @Company + '%'

Ticker_ListByTicker

Ticker_ListByTicker returns all the symbols that start with @Ticker.

CREATE PROCEDURE Ticker_ListByTicker
(
    @Ticker varchar(12)
)
As
select rtrim(ticker) as ticker, rtrim(company) as company, 
    rtrim(exchange) as exchange
from stocks
where ticker like @Ticker + '%'

Ticker_VerifySymbol

The stock purchase page needed a simple way to verify if a symbol was valid or not. The first version of this stored procedure didn't use @retval—it just did a simple select. If the resulting recordset was empty, we knew the symbol didn't exist. However, in our quest for maximum speed, we reasoned that the overhead of creating a recordset to return what was, in essence, a simple Boolean was too great. Using @retval seemed to give us a slight performance boost in testing.

CREATE PROCEDURE Ticker_VerifySymbol
(
    @Ticker varchar(12),
    @retval int output
)
As
select @retval = count(ticker) 
from stocks
where ticker=@Ticker

Tx_GetByID

Used by the business code to return information about a transaction.

CREATE PROCEDURE Tx_GetByID 
(
    @TxID integer
)
As
select AccountID, Ticker, TransactionTypeID, shares
from transactions
where TransactionID = @TxID

Tx_AddBuyOrder

Adds a new buy order to the transaction and returns the new transaction ID.

CREATE PROCEDURE Tx_AddBuyOrder
(
    @AccountID int,
    @Ticker varchar(12),
    @Shares int,
    @retval int output    -- TxID
)
As
insert into Transactions (AccountID, Ticker, Shares, TransactionTypeID)
values (@AccountId, @Ticker, @Shares, 1)

select @retval = @@IDENTITY

Tx_AddSellOrder

Adds a new sell order to the transaction table and returns the new transaction ID.

CREATE PROCEDURE Tx_AddSellOrder
(
    @AccountID int,
    @Ticker varchar(12),
    @Shares int,
    @retval int output        --TxID
)
As
insert into Transactions (AccountID, Ticker, Shares, TransactionTypeID)
values (@AccountId, @Ticker, @Shares, 2)

select @retval = @@IDENTITY

Tx_SetTxType

Changes the TransactionTypeID for a given transaction record.

CREATE PROCEDURE Tx_SetTxType
(
    @TxID integer,
    @TxTypeID integer
)
As
update transactions
set TransactionTypeID = @TxTypeID, DateExecuted = getdate()
where TransactionID = @TxID

Triggers and DRI

Think of triggers as a special stored procedure attached to a table that are executed automatically when a new record is inserted into that table, deleted, or updated. Triggers are useful for controlling side effects.

For example, when an Account is deleted, we want its corresponding Positions and Transactions to be removed. The trigger tr_Accounts_d handles this:

CREATE Trigger tr_accounts_d
On dbo.Accounts
For Delete
As
    -- Cascade delete this accounts positions and transaction history
    delete Positions
    from Positions, deletedS
    where Positions.AccountID = deleted.AccountID

    delete Transactions
    from Transactions, deleted
    where Transactions.AccountID = deleted.AccountID

The Positions table trigger, tr_Positions_iu, is more complicated. It is fired whenever an insert or update command hits the Positions table (hence the suffix "iu"). It is responsible for debiting the Account.Balance when a new stock is purchased. If it detects that there are insufficient funds, it will abort or roll back the entire transaction. 

CREATE Trigger tr_positions_iu
On dbo.Positions
For Insert, Update 
As
   if update(Price)
   begin
      declare @Balance money
      declare @AccountID integer

      select @Balance = Balance, @AccountID = i.AccountID
      from inserted i, Accounts
      where i.AccountID = Accounts.AccountID

      declare @debit money
      select @debit = (shares * price / 100.0) + commission
      from inserted

      if @debit > @Balance
      begin
        raiserror ('Account has insufficient funds for this purchase',
            16, 1)
         rollback tran
         return
      end

      update accounts
      set Balance = Balance - @debit
      where AccountID = @AccountID
   end

If your ASP page and middle-tier components are transaction-aware, they in turn will participate in the rollback. FMStocks_Bus.Broker.BuyStock catches the rollback and returns a user-friendly message reminding the customer that they don't have enough money.

We wanted to defer the balance checking until the last possible moment because, theoretically, the price could be changing in real time. The request to purchase a stock, in our system, is limited to market orders. This means we don't know if the account has sufficient funds until the last possible moment.

For a detailed discussion on triggers and DRI, please refer to the Fitch & Mather Expense Report Demo, documented at http://www.fmcorp.com/.

Conclusion

There are two take-home messages for designing a database for performance. First, funnel all data access through stored procedures. Second, test and tune your indexes using the Query Analyzer if you encounter performance problems.

About the Author

Scott Stanfield is the president of Vertigo Software, Inc. Vertigo Software is a San Francisco Bay Area-based consulting firm that specializes in the design and development of Windows Distributed interNet Architecture (DNA) applications and components. He can be reached at scott@vertigosoftware.com or on the Web at http://www.vertigosoftware.com/.

For More Information