Storing Stock Market Data

[Cagecrawler]

I have a fantasy stock market that I am in the process of creating, and what I want to do is give the user data about changes over a 6hr and 24hr period.  The prices update every 5 mins, so every 5 mins the last value would be discarded and a new one added to the front end.  My qustion is this - what is the most efficient way of doing this?  I was going to store it in a database and then move all of the values, but I'll need almost 300 columns to do it (and with over 100 companies, thats a lot of data). That seems really inefficient, but I can't think of any other way to do it.

Any ideas?

[utexas_pjm]

I think the most efficient way IS to use a database.  I don't know anything about your implementation but I assume it will go something like this.

DB Schema:
companies {company_id, company_name, company_abbr}
stock_values {value_id, company_id, value, date_inserted}

When values change simply insert n values into the stock_values where n := # of companies.

[code]
<?php
//...
$sql = 'INSERT INTO `stock_values` (`company_id`, `value`, `date_inserted`) VALUES ("'.$campanyid.'", "'.$value.'", "'.date('Y-m-d h:i:s').'")';
//...
?>
[/code]

Then you can view the most recent value of each stock like this:

[code]
<?php
//...
$sql = 'SELECT `c.company_name`, `sv.value`
FROM  `companies` as c
INNER JOIN `stock_values` as sv
USING (`company_id`)
WHERE `company_id` = "'.$comapanyid.'"
ORDER BY `sv.date_inserted` DESC';
// ...
?>
[/code]

Additionally you can give historical data like this:

[code]
<?php
//...
$sql = 'SELECT `c.company_name`, AVG(`sv.value`) as avg, MINUTE(`sv.date_inserted`) as period
FROM  `companies` as c
INNER JOIN `stock_values` as sv
USING (`company_id`)
WHERE `company_id` = "'.$comapanyid.'"
GROUP BY `period`
ORDER BY `sv.date_inserted` DESC';
// ...
?>
[/code]

Hope this helps,

Patrick

[Cagecrawler]

Thanks. It's similar to what I was thinking, and it'll give me a good start.  I'll no doubt be back saying how it never works... :P