Modeling bookings in an RDBMS

To model data shown in Figure 4.1 in an RDBMS, we will have to create tables for bookings, journeys, passengers, and users. In the previous model, we have intentionally added booking_id to Journeys and user_id to Bookings. In an RDBMS, these will be used as foreign keys.

We also need an additional table Bookings_Passengers_Relationships so that we can depict the many relationships between Bookings and Passengers. The multiple relationships between Bookings and Passengers help us to ensure that we capture passenger details for two purposes. The first is that a user can have a master list of travelers they have travelled with and the second use is to ensure that all the journeys taken by a person can be fetched when the passenger logs into their account or creates an account in the future.

Note

We are naming the foreign key references with a prefix fk_ in adherence to the popular convention.

Figure 4.2: Modeling bookings in an RDBMS

In an RDBMS, every record is a representation...