Dates in databases help ?

Well ive been reading up and id just like to pose the problem.

i want to hold information about bookings in a database.
they will make a choice about what date they wish to book from and then the type of booking either 3-4 or 5 days.

So they will pick these values , and what i was to do is then create a page that will create some sort of timetable as it were to show the availablility over say a month.

am i going to have to test every date with dates in between calculated dates. (if that makes sense)

i can hardly store every date that is booked in a certain booking so how am i going to test each booking for availability .

im so confused with the idea and how other people have stored dates in such ways.(im using mysql)
If anyone could help (de)confuse me.

for simplicity, store the first and last date of a booking, even if it's the same date (for a 1-day booking)

this allows BETWEEN syntax in your sql

so in theory i could :

Loop through the database getting bookings in the current month.
Then display in date order the bookings and if a booking does not exist for a certain day or days display its availability using this between statement.

you could, i guess

i wouldn't

to start off, sql doesn't loop

you get a single result set, i.e. a table, returned from the SELECT

how the database does it (looping, or the equivalent) is not your primary concern

what you want is to return all bookings where the given date is between the start and end booking dates

if you get any results back, then that given date is already booked

if you don't, it's available

easy, eh?

easy indeed if im using sql but i have to use JDO (java) and have no idea how i could do exactly the same thing using it .

JDOQL is ment to be crap to do this sort of thing....

hi all,

i am currently completing a similar project for recording the bookings at a YMCA. they have a limited number of beds and so need to keep a check on whos coming in and out to prevent double bookings

my database structure is like this

bookingID
date_in
date_out
accomodation /* allocated a room */
.
.
(other personal name, address type stuff...)

when i a new booking is coming in i will search for bookings between the new bookings date of arrival and date of departure.

i.e. SELECT * WHERE date_in > selected_arrival_date AND date_out < selected_leaving_date

(my greater and less thans may be the wrong way round) This allows me to see everyone in between those two dates. If the number of rows returned is greater than the constant NO_OF_BEDS then they can't be booked in. I'm doing this in PHP but at the end of the day the theory is the same

This is my 2cents, but it bothers me that i can't tighten it down at the database level, only in the application. If someone can come up with a better method please let me know

Thanks

Tyndyll