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.
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
startin Room A must be greater than or equal to the
endof another booking in Room A.
- Each booking's
endin Room A must be less than or equal to the
startof another booking in Room A.
- Each booking's
usercountmust be less than or equal to the room's
roomnumber → (maxcapacity, equipment)
(userid, roomnumber, usercount, start, end) → calendarid
maxcapacity → usercount
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.
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'));
To clarify the context of this question, but as an aside from the database structure definition, the application workflow is as follows:
- A user is presented with a room availability search form.
- The search form has fields for start time, end time, capacity (max capacity), and equipment.
- 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).
- 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.
How can I solve this problem?