Mysql – How to model a database structure for a booking business domain

database-designMySQL

I'm working on a MySQL “database-driven” booking application but my brain can't seem to work through how to “normalize” the database for what I'm trying to accomplish (nor have I been able to discover anyone else posting about running into a similar hurdle after a week of searching).

My aim of “normalizing” (i.e., modeling) it while I create it is to maximize performance once it gets put into production.

Business Rules

Expanding on rathishDBA's assumption of business rules please note that, in fact, the business rules are as follows:

  • Each room may contain zero or more pieces of equipment (zero-to-many).
  • Each user can book one or more bookings (one-to-many).
  • Each booking belongs to exactly one user (one-to-one).
  • Each booking contains exactly one set (set in this context being defined as one user, one room, and one time slot).
  • Each booking's start in Room A must be greater than or equal to the end of another booking in Room A.
  • Each booking's end in Room A must be less than or equal to the start of another booking in Room A.
  • Each booking's usercount must be less than or equal to the room's maxcapacity

Functional Dependencies

roomnumber → (maxcapacity, equipment)

(userid, roomnumber, usercount, start, end) → calendarid

maxcapacity → usercount

Current Structure

In order to meet requirements, I created four tables (configurations is used to determine which room has what equipment):

CREATE TABLE rooms (
`roomid` INT NOT NULL AUTO_INCREMENT,
`roomnumber` VARCHAR(20) NOT NULL,
`maxcapacity` INT NOT NULL, -- maximum number of people allowed in the room
PRIMARY KEY (roomid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE roomequipment (
`equipmentid` INT NOT NULL AUTO_INCREMENT,
`equipmentname` VARCHAR(20) NOT NULL,
PRIMARY KEY (equipmentid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE configurations (
`configurationid` INT NOT NULL AUTO_INCREMENT,
`roomid` INT NOT NULL,
`equipmentid` INT,
PRIMARY KEY (configurationid),
CONSTRAINT FK_RoomId FOREIGN KEY (roomid) REFERENCES rooms(roomid),
CONSTRAINT FK_EquipmentId FOREIGN KEY (equipmentid) REFERENCES roomequipment(equipmentid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE calendar (
`calendarid` INT NOT NULL AUTO_INCREMENT,
`configurationid` INT NOT NULL,
`userid` INT(11) NOT NULL,
`usercount` INT NOT NULL, -- number of people attending booking
`start` DATETIME NOT NULL,
`end` DATETIME NOT NULL,
PRIMARY KEY (calendarid),
CONSTRAINT calendar_icfk FOREIGN KEY (configurationid) REFERENCES configurations(configurationid),
CONSTRAINT calendar_ibfk FOREIGN KEY (userid) REFERENCES users(userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The problem I'm running into with the way my tables are structured is that if a select query by roomnumber to get results listing what equipment is in a specified room I don't know how I should have it structured without violating Normal Forms.

I have the rest of the database functional but the last goal is to structure the database in a way that a user can add a new piece of equipment to roomequipment arbitrarily but I have no clue how to accomplish this without results such as duplicate rows of roomnumber or storing configurations in something like a CLOB XML attribute.

Sample Data

To expand upon my question, some sample data to further explain:

INSERT INTO rooms 
    (roomnumber, maxcapacity) 
VALUES 
    ('Board Room', 30);

INSERT INTO roomequipment 
    (equipmentname) 
VALUES 
    ('projector');

INSERT INTO configurations 
    (roomid, equipmentid) 
VALUES 
    ((SELECT roomid 
        FROM rooms 
       WHERE roomnumber = 'Board Room'),
     (SELECT equipmentid 
        FROM roomequipment 
       WHERE equipmentname = 'projector'));

Application Workflow

To clarify the context of this question, but as an aside from the database structure definition, the application workflow is as follows:

  1. A user is presented with a room availability search form.
  2. The search form has fields for start time, end time, capacity (max capacity), and equipment.
  3. When the user submits the form, the intention is for the database to be queried and return a list of available (non-booked) rooms. The user then selects the room they wish to book from a displayed list (each list item will show room name, max capacity, and available equipment).
  4. After a specified room is selected the user clicks submit and the booking information (designated room, user, start date, end date) should be saved.

Note: One of my biggest concerns would be a race condition if two or more users attempt to book the same room with identical or overlapping times.

Question

How can I solve this problem?

Best Answer

This may be totally wrong since I don't fully understand your business, but I'll lay out some ideas anyhow.

When modelling entities I would stay away from adding auto-generated keys in each and every table. At a later time that can be done if necessary, but to get a feeling af how things relate I would start with natural keys.

If I get it right your calendar table represents bookings and I would use that identifier. A user can book a room with zero or more equipment. This would lead me into something like:

CREATE TABLE bookings 
( userid INT NOT NULL
, roomid INT NOT NULL
, start DATETIME NOT NULL
, end DATETIME NOT NULL
,     PRIMARY KEY (userid, roomid, start)
, CONSTRAINT calendar_icfk FOREIGN KEY (roomid) 
      REFERENCES rooms(roomid),
, CONSTRAINT calendar_ibfk FOREIGN KEY (userid) 
      REFERENCES users(userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How does the equipment fit into the picture? If the equipment for a room is rather static, I would add equipment to the room:

CREATE TABLE equipment
( equipmentid INT NOT NULL AUTO_INCREMENT
, equipmentname VARCHAR(20) NOT NULL
,   PRIMARY KEY (equipmentid)    
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE roomequipment 
( roomid INT NOT NULL
, equipmentid INT NOT NULL
,   PRIMARY KEY (roomid, equipmentid)
, ... foreign keys to rooms and equipment
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

If on the other hand, the equipment for a room varies from booking to booking, I would relate equipment with the booking:

CREATE TABLE bookingequipments
( userid INT NOT NULL
, roomid INT NOT NULL
, start DATETIME NOT NULL
, equipmentid INT NOT NULL
,     PRIMARY KEY (userid, roomid, start, equipmentid)
, CONSTRAINT calendar_icfk FOREIGN KEY (userid, roomid, start) 
      REFERENCES bookings(userid, roomid, start),
, CONSTRAINT calendar_ibfk FOREIGN KEY (equipmentid) 
      REFERENCES equipment(equipmentid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In both cases, the USER books a room.

If you decide to introduce a surrogate key in for example bookings:

CREATE TABLE bookings 
( bookingid INT NOT NULL AUTO_INCREMENT
, userid INT NOT NULL
, roomid INT NOT NULL
, start DATETIME NOT NULL
, end DATETIME NOT NULL
,     PRIMARY KEY (bookingid)

don't throw away the candidate key you identified, make that a UNIQUE constraint:

,     UNIQUE (userid, roomid, start)

, CONSTRAINT calendar_icfk FOREIGN KEY (roomid) 
      REFERENCES rooms(roomid),
, CONSTRAINT calendar_ibfk FOREIGN KEY (userid) 
      REFERENCES users(userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

bookingequipment could then be:

CREATE TABLE bookingequipments
( bookingid INT NOT NULL
, equipmentid INT NOT NULL
,     PRIMARY KEY (bookingid, equipmentid)
, CONSTRAINT calendar_icfk FOREIGN KEY (bookingid) 
      REFERENCES bookings(bookingid),
, CONSTRAINT calendar_ibfk FOREIGN KEY (equipmentid) 
      REFERENCES equipment(equipmentid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

When to introduce a surrogate key is almost a religious issue, some people will tell you to always use them, other to never use them. Personally, I try to decide from case to case. The criteria I use:

A key should ideally be all of the following:

* unique -- obvious
* simple -- to many attributes is a bad thing, how many is too many?
* familiar -- can be used to identify something outside of the database
* irreducible -- no subset of the key should be a key
* stable -- should not change on a regular basis

In many situations there will be a conflict between these criteria and one will have to sacrifice one or more of these.

As mentioned I don't know your business, hopefully, you will get some ideas from my post anyhow.

As a side note, your INSERT statement can be written as:

 INSERT INTO configurations (roomid, equipmentid) 
 SELECT r.roomid, e.equipmentid 
 FROM rooms as r
 CROSS JOIN equipments as e
 WHERE roomnumber = 'Board Room'
   AND equipmentname = 'projector';

or by using an old comma join:

 INSERT INTO configurations (roomid, equipmentid) 
 SELECT r.roomid, e.equipmentid 
 FROM rooms as r, equipments as e
 WHERE roomnumber = 'Board Room'
   AND equipmentname = 'projector';

You can think of VALUES as a table constructor in OO terms, it is only needed when you want to insert literal values only:

 INSERT INTO configurations (roomid, equipmentid)
 VALUES (1,34);

If you are inserting the result of a select, you can add literals in the select:

 INSERT INTO configurations (roomid, equipmentid)
 SELECT roomid, 34
 FROM rooms
 WHERE roomnumber = 'Board Room';

EDIT: Temporal constraints

In SQL2011 support for temporal constraints were added to the standard, several DBMS:s implement at least part of this. You could then implement your business rules:

  • Each booking's start in Room A must be greater than or equal to the end of another booking in Room A.
  • Each booking's end in Room A must be less than or equal to the start of another booking in Room A.

like:

CREATE TABLE bookings 
( bookingid INT NOT NULL AUTO_INCREMENT
, userid INT NOT NULL
, roomid INT NOT NULL
, start DATETIME NOT NULL
, end DATETIME NOT NULL
,     PRIMARY KEY (bookingid)

,   PERIOD BUSINESS_TIME (start, end)
,   UNIQUE (roomid, business_time without overlaps) );

But as far as I know this is not implemented in the latest version of MySQL yet.

Instead, you can use validation triggers to enforce this at the DBMS level (untested):

DELIMITER @
CREATE TRIGGER validate_room_period
BEFORE INSERT ON bookings 
FOR EACH ROW
BEGIN
      IF (SELECT COUNT(id) FROM bookings
          WHERE (NEW.BEGIN <= BEGIN AND NEW.END >= BEGIN)
             OR (NEW.END <= END AND NEW.END >= END) 
         ) > 0
      THEN SIGNAL SQLSTATE '77000'
                SET MESSAGE_TEXT = 'Room already booked during this period';
      END IF;
END @
DELIMITER ;
Related Question