Need advice on a database model

Hello guys. I am developing a small application with Laravel. Short description:

It is a booking application for location booking. There are centers, which contain rooms. Each room can be booked several times. The number of times a room can be booked is named units. So:

In the first page, "CENTERS" are listed. Each center will have several "ROOMS" (each "CENTER" will have its own unique "ROOMS"). Each "ROOM", will have a "UNITS" record in database. This record shows how many times the "ROOM" can be booked. Then, using a "FROM" and "TO" date, how can I set the "BOOKED" days for a "ROOM". I also need to know how many times for a given day, a "ROOM" has been booked. I already have something in my mind, but as a beginner in database design, it would be great if I got adviced from someone with more experience. This may seem hard from my explaining, but in reality, it isn't. Any help is really appreciated.

Thanks a lot.

taijuten

11 years ago

Level 10

Try writing out your Models, it might help you think about how to achieve this in a normalized way.

Center {
    hasMany('Room');
}
Room {
    belongsTo('Center');
    hasMany('Booking');
}
Booking {
    belongsTo('Room');
}

That's probably all the "structure" you need, and if you look into the Laravel docs, you'll see what each of these relationships require in terms of references. You have no pivot tables required in this structure.

Within your booking table, you'd simply have a "starts_at" and "ends_at" datetime column, so you can perform the queries you've requested. Also, your "Rooms" table would have an integer for "units", if I understand your purpose behind this.

Please or to participate in this conversation.