CODEWITHSUNDEEP
mysqldatabase
user636044
user636044

Reputation:

How do I structure MySQL to store stock information?

I want to store stock market data in MySQL. There are some 6,000 individual stocks to store. Each has the following data:

Symbol    Date          Open      High    Low    Close    Volume
ABCD      2012-09-22    50.00     55.25   48.73  51.23    34,002,212

with an entry for each trading day.

The way I see it, I can either create one table with every entry making for an enormous table or I can create 6,000 tables, one table for each stock. New entries will be added daily.

I will eventually want to query the database to give me the data of two or more symbols between two dates.

What is the best way to store this data in MySQL?

Upvotes: 2

Views: 2764

Answers (3)

SQLWizard
SQLWizard

Reputation: 11

As long as you can manage creation of 6000 tables dynamically, you can go for either approach depending upon what kind of queries you want to optimise.

Problem with 6000 tables is not about its SQL being ugly or preety. (I mean, do you really care?)

SQL to work on one table would be like 

Select <columnslist> FROM ScripDataTable
Where  scripcode = '<YourChoice>'
And ....

SQL to work on 6000 table would be like 

Select <columnslist> FROM (Select tableName FROM TableDictionary
Where  tableName Like '%<YourChoice>%')
Where ....

Problem with one-table-per-scrip will occur will be if you ever decide to add extra columns to table. I have maintained prices in excel for approx 5 scrips and recently decided to add lots of statistical published from exchange. With 5 scrips, it took me a day to add it manually.

Upvotes: 1

Science_Fiction
Science_Fiction

Reputation: 3433

Sounds like one table with the Symbol as a primary key. Which should give you a clustered index on the Symbol (speeding things up somewhat).

Having 6000 tables would be a bad idea I think. Your Select query would not be too pretty.

Upvotes: 1

John Lockwood
John Lockwood

Reputation: 3875

You definitely DON'T want 6000 tables. :) One table can certainly handle this. You might look at creating an index on Symbol and Date since that's what you'll be querying on. Hope that helps.

Upvotes: 0

Related Questions