locked
2 tables or 6000 tables? RRS feed

  • Question

  • Hello everybody,

    sorry if i won't write english very well and sorry if this forum's section is not the correct one for my question but this is the first time I use this forum!!!

    I must project a database that allow me to store stock Exchange data for about 6000 stocks. For every stocks I must store the last price with the relative date and hour for the current day and the previous 4 days. The prices saving must be in push because I will have a my own program that will receive RealTime prices and will save them immediately on the DB.

    Of course there will be some stocks that will have a lot of trades and some other stocks with a little number of trades. For example, one stocks could have 14.000 trades per day and some other one only 1. Everytime I receive a price a must access to the DB and store it.

    With another my own application I will have to download the prices and see them in chart so i don't need to do particular queries over my DB but I will have a lot of data to store.

    My question is: which is the best way to project my database?

    At first i thought to create one table for each stock and saving the relative prices inside. Then I thought that maybe the best way would be to create one table that have only one field (the stock's symbol) setted as primary key and another table with an integer primary key, a relation with my first table and then other 4 field for my prices. The second tables will contain the prices for all instruments and so will be a monster table that at the end off the day could have more than 10.000.000 rows.

    My main target is that the queries made by my second application should be fastest as possible (1 or 2 second) to display the data in the client.

    So, in your opinion, which is the best way to project the database?

    Thank you very much

    Giacomo


    • Edited by Jack_Misani Wednesday, March 23, 2016 7:43 AM wrong title
    Wednesday, March 23, 2016 7:41 AM

Answers

  • I must project a database that allow me to store stock Exchange data for about 6000 stocks. For every stocks I must store the last price with the relative date and hour for the current day and the previous 4 days.

    Something doesn't match in your description.  My interpretation is that you need to store a price - a single price - for the current and previous 4 days.  But then you mention that a stock might have thousands of trades in a day.  Given that mismatch, you force your reader to make assumptions about what your system is intended to do.  Do you really need to store every individual trading price - and do you actually get that level of information?  Without knowing your circumstances or goals, discussions about designs are difficult and error-prone.

    One thought that does cross my mind is that you apparently have a need to - on a daily basis - remove prices that are more than 4 days old.  Perhaps you don't actually need to do that and you can just "ignore" them - purging can be done in a scheduled fashion.

    My first thought is that you should use a single table.  The individual table route violates normalization rules and will make your code VERY complex and VERY difficult. Every time a stock is added you will need to create yet another table - with the appropriate permissions - and then adjust dependencies. 

    that the queries made by my second application should be fastest as possible (1 or 2 second) to display the data in the client.

    Unfortunately, you cannot design properly with such vague requirements. And since you are asking basic design questions, I'll guess that you have very unrealistic expectations of what you can accomplish.  Applications that "react" that quickly are difficult to write and usually require complex threading and inter-process communication.  What version of sql server do you plan on using?  Partitioning might help in managing large amounts of data - but that feature isn't available in every edition.  Perhaps this is a situation where you should try some experimentation and determine which one better suits your goals.

    Wednesday, March 23, 2016 12:51 PM
  • Thank a lot Scott for you reply!!!

    You're right because I made an error describing my goal. I spoke about "last price" assuming that everyone can know what I mean for "last price". Indeed if you read the words you assume I have to store only one price for stocks but in reality I've to store every trading price, for example from today US Markets opening (1 hour and 20 minutes ago) i would have added:

    14.200 rows regarding Cisco

    6.100 rows regarding IBM

    In 1 hour and 20 minutes I would have added 20.000 row in my databases only for 2 stocks.....but I want to save prices for 6.000 stocks for all the day!!!

    I want to store these data for 5 days and at the beginning of the 6th day I can delete the data of the first day.

    I also thought about partinioning to have a faster data access and the SQL version is not a problem and writing this answer i thought to these 2 ways:

    1) I could create one normal table with the trading code of every stocks I want to save data. Another partioned table where I put the prices. I could partition the second table by trading code and then i could partition every partition by date

    2) I could create one normal table with the trading code of every stocks i want to save data. Other 5 tables, each one per day, and partitioning the 5 tables by trading code

    Thursday, March 24, 2016 3:02 PM

All replies

  • I must project a database that allow me to store stock Exchange data for about 6000 stocks. For every stocks I must store the last price with the relative date and hour for the current day and the previous 4 days.

    Something doesn't match in your description.  My interpretation is that you need to store a price - a single price - for the current and previous 4 days.  But then you mention that a stock might have thousands of trades in a day.  Given that mismatch, you force your reader to make assumptions about what your system is intended to do.  Do you really need to store every individual trading price - and do you actually get that level of information?  Without knowing your circumstances or goals, discussions about designs are difficult and error-prone.

    One thought that does cross my mind is that you apparently have a need to - on a daily basis - remove prices that are more than 4 days old.  Perhaps you don't actually need to do that and you can just "ignore" them - purging can be done in a scheduled fashion.

    My first thought is that you should use a single table.  The individual table route violates normalization rules and will make your code VERY complex and VERY difficult. Every time a stock is added you will need to create yet another table - with the appropriate permissions - and then adjust dependencies. 

    that the queries made by my second application should be fastest as possible (1 or 2 second) to display the data in the client.

    Unfortunately, you cannot design properly with such vague requirements. And since you are asking basic design questions, I'll guess that you have very unrealistic expectations of what you can accomplish.  Applications that "react" that quickly are difficult to write and usually require complex threading and inter-process communication.  What version of sql server do you plan on using?  Partitioning might help in managing large amounts of data - but that feature isn't available in every edition.  Perhaps this is a situation where you should try some experimentation and determine which one better suits your goals.

    Wednesday, March 23, 2016 12:51 PM
  • I would suggest you not reinvent the wheel.  There are many examples of how to do this on the Internet.  I would suggest starting here:

    http://www.sqlusa.com/bestpractices2005/stockmarket/

    Wednesday, March 23, 2016 1:02 PM
    Answerer
  • A couple of tables as a maximum, indexes and properly datatypes and good harware behind.

    Partitioning as scott_morris-ga pointed out

    Wednesday, March 23, 2016 4:39 PM
  • Thank a lot Scott for you reply!!!

    You're right because I made an error describing my goal. I spoke about "last price" assuming that everyone can know what I mean for "last price". Indeed if you read the words you assume I have to store only one price for stocks but in reality I've to store every trading price, for example from today US Markets opening (1 hour and 20 minutes ago) i would have added:

    14.200 rows regarding Cisco

    6.100 rows regarding IBM

    In 1 hour and 20 minutes I would have added 20.000 row in my databases only for 2 stocks.....but I want to save prices for 6.000 stocks for all the day!!!

    I want to store these data for 5 days and at the beginning of the 6th day I can delete the data of the first day.

    I also thought about partinioning to have a faster data access and the SQL version is not a problem and writing this answer i thought to these 2 ways:

    1) I could create one normal table with the trading code of every stocks I want to save data. Another partioned table where I put the prices. I could partition the second table by trading code and then i could partition every partition by date

    2) I could create one normal table with the trading code of every stocks i want to save data. Other 5 tables, each one per day, and partitioning the 5 tables by trading code

    Thursday, March 24, 2016 3:02 PM