Converting large excel simulation to MySQL - Tools & Steps
Posted on 2008-06-10
In Excel I have a simulation that I have been slowly adding to over a series of months. The simulation is entirely based in Excel and simulates a 8 season period of a football clubs league life.
It allows for 8 clubs to be 'played' over the 8 season period, and each season you enter data for each club.
I want to convert the project to a MySQL database, some of which is simple , some is complex.
The key problem is the worksheet which represents the 'engine' of the simulation, this takes several hundred complext calculations based on the input from all 8 clubs and generates data based on them, this data is then in turn displayed in reports.
How can I go about breaking the incrediably complex excel spreadhseet apart strategically ?
How can I programme an MySQL table to derive data from other table values ? Is this possible or will the PHP script need to handle the calculations and then post them into the 'stats' table.
Can I use a function as a cell value within a MySQL table, and when will this data update if the function returns a new value, say for example when the club data is added ?
Ultimately I want to build an intelligent MySQL that can store the data , and perform all lthe calculations on the fly as data is updated.