Database modeling for stock prices

Question

I have recently been given the assignment of modelling a database fit to store stock prices for over 140 companies. The data will be collected every 15 min for 8.5 h each day from all these companies. The problem I'm facing right now is how to setup the database to achieve fast search/fetch given this data.

One solution would be to store everything in one table with the following columns:

| Company name | Price | Date | Etc... |

Or I could create a table for each company and just store the price and the date for when the data was collected (and other parameters not known atm).

What is your thought about these kind of solutions? I hope the problem was explained in sufficient detail, else please let me know.

Any other solution would be greatly appreciated!

Answer 1

I take it you're concerned about performance given the large number of records your likely to generate - 140 companies * 4 data points / hour * 8.5 hours * 250 trading days / year means you're looking at around 1.2 million data points per year.

Modern relational database systems can easily handle that number of records - subject to some important considerations - in a single table - I don't see an issue with storing 100 years of data points.

So, yes, your initial design is probably the best:

Company name | Price | Date | Etc... |

Create indexes on Company name and date; that will allow you to answer questions like:

• what was the highest share price for company x
• what was the share price for company x on date y
• on date y, what was the highest share price

To help prevent performance problems, I'd build a test database, and populate it with sample data (tools like dbMonster make this easy), and then build the queries you (think you) will run against the real system; use the tuning tools for your database system to optimize those queries and/or indices.