Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    1

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •