database design and selection

djdjoko · #1 (**permalink**) 02-08-07, 11:18

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

loquin · #2 (**permalink**) 02-08-07, 13:56

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.