Which database design is best for this?

[skubasteve]

Hi everyone,

I'm having trouble figuring out which database design would be best (and less CPU heavy) for a project I'm working on.

Let's say I'm tracking stock prices each day for several thousand different stocks. I'm tracking the date, start price, end price, high price, and low price for each stock.

I'm eventually going to have a PHP page that let's me choose specific dates and displays the averages details for each stock for the dates selected.

So here's my issue. Would it be best for me to create a new table for each date, and on the PHP page go through all the tables to find the avearages for the list of stocks.

or

Would it be best for me to have one table with all the data on it?

I know initially it would be best for me to have one table with all the data on it, but if i had over a years worth of data on the table and wanted to search that table for a specific date range...wouldn't it be CPU heavy having to go through hundreds of thousands of records to find the specific data I queried?

[Dr John]

I'd guess that putting an index on the date column would get round any problems, and MySQL is said to be able to work with millions of rows with no problems. So one table is better than continually creating new tables, and then having to create new queries to access the new table.

[DaveMaxwell]

Like Dr. John says, if the table is indexed properly, then tables of that size shouldn't be a problem. Creating multiple tables would just be a mistake.

You'd want a table for the basic stock info (stock name, stock type, etc) and a table for the various markets (NYSE, NASDAQ, Tokyo, etc), then a table which marries the three together

StockMarketValues
StockID - FK to the StockInfo table I described above
MarketID - FK to the Market table described above
MarketDate - you could have a more granular if you wanted to track hourly or something
OpeningPrice
ClosingPrice

But that's off the top of my head. I'm sure there's more details needed...

[felgall]

Small table sizes like that should certainly be no problem if properly indexed. It is only once you start to get into billions of records that you might need to start rethinking the design. A few million records is next to nothing for a database.

[skubasteve]

That's what I was thinking, but just wanted to make sure. Thanks for your help.