implementing a simple calendar

Question

I have come up to a dead end while trying to implement a simple calendar. That’s my weeks' table schema:

DROP TABLE IF EXISTS `weeks`;
CREATE TABLE `weeks` (
  `weeknum` varchar(255) NOT NULL DEFAULT '',
  `period1` varchar(255) DEFAULT NULL,
  `period2` varchar(255) DEFAULT NULL,
  `A11` varchar(255) DEFAULT NULL,
  `A22` varchar(255) DEFAULT NULL,
  `A31` varchar(255) DEFAULT NULL,
  `A32` varchar(255) DEFAULT NULL,
  `C11` varchar(255) DEFAULT NULL,
  `C12` varchar(255) DEFAULT NULL,
  `C21` varchar(255) DEFAULT NULL,
  `C22` varchar(255) DEFAULT NULL,
  `C31` varchar(255) DEFAULT NULL,
  `C32` varchar(255) DEFAULT NULL,
  `D11` varchar(255) DEFAULT NULL,
  `D12` varchar(255) DEFAULT NULL,
  `D21` varchar(255) DEFAULT NULL,
  `D22` varchar(255) DEFAULT NULL,
  `D31` varchar(255) DEFAULT NULL,
  `D32` varchar(255) DEFAULT NULL,
  `E11` varchar(255) DEFAULT NULL,
  `E12` varchar(255) DEFAULT NULL,
  `E21` varchar(255) DEFAULT NULL,
  `E22` varchar(255) DEFAULT NULL,
  `E31` varchar(255) DEFAULT NULL,
  `E32` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`weeknum`)
) ENGINE=InnoDB DEFAULT CHARSET=greek;

Column names are room numbers and the weeknum takes values from 15 to 45. after running a query that contains the weeknum(eg 40) as an argument, I get some room numbers (E31,E32 etc). I just want to set the cell value to “yes” for the appropriate row and column, depending on the query’s result. For example: after running

select room.roomnum
from payment,contract,room,customer
where payment.contractID = contract.contractID
and contract.roomID=room.roomID
and customer.customerID=payment.customerID
and payment.yearkoino='2009' and contract.weeknum=40 

I get :

+---------+
| roomnum |
+---------+
| c21    |
| a32    |
| c12    |
| d12    |
| d11    |
| e22    |
| a22    |
| c31    |
| e12    |
+---------+

this is also a screenshot that might give you an idea of what i am trying to do:

http://img37.imageshack.us/img37/7633/screenshotoy.png

Any help would be appreciated.

Answer 1

Generally speaking its better to use third normal form for these types of tasks.

One table for Weeks, one for Rooms, and then a third table that tracks the relation RoomWeek.

RoomWeek would have a minimum of two fields - RoomNum, child of Room.RoomNum WeekNum, child of Week.WekNum Status, track "Yes/No" etc. other fields as needed

next step would be to populate RoomWeek with a cross-join from Room and Week tables to get one row for every possibility.

So then when you have a query that returns a room list for a given week then you would do something like

update RoomWeek, payment,contract,room,customer
set RoomWeek.status = "Yes"
where payment.contractID = contract.contractID
and contract.roomID=room.roomID
and customer.customerID=payment.customerID
and payment.yearkoino='2009' and contract.weeknum=40 
and RoomWeek.weeknum = contract.weeknum
and RoomWeek.Roomnum = room.roomnum

If you are insisting on updating the current table then its tricky bc its much more difficult to update a table like Week when then column you want to update is dependent on the data itself (the roomnum returned) - this is possible to do but if the rooms change, your table has to change, the code may need to change etc.