CODEWITHSUNDEEP
sqlsql-serverdatabase-design
Ryan
Ryan

Reputation: 4414

Enforce rules in a stock transaction table

I'm creating a fake stock market type database and the Transaction table needs some sort of logic to enforce certain rules. Here's the Transaction table I'm using:

Id  AccountId   Action  Qty Price   Amount
1   1           SELL    30  $1.00   $30.00
2   2           BUY     30  $1.00   -$30.00
3   1           SELL    20  $2.00   $40.00
4   3           BUY     20  $2.00   -$40.00
5   3           DEPOSIT            $100.00

As you can see here BUY/SELL Actions must have a Qty and Price and an Amount that should be calculated. The DEPOSIT action doesn't require Qty or Price because thats just a user putting money into an Account table

I was thinking of using some sort of trigger to do this. Is there a better practice?

Upvotes: 1

Views: 650

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

Tested in SQL Server 2012. (Stock symbols are omitted.)

create table stock_transactions (
  trans_id integer primary key,
  trans_ts datetime not null default current_timestamp,
  account_id integer not null, -- references accounts, not shown

  -- char(1) keeps the table narrow, while avoiding a needless
  -- join on an integer. 
  -- (b)uy, (s)ell, (d)eposit
  action char(1) not null check (action in ('b', 's', 'd')),

  qty integer not null check (qty > 0),

  -- If your platform offers a special data type for money, you
  -- should probably use it. 
  price money not null check (price > cast(0.00 as money)),

  -- Assumes it's not practical to calculate amounts on the fly
  -- for many millions of rows. If you store it, use a constraint
  -- to make sure it's right. But you're better off starting
  -- with a view that does the calculation. If that doesn't perform
  -- well, try an indexed view, or (as I did below) add the 
  -- "trans_amount" column and check constraint, and fix up
  -- the view. (Which might mean just including the new "trans_amount"
  -- column, or might mean dropping the view altogether.)
  trans_amount money not null,

  -- Only (b)uys always result in a negative amount.
  check ( 
    trans_amount = (case when action = 'b' then qty * price * (-1)
                         else                   qty * price
                    end )
  ),

  -- (d)eposits always have a quantity of 1. Simple, makes logical 
  -- sense, avoids NULL and avoids additional tables.
  check ( 
    qty = (case when action = 'd' then 1 end)
  )
);

insert into stock_transactions values
(1, current_timestamp, 1, 's', 30,   1.00,   30.00),
(2, current_timestamp, 2, 'b', 30,   1.00,  -30.00),
(3, current_timestamp, 1, 's', 20,   2.00,   40.00),
(4, current_timestamp, 3, 'b', 20,   2.00,  -40.00),
(5, current_timestamp, 3, 'd',  1, 100.00,  100.00);

But look at what happened. Now that we've added deposits as a type of transaction, this is no longer a table of stock transactions. Now it's more like a table of account transactions.

You'll need more than a CHECK constraint to make sure that an account has enough in it to buy whatever the account holder wants to buy.

In SQL Server, decisions about clustered indexes are important. Give that some thought and testing. I'd expect you to query often on account id numbers.

Upvotes: 1

Related Questions