Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    toronto
    Posts
    4

    Database design question - How to store stock data?

    Hi there,

    I am designing a database to store stock data(like symbol, date, open, high, low, close and volume etc). I come up with two ideas here:

    1)One table with fields symbol, date, open, high, low, close and volume. symbo and date as primary key.

    It does not seem to be efficient to retrive data for a paticular stock considering if that table keeps 5000 stocks and more then two years data.

    2)Multiple tables
    A Stock Info table contains symbol, company name and description fields.

    and one table for each stock has fields date, open, high, low, close and volume. The table name like "CISCO_DATA", "IBM_DATA" etc.

    How can I find the foreigh key to relate to above mentioned table? Is it a valid design to have multiple table with the same structure?

    Can anyone shed any light on this? Any other way to design the database?

    Thanks in advance!

    Andrew

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Even the larger exchanges only generate a few million rows per year. There is no reason to segment data into separate tables (ie, IBM, CISCO, etc) on the basis of the number of rows.

    Without a lot more information about what kind of analysis you wanted to do, it is impossible to give you really good advice about how to store your data. My first suggestion would be to normallize as much as possible, then selectively denormalize only if there is some compelling reason to do so.

    Based on your present design, there would be a stock table (containing information about the security itself), a period table (defining the time periods of interest), and a trading detail table (with trading info like open, high, low, close, and volume) that had FK relationships to the stock and period tables.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Location
    toronto
    Posts
    4
    Thanks for the reply!

    What I want to do is that some scripts automatically update stock data in MySql for 5000 stocks every night and find the CandleStick patterns(use last 3-5 days' data) and Stochastics %K and %D(need last 10-14 days' data).

    I may just need one table for stock data(like open, high, low, close, volume) and use ticker and date as PK if performance is not a issue.

    I am really interested in the idea of creating a Period table. Can you elaborate more? What attributes it may have, date and any thing else?

    Andrew

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The concept of a period table comes from data warehousing. It allows you to enforce a degree of regularity on the temporal (date/time) values associted with your data. If this data isn't somehow constrained, it can sometimes be imposible to produce meaningful analysis of your data.

    Because the period table allows you to constrain the temporal values, you can manage them as you choose. You can create a single row for dates, hours, weeks, minutes, whatever you wish. You can even have different types of temporal data (for example, you can have daily and weekly periods if those are both important to your application).

    The purpose for a temporal table is to make it easier (possible?) to do temporal analysis of your data. Handle it in whatever way makes sense to you, but be sure to consider the ramifications of what you decide.

    There are several good books on datawarehousing that cover this in depth. I particularly like Steve Hoberman for this issue.

    -PatP

  5. #5
    Join Date
    Apr 2004
    Location
    toronto
    Posts
    4
    Do you happen to have an example about how to model temporal data in RDBMS(mySql in pariticular)?

    Thanks,
    Andrew

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Not off the top of my head. Most of the references I remember are from books like the one I cited in my previous posting.

    -PatP

Posting Permissions

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