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.
the simplest solution, and the one that's properly normalized, is to have a "taken" table with unit, year, week, user, with primary key unit, year, week
that will take care of your main requirement, and the query for that will be dead simple
the only tricky part in finding 2-week accommodation for new users is if the 2 weeks have to be consecutive (and it gets trickier if it's last week of december plus first week of january) -- mysql doesn't allow subselects, so you're going to have to do a fair bit of logic in php