Java/Database Query - looking for your expert advice! 1

[relisys]

I'm developing a simple prototype system for online booking of a doctors surgery.

Basically, the doctors can book in the times they are at a given surgery 48 hours in advance. Patients can then book slots according to surgery or doctor.

I'm using MySQL - and Java servlets (XML DOM to output HTTP response)

What i'd like advice on is the best way to construct the database and the best method of getting the dates / times from java to allow me to do this. The actual java manipulation i can do, its just the way in which i write the tables to best be able to allow this. I've never attempted dates and times etc and was wondering if any one had accomplished something similar with which they could give me their advice and tips for.

Thanks in advance

Relisys

 

[palbano]

Basically, the doctors can book in the times they are at a given surgery 48 hours in advance. Patients can then book slots according to surgery or doctor.

sorry but i can not determine your requirements from that statement.

Maybe you mean something like this:

1) Doctor can enter a time they are available for surgery no more than 48 hours in advance. This entry is then "available".

2) Patients can "book" an "available" time by doctor, thus changing the state of the entry to "booked".

But maybe you mean something completely different, i can't tell.

-pete

 

[relisys]

yeah thats it palbano

doctors book slots which become available (for the following 48 hours). Patients can then book slots which are available.

Any suggestions?!

relysis

 

[palbano]

>> the best way to construct the database

I do not claim this is the "best" but it is one way.

you need a DATETIME column and a BOOLEAN column. When the new row is created the DATETIME column value is supplied from the users (doctor) input and validation logic. The BOOLEAN column is set to false meaning that the slot is "available".

A simple Query can obtain all rows where the BOOLEAN column is false providing a list of "available" times.

When a patient "books" that slot the row's BOOLEAN value is updated to true.

>> best method of getting the dates / times from java

use java.util.Calendar to work with Date/Time values obtained from user input and java.sql.Date to read/write database Date/Time values.

hope this helps
-pete