Reservation system for resources for 365days?

How to design a reservation system for resources that user can book any time & day they select?

Currently, my application design only do booking for today. As:

I am using a JTable to populate a list of resources with details for user to view. It also indicate the block of time that other user had booked. I joined the workstation table, pc table, peripheral table, and timeslot table. When a user booking is successful it will insert the name of the user to the block of time booked in this timeslot table.

I do have a Reservation table where by it stores all the reservation details such as the date, time, the workstation reserved, user id etc.

However, I can't possibly create a timeslot table for the 365days. It didn't make sense.  How is such system(reservation system) implemented for real/in the market?




coffee_beanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimYatesCommented:
Can't you just have a booking table which stores:

Start Date/Time
End Date/Time
Person ID
Description

Then you can find bookings for a particular day, as that day will have StartTime <= today and EndTime >= today
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bochgochCommented:
I think you need a more complex solution. Use a booking table as above, supplement this with rules (which you can implement as you prefer, code or data) related to which days are bookable (eg. not weekends / public holidays), the hours that can be booked (eg. 9am to 5pm), the length of time bookable etc....

Then validate your user entry through these rules -- when valid update your table. You can also use these rules to present available timeslots, current bookings etc. to the user.

You don't need to create all those timeslots, you define the rules related to timeslots and implement through a mix of rules and data....
0
coffee_beanAuthor Commented:
Hi TimYates, I already had the booking table.
 
>>I do have a Reservation table where by it stores all the reservation details such as the date, time, the workstation reserved, user id etc.

Here is a screenshot of my resource reservation  if it's helps. It's still very basic at the moment.  

http://acupofcoffee.multiply.com/photos/photo/5/1.jpg

You see my table has the time block (columns) and information of the user who booked at that timeslot.
I can do so becos I had a table (timeslot) that is created to handle reservation for that day. Whenever user successfully reserve a workstation, the name +id of the user is inserted into the timeslot table.  

As said, I can't create a timeslot for 365days. So how am I suppose to create a table view of the resources, their timeslot availability and info of the user who booked on that slot??
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

TimYatesCommented:
I still don't get what you mean :-(

What do you mean by "I can't create a timeslot for 365days"

Sorry...  It's probably too early in the morning ;-)
0
coffee_beanAuthor Commented:
i meant Timeslot table inside Microsoft Access database.
This timeslot table is where I had columns of the time (eg0830,0930 ...etc)

Timeslot table is one of the tables I used it to joined it with workstation table, pc table, peripheral table. so that I can display the columns of timeslot together with other workstation details. Whenever user successfully reserve a workstation, it will insert their name and user id into this Timeslot table.

Screenshot if it helps.

http://acupofcoffee.multiply.com/photos/photo/6/1.jpg
0
TimYatesCommented:
Yeah, what I meant was instead of creating a table with a column for every time over a year, and a row for every resource, just create a table where it has a start time, and end time, and a resource id...

You can then find timeslots for a specified time by using a "where" clause in your SQL
0
coffee_beanAuthor Commented:
no no this qn has not been abondon. I will close this question myself. Thanks.

0
coffee_beanAuthor Commented:
Tim Yates, bochgoch

Thanks for the advice. I have closed this qn.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.