Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    1

    database design and selection

    Hi,
    I am trying to create a database but before starting would like to consult some professionals on the best approach since I am a database rookie
    Basically I currently have a textfile collection which I would like to transform to a database since it is getting very large (150GB) and getting difficult to manage. I have financial stock information for a large number of stocks (ca.1000). Each stock has 3 different tables associated with it.

    Currently I save the information on a daily basis in a text file for each stock, for each table and I have information over the last 2 years. On average one textfile, for one day and one stock is 1MB large. Since I am querying my ‘database’ on a daily basis, on individual stock and table level this structure made sense and is working fine. I currently read all the stocks by opening all the required texfiles for one day , sort the rows by time, close the files and open the next day of data.
    Since I do not have experience with databases I wanted to ask what the best approach is of database design. Can I put all stocks together for all dates into one large database table? I am worried that this might take ages to sort and filter the database when I have multiple millions if not a billion records inside and have to merge tables before sorting. Any ideas if this is possible to solve efficiently? Further, which database would you suggest to choose for this kind of problem as speed is very important for me.
    Thanks for all the help in advance

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If the tables are indexed on the fields that you use to sort and join, the operations should be fairly quick.

    Since you are a database noob, I would probably suggest SQL Server. It's straight-forward to install/configure.

    Will there be many concurrent database users? If yes, PostgreSQL is also a viable alternative. It's quick, very ANSI compliant, with loads of features. It IS a bit more difficult to configure, though.

    I'm sure that others will suggest MySQL. It's fast, and easy to install, although it has fewer features than most database servers, and bogs down with many users. I've also seen reports of table corruption issues.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by loquin
    ... and bogs down with many users.
    unsubstantiated hearsay!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by r937
    unsubstantiated ...
    Not necessarily... ref Tweakers.Net Testing page 3 and beyond...
    Last edited by loquin; 02-14-07 at 17:43.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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