预订网站的数据库表 [英] Database Tables for Reservation site

问题描述

我们正在建立预订系统。共有三个模块

We are building an Reservation booking system. There are three modules

1. 客户


2. 房间


3. 预订

客户可以预订一个或多个房间，日期不同。预订房间时，我想搜索在日期A和日期B之间可以预订的房间。

A customer can book one or many rooms and for varying dates. While booking a room(s) i want to search for which rooms are available to be booked between date A and date B.

表（可能的解决方案）
个客户（id，名称，.....）
个房间（id，roomNo，roomType，....）
个预订（id，room_id，fromDate，toDate）

Tables ( likely solution) customers(id, name, .....) rooms(id, roomNo, roomType, ....) bookings(id, room_id, fromDate, toDate)

目前我有这样的表

CREATE TABLE IF NOT EXISTS `bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `checkin` datetime NOT NULL,
  `checkout` datetime NOT NULL,
  `advance` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `booking_details_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `bookings_rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `booking_id` int(11) NOT NULL,
  `room_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `nationality_id` int(11) NOT NULL,
  `mobile` int(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(4) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

需要帮助设计数据库，这是一种更好的方法。

Need help to design the database and which is a better approach.

推荐答案

这些架构结构已经允许您查找两个日期之间的空闲房间。

These schema structures already allow you to find the free rooms between two dates.

以下查询会检查在开始时间之前预订的房间是否会在开始时间之前免费，对于之后预订的房间，它还会检查他们是否也在结束日期之后入住。

The following query will check if rooms booked before start-time will be free before start-time or, for those ones booked after, it checks if they're checkin is following end-date too.

select *
from rooms r
where id not in (
        select room_id
        from bookings_rooms br
            join bookings b on (br.booking_id=b.id)
        where (checkin < :dateA and checkout > :dateA)
            or (checkin > :dateA and checkin < :dateB)
    )

这篇关于预订网站的数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！