thousands of db tables VS one huge table

Question

I am trying to develop an application that keeps track of daily stock data. (Each day a new record is created for every stock). There will be around 5000-10000 stock tracked. Then I need to analyze every day, month or other period some stock data, and keep it.

My question is this: Is it better to have an activity table for each stock that will keep the daily activity (each day a new row) or is it smarter to have one huge table that is inserted with 10,000 records everyday for all the stocks? Keep in mind that I need to do batch calculations every day for every stock (calculating moving averages and stuff).

Depends on what analysis you need doing after.. 1 table covers all — BugFinder, Sep 19 '12 at 12:37

score 6 · Accepted Answer · answered Sep 19 '12 at 12:33

6

One table. You might want to partition it by stock ID.

Automatic table creation is almost always a bad idea.

answered Sep 19 '12 at 12:33

Daniel Hilgarth

171,043
40
335
443

Depends on the database you are using. For the ease of calculations it is the best to have one table. But when you have the chance to make the calculations in parallel (with threads) then think about creating tables for each stock id. – Ali Avcı Sep 19 '12 at 12:40
Agreed, also I would consider separate tables for keeping aggregates, such as your_tab_day, _weak, _month, etc. Maybe some aggregates would be possible to count based on some lower level aggregates - for example month SUM, can be based on day SUM, year SUM based on month SUM, etc. – WojtusJ Sep 19 '12 at 12:57
In addition, if you have a change required to the underlying structure, updating a single table is much easier than finding 1000s. – Paddy Sep 19 '12 at 13:04

score 0 · Answer 2 · answered Sep 19 '12 at 12:46

0

Generally you can query single table faster in comparison to joins and multiple queries.

answered Sep 19 '12 at 12:46

RMN

754
9
25
45

thousands of db tables VS one huge table

2 Answers2