Database Design for hotel management system

[nitiphone2021]

Dear all,

I am design hotel management system as below schema.
Do you have any idea to improve it? to verify room status
 

[Barand]

I have a room booking tutorial here, if it helps.

[nitiphone2021]

it's a good tutorial but how can I get the available room?

I tried to follow your logic but it's not work

This is my sql query from laravel

	$load_room = DB::table('tb_reservation_room')
	->rightJoin('tb_rooms', 'tb_rooms.id', '=', 'tb_reservation_room.room_id')
	->select('tb_rooms.id', 'tb_rooms.room_name')
	->whereDate('checkin_date','<',$checkin_date)
	->whereDate('checkin_date','>',$checkin_date)
->get();

[Barand]

I don't speak laravelese but is that query trying to find a date that is both greater than and less than $checkin_date?

[nitiphone2021]

yes, both greater than and less than $checkin_date

[nitiphone2021]

first I tried this code will show all rooms in system

	select `tb_rooms`.`id`, `tb_rooms`.`room_name` from `tb_reservation_room` right join `tb_rooms` on `tb_rooms`.`id` = `tb_reservation_room`.`room_id`
	

I tried this code but no any output

	select `tb_rooms`.`id`, `tb_rooms`.`room_name` from `tb_reservation_room` right join `tb_rooms` on `tb_rooms`.`id` = `tb_reservation_room`.`room_id` where `checkin_date` < '2022-02-12'
	

[Barand]

1 hour ago, nitiphone2021 said:

yes, both greater than and less than $checkin_date

And you don't see a problem with that?

For example

1    2    3    4    5    6    7    8    9    10


Underline any number above that is both less than 5 and greater than 5