I've exhausted my google-fu and cannot get anything like an answer, so I thought I'd ask here.
[background] I run a forum centered around an American Football based video game (BloodBowl). Each member has their own team, and we run leagues and cups. I thought it would be fun to write some 'stocks and shares' functionality for a site, so each member can invest in other teams. Prices would update after each game is played (~100 games per week), but the actual update would be asynchronous (I have current code triggering at the correct time, so it can be modified to incorporate what is needed. [/background]
Basically, I need to record the prices that each team (asset) has at each change, updated asynchronously but only once per week. With 200 teams, and 12 weeks to a season, I'd be looking at 2400 records per season (and this will run into infinity, though weeks are real-life weeks, so not too big).
My thinking is a single table, such as :
[teamID] Int
[price] Int
[date] Date
[dividend] Int
[gain] Int
[gain%] float(2dp)
[comment] Text
Which row(s) should be my primary key?
Am I over-thinking this? Should I just code it? My worry is that I want to be able to get historical data, for example for graphs and such, though I would be hitting this table very regularly as players trade stocks. I don't want to code it and later realise that I'm missing some field that would make all of my queries massively more efficient.
What I suppose I'm really looking for is the schema used by some db that actually uses this for stocks in real life, just to model it in a similar way.
Thanks for reading, and I hope that somebody has some input here.
Regards, Dan
