Rooms reservation DB - any help?

[Paolo Galli]

Hi all
I'm working on a rooms reservation system based on Access 2003 and then
realize an ASP web page to manage it.

I need a little help in creating the DB since my ideas aren't so clear.

First of all what I need is very simple:

- I have two rooms and I have to let user reserve time slot of 15
minutes each
- every day has 8 slot
- a user can't book more than 1 slot per day

I thought to realize a DB with this structure

- 1 table with ID,DATE,ID-CUSTOMER
- 1 table with ID,NAME,SURNAME,PHONE,ID-SLOT
- 1 table with ID,SLOT

then I connect the first table with the second, and the second with the
third...

I need that every user opne and update a single slot record so I caan't
put in a table all the slots.

but I'm not sure... am I in the right direction?

anyone can help me?
thanks

 

[scubadiver]

When you say a person can't book more than one slot what exactly do you
mean? Can I book one slot in both rooms? Or am I restricted to one slot for
either room per day?

 

[Paolo Galli]

NO, just a slot per day per user

What do you think about a simpler db like that

Customer table = CUST_ID,NAME,SURNAME,PHONE
Reservation table = ROOM_ID,DATE,SLOT1,SLOT2, ... ,SLOT8

thanks
Paolo

 

[Beetle]

You have a many-to-many relationship between Customers and Rooms
so you need;

tblCustomers
**********
CustomerID (Primary Key)
FirstName
LastName
Phone

tblRooms
*******
RoomID (PK)
RoomDescription

tblCustomerRooms (junction table)
**************
CustomerID (Foreign Key to tblCustomers)
UseDate
RoomID (FK to tblRooms)

The PK of the junction table would be a combination of CustomerID
and UseDate. The junction table would also need additional field(s)
for start time or slot number, however you want to track that.

 

[scubadiver]

What I would is have three tables:

One table for customer ID and name
One for date ID and date

One table for storing Customer ID, date ID and slot

*BUT* what I would do is give this third table two primary keys: one for
customer ID and one for date ID. This prevents you from giving one customer
more than one slot for a particular day but will let you give a slot for a
customer on different days.

So you create one form for the three fields.


Customer table

Cust_ID (PK)
Cust_name

Date table

Date_ID (PK)
Datefield

Slots

Cust_ID (PK)
Date_ID (PK)
Slot


Having said that you can create a main form and subform but the primary keys
will prevent you from creating duplicates.

 

[scubadiver]

Did you manage to sort it out?

 

[Paolo Galli]

I'm trying but is the first time I managed something so complex...

I'll try online I didn't find nothing already done similar to what I need

thanks

 

[scubadiver]

What I suggested seemed reasonable (if I understood you correctly that is!)

 

[Paolo Galli]

yes of course!



thanks

What I suggested seemed reasonable (if I understood you correctly that is!)