hi everyone, i don't know if i have post this at the right place. Anyhow, i am facing some problems in figuring out how to approach a recurrence booking system. I am required to check if a room is available for booking. Each booking has a recurrence type: daily, weekly, fortnightly, monthly. Before i can book a venue, i need to loop through the database to see if there is any overlapping.
What is the best approach to tackle such kind of problem? Thanks~
That problem is close to impossible to solve, its vague and too big, try solving the smaller problems its made up of instead. Some clarity on the requirements will help, details are important. Some of the problems will be bigger in your head than they really are, others won't exist at all. One thing I can help with is that loop through the database, its not needed at all. You'll run a query instead, that's about all a database is useful for. I'm kidding of course, but kinda serious too.
If you have problems as you go along, feel free to ask and tell us what you've got so far and what you're stuck with.
Joined: Aug 28, 2008
thanks for the advice.
I meant to use the query for the start and end date. The problem could be i wouldn't be able to detect those activities that recurred. Even if i could do that, what if the going-to-be newly created activity also has recurrence. I don't know if i think too much...
Place yourself in our shoes. We have never seen your system. We don't know any of the logic. We have no idea what you mean when you say "booking system". Or even when you say "query". We don't have any idea of the code that you wrote period.
We know the Java API, and have some experience in using it.
So, if you have a question, I would recommend that you (1) restrict the questions to Java specific terms and not your application specific terms, (2) don't assume that we know what logic you are talking about, and (3) give us as much details as possible.
the query for the start and end date
What is the start and end date? Is it using the Java Date object? What is a query on that date? What does that mean? .... and ... what is the question? As even if you describe this, there isn't a question here?
Currently, i need to write a reservation system for classrooms. The booking entity consists of the following fields: (important fields) start date, end date, recurrence type(daily, weekly, monthly, fortnightly), day of week, date of month.
Before a booking can be committed successfully, i need to validate if the booking has any clashes/conflicts with any of the existing booking for that particular classroom at the time selected for the newly made booking.
Right now, i intend to
1) use query to obtain list of bookings that use the classroom within the start and end date of the newly made booking. 2) then i use for loop to check if the existing bookings have any recurrence, if any, i need to loop again with the condition like if (the booking is weekly, fortnightly or monthly)
The question is whether i am on the right track or there could be better way in doing this?
For classrooms, you would set up bookings repeatedly for the whole academic year; if you have biology in S3 on Tuesdays from 3 to 4, then S3 will be booked for biology every Tuesday at 3.00 until the end of term. Maybe:
Put the bookings into a database.
Have a List of rooms; each room has a list of bookings with dates/times
But these are only vague suggestions.
What you actually want is called "constraint programming." But I know next to nothing about it.
Joined: Aug 28, 2008
hi ritchie, you got it right there. The only thing is my system requires the ability to check for clashes whenever i input a new booking, so that they would not be any conflict of courses for the same venue at the same time.
Just one thought which may help a little bit: you can make some simplifications when retrieving the initial data set. Let N be the new booking and R be a retrieved record from the database. Then in the database query there are some mutual exclusions which will avoid many unnecessary records being retrieved:
If N.recurrence is weekly or fortnightly and R.recurrence is weekly or fornightly, only select if N.day_of_week == R.day_of_week.
If N.recurrence and R.recurrence are both monthly, only select if N.day_of_month == R.day_of_month.
Limit the results by time---so if it's a 3-4pm booking, you'll only want to select records which overlap with a 3-4pm booking (if the booking is by single hours only, then this selection is even easier).
If there are now any matching records which are daily, there will be clash, inevitably.
If the input is weekly and there are matching weekly or fortnightly records, clash.
If the input is fornightly and there are matching weekly records, clash.
That'll cut down the possiblities by so many orders that eventually you'll have the result you require. You probably won't need loops, at least in Java. Try to use database queries only to achieve the goal (e.g. in SQL you can also use loops). This is really a logical puzzle, and you need to think through all the possibilities carefully. I'd do like the above and just list as many different ways a clash can occur as you can. You could have separate queries depending on what type of recurrence N is, for example, since they will be different in each case. But you'll still only need one query. The difficulty comes when you're using monthly, since the date of month could correspond to any day of the week, so you'd need to use Java (or an SQL date function) to figure out what day that actually is. Does that help?
Charles Lyons (SCJP 1.4, April 2003; SCJP 5, Dec 2006; SCWCD 1.4b, April 2004)
Author of OCEJWCD Study Companion for Oracle Exam 1Z0-899 (ISBN 0955160340 / AmazonAmazon UK )
Joined: Oct 13, 2005
You can put in details, eg
bookRoom(TUESDAY, 3, 4, "Biology");
and return false if there was a problem getting a booking, or true if the booking is all right.
Joined: Aug 28, 2008
hi everyone, i really appreciate all the assistance here, i guess i will try on Charles's method. It didn't struck me that sql queries could be so useful, perhaps i was so confused with the problem itself.
Joined: Mar 27, 2003
As I said before, you should be able to do the whole clash detection in SQL, without even touching Java. Just grab yourself an SQL editor, something like RazorSQL or the free MySQL Query Browser, and keep improving your queries until you get what you need.
There are two possibilities for returning the data that I thought of:
If you need to know what actually clashes, then you'll need to end up selecting all records which do indeed clash, and those will be returned as a Java ResultSet for your inspection. If it's empty, you know there are no clashes. If not, you can investigate the results further.
If you only want to know if there is a clash, but not with what else, then use a "SELECT count(*) ..." query. This will return just one row with one column: the number of records which would have been returned. You avoid having to return the record data itself if you don't need it.
Happy coding! [ November 03, 2008: Message edited by: Charles Lyons ]
Joined: Aug 28, 2008
Hi Charles, i just came out with the ejbql for the method you mentioned earlier. However, i would like to ask if the N.Recurrence is Monthly and say the date is 25th of the month, could there be a possibility that the 25th is a Monday and it happens to clash with a Weekly booking that is on a Monday also?
Currently, my understand of your method is that if N.recurrence is either a weekly or monthly i will only check for R.recurrence with weekly or monthly. Is that right?
My current flow of checking:
1) Check whether the existing bookings(R) is within the new booking(N), which includes the start date, end date, start time and end time.
2) Next, from the result set of (1), i check through the frequency with Daily.
3) If there happen to be no clash in (2), from the result set of (1), i check though frequency with Weekly or Fortnightly.
4) Again, if there happen to be no clash in(3), from the result set of (1), i check though frequency with Monthly.
According to my flow, i will go through all the conditions regardless of whether N frequency is Daily, Weekly, Fortnightly or Monthly. Did i miss out something? Please correct me if i am wrong.
Joined: Mar 27, 2003
I'm afraid I have my own such design problems to think about ATM so don't have time to check through all your logic. But...
i would like to ask if the N.Recurrence is Monthly and say the date is 25th of the month, could there be a possibility that the 25th is a Monday and it happens to clash with a Weekly booking that is on a Monday also? Currently, my understand of your method is that if N.recurrence is either a weekly or monthly i will only check for R.recurrence with weekly or monthly.
I was only saying that IF BOTH are weekly or fortnightly, then you can go by day of the week to find a clash. Of course there can also be a clash daily which should be the first check. Naturally monthly can also clash, but it's more difficult to do because the day of the month varies on the same date of each month! So you'd need to think carefully about that. Incidentally, you may find that "day of month" isn't an ideal booking method because, unless you book 7 days a week, it could easily end up being a Sunday... and who wants to go from having classes on a Thursday to a Sunday for example??
I note you're using EJBQL. Are you using the newer JPA for this? If so, I'd personally recommend avoiding all the overheads of JPA QL-SQL conversion by using a native SQL query. Something like:That's what I'd do anyway... the native SQL will be slightly better in performance since you avoid the JPA query overheads. But you still get a Booking entity back too. [ November 04, 2008: Message edited by: Charles Lyons ]