M
M
Mick Coder2015-02-03 12:03:44
MySQL
Mick Coder, 2015-02-03 12:03:44

How to properly design the database "booking"?

Hello! I need to properly design a DB (Hotel Room Cleaning). Cleaning takes place every week in the occupied room, but the client can appoint a cleaning day (for example, every Tuesday)! For the rest of the rooms (not booked, cleaning is not necessary). But you can also assign a separate room cleaning.
What I did. Now I have 3 tables rooms, cleanings, workers.
Cleanings (
id_cleaning bigint (20) NOT NULL
date_cleaning date NULL
id_room int(11) NULL id_worker
int (11) NULL
notes text NULL

(
id_room int(11) NOT NULL
number varchar(5) NOT NULL
last_cleaning date NULL // Last cleaning optional
status tinyint(4) NULL // Room status optional
cleaning_day tinyint(7) NULL // Day for regular room cleaning
lock_start date NULL //start of booking number
lock_finish date NULL // check-in of the client
)
Workers ( //The one who will clean the room,
id_worker int(11) NOT NULL
name varchar(50) NOT NULL
phone varchar(20) NULL
)
At the output in my program You should get a similar table like this:
Screenshot of the application
I'm waiting for your advice, what can be added, what can be removed! thank!

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Mick Coder, 2015-03-02
@lbondodesc

Made it like this! 011aac7b26104ffcb2723571c000320e.png
And I define actual reservations with queries like

SELECT * 
FROM reservation
LEFT JOIN rooms ON rooms.id_room = reservation.id_room
WHERE reservation.lock_finish >= CURDATE()

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question