database structure

[php_b34st]

I curently have a database with over 1000 entries in it the databse is structured as follows:

 

code | name | club | points | price

 

every saturday I update the database because the points for each entry changes. at the moment the points field is for total points but now i need to show weekly and monthly values.

 

there will be 41 weeks which is 10 months in the season. The months are not necessaryily calandar months as it works on a system of every 4th week is 1 month. for example:

 

the season started on the 16th august so

16th august, 23rd august, 30th august and 6th september = month 1

13th sep, 20th sep, 27th sep and 4th oct = month 2

etc...

 

how can I structure my database to hold the relevant data? I originally thought of just adding another column which would be week but that would mean at the end of the season the database will have over 41000 entries. Also how would I get the script to recognise which week and month it currently is to show the relevant data?

[ThunderAI]

My initial thoughts are to have three tables.

 

One table will have the following structure:

 

ID | code | name | club | points | price

 

then another table with this strucure:

 

ID | Month_ID | W_points | Parent_ID

 

ID | Month_name

 

Parent_ID is an index forgien key to the first Tables ID field.

 

the last table ID is an Index forgien key to the second to last table.

 

When information is added to the second to last table, a function is called that adds the points to the first table.  In order to get the number of points in a month you add up all rows with the proper month_ID.

 

 

[php_b34st]

Thanks for the reply, This looks like it will work for the monthly values but maybe not for individual weeks, however I will test this and see what outcome i can get.

 

Thanks again for the help

[ThunderAI]

It should work just fine for weeks:

 

You will have four rows for each month ID.