Database Design help

I have an issue with database design and i was wondering if anyone would be able to give me a hand.

basically this database would be used to organise accomodation. Basically the user signs up. They are given 2 weeks a year for 3 years in our resort. The user organisation is fine the thing we are having problems with is the unit organisation database or tables.

every unit is identified by a unique id (eg. gfr157 is a id used currently) every user is identified by a unique username.

the issue that we are having trouble with is designing a table that shows the unit id, username, year 1, year 2, year 3, week 1, week 2, week 3, week 4, week 5, week 6

we have tryed several ways of doing this. The most obvious being the table above. But if you look at it closesly the weeks arent actually assigned to a year so a query in php like SELECT year_1, week_1 FROM units WHERE year_1 = '2002' && week_1 = '1'. would have exactly the same out put if the year was 2003, 2004 etc.

The main thing that needs to be done is we have to be able to tell if a week has been taking. By first checking if the week and year combination are already taken. which in a way is simple enough. But it has to be able to check if the week and year combination is available over several different units.

i have had lots of trouble with this. so i thought i would pass it on to others to see if anyone can see a way this can be acheived.

Even if you have the slightest idea please post it. Any help is better than none.
Thanks in advance.

P.S. in using MySQL server if that helps

Hi Lido,
I would recommend having three tables: One for the hotels, one for weeks and one for years. You can then inner join them:

select * from hotels
inner join months on hotels.hotelId = months.hotelId
where months.month = 9

... or something similar to this? That way you can have unlimited bookings.

It seems to me that rather that the system that you are considering a better approach might be to label the weeks as follow: year1_week1, ....,year1_week52, year2_week1, ....,year2_week52,year3_week1, ....,year3_week52

I hope this provides some help. Of course you might even want to use even shorter labels like yr1w01 to yr1w52