Rooms reservation DB - any help?
in [Tables]
|
Prev: sharing databases
Next: How Add Row Number
From: Paolo Galli on 25 Jun 2008 09:29 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
From: scubadiver on 25 Jun 2008 10:45 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" wrote: > 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 >
From: Paolo Galli on 25 Jun 2008 10:55 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 scubadiver wrote: > 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" wrote: > >> 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 >>
From: Beetle on 25 Jun 2008 11:47 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. -- _________ Sean Bailey "Paolo Galli" wrote: > 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 > > scubadiver wrote: > > 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" wrote: > > > >> 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 > >> >
From: scubadiver on 25 Jun 2008 11:51
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. "Paolo Galli" wrote: > 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 > > scubadiver wrote: > > 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" wrote: > > > >> 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 > >> > |