Help with suitable mysql db structure

[sir pannels]

Alright people.

Stuck with the structure of a database.. will tell you what I have currently, then what I'm going for ...

I have a CSV file that is displayed on a page after some fancy parsing.

The CSV file has 6 important values seperated by commas...
ID, NAME, VALUE1,VALUE2,VALUE3,VALUE4
the values are numbers, to two decimal places.

There are about 500 lines of data in this CSV file. Also, something worth noting is that each one of those values corrosponds to a week... those four values are four weeks of data... the 4 most recent weeks, when another 4 weeks passes, more data gets added and the page will display only the most recent 4 weeks, but keep the rest still for archive reasons.

I need to put this Data into a mysql database and stop using the CSV.
I'm stuck on a structure to hold the data. Thought about putting a database for each id/name and in there having a table for each 4 week period in the table are four fields, the four values.. but the problem is there after a few years the table will be massive and also displaying data from 500 different databases on one page is going to be very slow.

I have no other idea how to go about this, if someone can help me brainstorm some structure ideas that would be great as im stuck.

Cheers all,
Sir P =]

[Beagle]

I would have a table with

id, name, weekOfYear, year, value

and then just select the last 4 weeks as 4 separate rows.

Yeah, after 1 year, you'll have 52 rows per name, that's not big at all. Big is 500k+ rows.

[guelphdad]

If you can show some sample data it might give a better idea of how to store the data for you. if you need to total the data etc. it may make sense to have those columns in a single column for instance.

[sir pannels]

Thank you for offers of help both of you.
Beagle, what format would weekOfYear be in?

week number or some kind of ww/mm format?

cheers

[sir pannels]

dont worry found mcal_week_of_year .. will use that :)

thanks again

[sir pannels]

Though im struggilin getting it to work.. tried printing mcal_week_of_year ( 29, 03, 2006 ) but it all gets printed.. tried returning mcal_week_of_year but it says undefined function, that is php4 and 5.. anyone have any thoughts of how to get the week of the year from a date?

cheers

[guelphdad]

Yes. look at the mysql manual (since you are using mysql right) and look at the date and time functions. You can pull the week of the year out of your dates.