Advanced MySQL Trigger Help

[Ryan Rigby]

I am working on setting up a mysql database to store all of my historical stock data. I want to organize it such that there is a table of contents table that contains the ticker, name, and date range of prices for each security in the database. I also want this table to have a trigger that creates a new table each time a new row is inserted. This table name would be based on a the primary key of the new row and would be used to store the price data for the security listed in the row.

My proplem is that mysql does not allow triggers to create tables. When I try to create a trigger such as this I get the following error.

Error 1422: Explicit or implicit commit is not allowed in stored function or trigger.

Does anyone know a workaround for this limitation?

 

[daleholborow]

While not directly answering your question, I would make the following two statements:

a) avoid triggers most of the time (wow... i know people are going to flame me for that), as people tend to use them to put business logic in their db layer of their application.
and
b) I seriously question your db design. Why store a date range of existing prices in the "ticker/name" table? And REALLY, _WHY_ create a new table for each new ticker?

instead, have a table -> columns as follows:

security ->ticker | name

and a table:

price data -> ticker | open | close | etc

where ticker in "price data" table is a foreign key to the security table.

(as an example)

 

[Ryan Rigby]

justification for multiple tables

I knew the idea of creating a new table for each security may upset a few people, but I do have a reason. If I were using daily data I would probably use a table like the one posted below. However, I am using relatively high frequency data (15 seconds). I am concerned that if I put 15 second price data for the entire S&P 500 into a single table it would seriously affect performance. One year of historical data would equate to about 195 million rows in a single table.

In light of this new knowledge, I would be interested to know which method you would choose. I am not a professional database programmer and am interested in learning best practices from those who know more than myself.

While not directly answering your question, I would make the following two statements:

a) avoid triggers most of the time (wow... i know people are going to flame me for that), as people tend to use them to put business logic in their db layer of their application.
and
b) I seriously question your db design. Why store a date range of existing prices in the "ticker/name" table? And REALLY, _WHY_ create a new table for each new ticker?

instead, have a table -> columns as follows:

security ->ticker | name

and a table:

price data -> ticker | open | close | etc

where ticker in "price data" table is a foreign key to the security table.

(as an example)

 

[daleholborow]

If you're going to get into zillions of records, such as historical tick data, then I believe you might be well advised to look into Kx. I've not used it myself, but I read about it (and am pretty sure you will find it discussed on this forum, and on wilmott.com) as being more suited to the task than regular SQL dbs.

Kx Systems - Fast database for real-time and historical data

Note that from the little I have investigated, using Kx is not at all like using SQL, so you'll have to learn something new.

But better a new square peg in a square hole eh?

 

[Ryan Rigby]

thanks for the kx recommendation

If you're going to get into zillions of records, such as historical tick data, then I believe you might be well advised to look into Kx. I've not used it myself, but I read about it (and am pretty sure you will find it discussed on this forum, and on wilmott.com) as being more suited to the task than regular SQL dbs.

Kx Systems - Fast database for real-time and historical data

Note that from the little I have investigated, using Kx is not at all like using SQL, so you'll have to learn something new.

But better a new square peg in a square hole eh?

Thanks for pointing out kdb as an option. I had never heard of it before. I downloaded the trial version so I could play around with it and see how hard the syntax is to pick up.

 

[alain]

KDB will open a MASSIVE hole in your pocket.

 

[const451]

Note that from the little I have investigated, using Kx is not at all like using SQL, so you'll have to learn something new.

But better a new square peg in a square hole eh?

kdb is super expensive, not something ppl normally use at home, I do not even know the price tag.

There is an open source alternative, search this forum.

 

[daleholborow]

Ah yeah, sorry, I thought that was the open source option, when i went hunting for the link I just remembered it started with a k and saw the "download here" option on their page

sorry for the confusion.