Methods to create a timeline chart
I have a database with the basic 2 tables : Clients----<Appointments . I 'm trying to create a timeline chart that would have X axis as a timeline : starting from the first appointment and ending to the last appointment, with regular monthly intervals in between (total duration more than a year). For each appointment I 'd like the Y axis to show a specific value from a field, named Score.
Create a dummy table, with a field that is monthly_timeline and a Score field. A script should populate the timeline_field with monthly intervals (starting from first appointment and ending to the last) and the Score field should be "copied"(?) or "looked up"(?) from the related table corresponding to the month. But should the script delete all entries everytime it is run before populating again? How should I set up the relationships so that the "Score" corresponds to the particular client and get all relevant dates?
To give you an idea about the tables :
Client 1 ---------- 1/1/2015 , Score : 2
1/3/2015 Score :4
1/5/2015 Score :3
Client 2 ---------- 1/5/2015 Score 10
1/6/2015 Score : 8
Hypothetical "Dummy table" (for Client 1)
1/2015 Score 2
3/2015 Score 4
5/2015 Score 3