SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Which database design is best for this?

    Hi everyone,

    I'm having trouble figuring out which database design would be best (and less CPU heavy) for a project I'm working on.

    Let's say I'm tracking stock prices each day for several thousand different stocks. I'm tracking the date, start price, end price, high price, and low price for each stock.

    I'm eventually going to have a PHP page that let's me choose specific dates and displays the averages details for each stock for the dates selected.

    So here's my issue. Would it be best for me to create a new table for each date, and on the PHP page go through all the tables to find the avearages for the list of stocks.

    or

    Would it be best for me to have one table with all the data on it?

    I know initially it would be best for me to have one table with all the data on it, but if i had over a years worth of data on the table and wanted to search that table for a specific date range...wouldn't it be CPU heavy having to go through hundreds of thousands of records to find the specific data I queried?

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'd guess that putting an index on the date column would get round any problems, and MySQL is said to be able to work with millions of rows with no problems. So one table is better than continually creating new tables, and then having to create new queries to access the new table.

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Like Dr. John says, if the table is indexed properly, then tables of that size shouldn't be a problem. Creating multiple tables would just be a mistake.

    You'd want a table for the basic stock info (stock name, stock type, etc) and a table for the various markets (NYSE, NASDAQ, Tokyo, etc), then a table which marries the three together

    StockMarketValues
    StockID - FK to the StockInfo table I described above
    MarketID - FK to the Market table described above
    MarketDate - you could have a more granular if you wanted to track hourly or something
    OpeningPrice
    ClosingPrice

    But that's off the top of my head. I'm sure there's more details needed...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,809
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Small table sizes like that should certainly be no problem if properly indexed. It is only once you start to get into billions of records that you might need to start rethinking the design. A few million records is next to nothing for a database.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's what I was thinking, but just wanted to make sure. Thanks for your help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •