How to model a database structure for a booking business domain?

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,

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

StackExchange Database Administrators Q#183547, answer score: 3