CODEWITHSUNDEEP
sqldatabase-design
Steve Robinson
Steve Robinson

Reputation: 3939

Database design for time tables, intervals, reservation etc

I am working on a project and I want some inspiration or insights for solving a problem. I am writing an app for logging visitors to a prayer center (a place with many rooms. people come and pray). We are going to integrate a reservation system. Now people can say "Ill come on so and so date" or "Ill come every wednesday" or "Ill come twice a week" or"Ill come once a month" etc.

I donot know how to maintain this sort of info in a relational db. Can anyone gimme some insights into this?

And How do I query the database to find which persons are scheduled to visit on a particular day?

Upvotes: 1

Views: 2733

Answers (1)

No'am Newman
No'am Newman

Reputation: 6477

This schema should be enough to get you started

VISITORS
id
name
... other atomic data

RESOURCES
id
name
... other atomic data

RESERVATIONS
id
visitor
resource
fromdate
tilldate

Thus if I want to reserve room number 200 from 10:00 till 12:00 on 19 November, there would be a record in the reservations table in which the 'visitor' field points to my entry in the visitors table, the 'resource' field points to the entry for room 200 in the resources table, 'fromdate' will be equal to 10:00 19-11-2012 and 'tilldate' will be equal to '12:00 19-11-2012'.

You can write queries which will show which resources are reserved at a specific date/time, such as

select resources.name, visitors.name
from resources inner join reservations on resources.id = reservations.resource
inner join visitors on reservations.visitor = visitors.id
where reservations.fromdate <= "2012-11-19 06:00"
and reservations.tilldate >= "2012-11-19 23:59"

and queries which show which resources are free at a given time

select resources.name
from resources 
where not exists (select 1 from reservations
where reservations.resource = resources.id
and reservations.fromdate >= "2012-11-19 10:00"
and reservations.tilldate <= "2012-11-19 12:00")

If someone says "I'll come every wednesday", your application program will have to be smart enough to insert several rows into the 'reservations' table according to the required resource, date and time.

Also, see this question: Database Tables for Reservation site

Upvotes: 1

Related Questions