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

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

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

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

Do you happen to have an example about how to model temporal data in RDBMS(mySql in pariticular)?

Thanks,
Andrew

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