1

I want to store stock data from the FTSE 100 every 10 seconds but am confused how I should set up my tables.

I need to save the price, increase and % increase as well as some way of identifying which stock is which.

Should I have one massive table with 300+ columns. (3 for each stock)?

Separate tables per stock or something else?

I will be querying the data using PHP and displaying it on a website.

|improve this question
  • Try imagine the stock name(id or something) as one more column in your table, 4 columns and one row per each stock in each "batch". Add some column with a datetime or other means (batch_id) to identify when did that row arrive to you. You can then query by time range or by specific stock etc. – jkavalik Sep 15 '15 at 13:31
1

What I would do is to create a table with the stock_id (that can be the alphanumeric code or a integer), the timestamp of the measurement and the current value. That is your entry data, 3 columns.

From that point you can add columns for calculations (the difference absolute or percent) with the previous value. Having all in the same table will simplify the model and ease your queries. Try to create a date (not timestamp) column and create a partition by it. It may lighten a bit the access to the table as long as you set it in your queries.

|improve this answer
  • 1
    If there are proper indexes then the partitioning hardly has any positive effect on performance. It is mostly useful for fast removing of "old" data. mysql.rjweb.org/doc.php/partitionmaint – jkavalik Sep 16 '15 at 5:24
  • Well, this is a discussion strongly dependent on the technology used, but for several DBMS the partition filtering is done previous to the index filtering.This means that having a large amount of data, accessing to a single partition would reduce considerably the access time. This project will store 306 000 records a day. I don't know the data horizon expected, but this would make around 6.12 million rows per month, 73.4 millions per year. Of course those are not large numbers, but this is not a discussion on technical implementation but on design. – LironCareto Sep 16 '15 at 8:24
  • 1
    I went by the [mysql] tag, not talking about other RDBMS. And with proper clustering/covering index the overhead of choosing the right partition seems quite similar to selecting the proper "branch" of the b-tree index imho. – jkavalik Sep 16 '15 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.