We tried to find a proper database structure for storing large amount of data for a long time. The solution below is the result of more than 6 years of experience. It is now working flawlessly for our quantitative analysis.
We have been able to store hundreds of gigabytes of intraday and daily data using this scheme in SQL Server:
Symbol - char 6
Date - date
Time - time
Open - decimal 18, 4
High - decimal 18, 4
Low - decimal 18, 4
Close - decimal 18, 4
Volume - int
All trading instruments are stored in a single table. We also have a clustered index on symbol, date and time columns.
For daily data, we have a separate table and do not use the Time column. Volume datatype is also bigint instead of int.
The performance? We can get data out of the server in a matter of milliseconds. Remember, the database size is almost 1 terabyte.
We purchased all of our historical market data from the Kibot web site: http://www.kibot.com/