If you are building a stock market portfolio database, you may want to create a table to store you current positions. You can do this by creating a simple table with the stock symbol, the quantity of stock and a transaction date. When new position is entered, you have purchased the stock. The ‘buy’ record can then be stored in a ‘history’ table using a trigger that is fired when a new record is added to the ‘positions’ table. When the stock is sold, the record is deleted. A trigger on the delete action adds a new record to the history table to indicate the stock was sold.

How to create a stock portfolio history table with MySQL? One method is as follows:

For example, see the screen shot below. A concise history of the transactions is produced.

Figure 1 stock portfolio position history table

Data definition in MySQL for the stock_portfolio_position_history table

The data definition in MySQL for the stock_portfolio_position_history table is described here:

delimiter $$

CREATE TABLE `test`.`stock_portfolio_position_history` (

`HistoryId` int(11) NOT NULL AUTO_INCREMENT,

`PositionId` int(11) NOT NULL,

`Symbol` varchar(10) NOT NULL,

`Quantity` int(11) NOT NULL,

`TransactionDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`Transaction` varchar(45) NOT NULL,

PRIMARY KEY (`HistoryId`),

UNIQUE KEY `HistoryId_UNIQUE` (`HistoryId`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$

How to set up the portfolio positions table?

First in MySQL create the stock positions table

delimiter $$

CREATE TABLE `stock_portfolio_positions` (

`PositionId` int(11) NOT NULL AUTO_INCREMENT,

`Symbol` varchar(10) NOT NULL,

`Quantity` int(11) NOT NULL,

`TransactionDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`PositionId`),

UNIQUE KEY `PositionId_UNIQUE` (`PositionId`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1$$

Triggers for stock_portfolio_positions table

Next, create the trigger that will enter the buy record in the history table.

CREATE TRIGGER `stock_portfolio_positions_AINS` AFTER INSERT ON stock_portfolio_positions FOR EACH ROW

-- Edit trigger body code below this line. Do not edit lines above this one

INSERT INTO `test`.`stock_portfolio_position_history`

(`PositionId`,

`Symbol`,

`Quantity`,

`TransactionDate`,

`Transaction`)

VALUES

(

new.PositionId,

new.Symbol,

new.Quantity,

CURRENT_TIMESTAMP,

'Buy'

)

Next create the MySQL trigger used to add a ‘sell’ record in the history table.

USE `test`;

DELIMITER $$

CREATE TRIGGER `stock_portfolio_positions_ADEL` AFTER DELETE ON stock_portfolio_positions FOR EACH ROW

-- Edit trigger body code below this line. Do not edit lines above this one

INSERT INTO `test`.`stock_portfolio_position_history`

(`PositionId`,

`Symbol`,

`Quantity`,

`TransactionDate`,

`Transaction`)

VALUES

(

old.PositionId,

old.Symbol,

old.Quantity,

CURRENT_TIMESTAMP,

'Sell'

)

Testing the stock market securities portfolio history feature

Now, you are ready to test. For example, for testing purposes, ‘buy’ 10 shares of IBM execute the following:

INSERT INTO `test`.`stock_portfolio_positions` (`Symbol`, `Quantity`) VALUES (‘IBM’, ’10’);

Note that because some of the fields have default values automatically generated, I do not need to specify the portfolioId value nor do I need to specify the transaction date.

Next for testing purposes, ‘sell’ the shares.

DELETE FROM `test`.`stock_portfolio_positions` WHERE `PositionId`=’6′;

The result should be something similar to figure 1.