05-29-09, 17:11 #1Registered User
- Join Date
- May 2009
Storing transactions of stock: 1 table or break into multiple tables?
Basically, I want to store transactions of each stock.
The structure of my Transaction table looks like the following:
Symol, TimeStamp, Price, Quantity
GOOG , 123456789, 0.5 , 10
GOOG , 123456788, 0.5 , 1000
IBM , 123457000, 1.5 , 100
IBM , 123457002, 1.5 , 500
The problem is I'm storing all transactions of more than 5000 stocks. A simple rough calculation shows that my table will grow to a huge number of rows.
Suppose that I'm storing all transactions for 5 years and there are on average 1000 transactions per stock per day. That would give me 9,125,000,000 rows(5000*5*365*1000). I'm afraid that when the time come to process this table(e.g with joins), I will seriously get a performance hit.
I'm planning to create a Transaction table for each stock(e.g GOOG_Transaction, IBM_Transaction).
Is this a good idea?
05-29-09, 20:04 #2Resident Curmudgeon
- Join Date
- Feb 2004
- In front of the computer
It is a VERY poor idea to fragment what is logically one table.
There will be a lot fewer rows than you estimate, since there are less than 255 trading days in any year on any exchange.
Even if your estimate was good, the number of rows isn't an issue. If you have enough disk to support a database that size then the number of rows isn't a problem.
Just an observation, but I don't know of anyone tracking exchange level transactions at the trade level (individual transactions) for more than 180 days... The number stop making sense at that level of detail pretty fast. Accountants care about that level of detail at the broker level, but investors don't care about that much detail at any level.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.