Search code examples
sqlentity-relationship

Help understand booking system

I'm creating a theatre booking system.

I am quite confused on how I can get multiple tickets to one booking, and able to query those tickets separately. (fields that are of no relevance to the question have not been included)

I have a ticket table:

ticketId, ticketName

Booking Table:

bookingId, bookingReference, ticketId

When connecting this I will receive the ability to create many tickets but the bookingId will change everytime, I will need the ability to find all the tickets associated with a booking and then query an individual ticket so it can be used for single ticket printing etc.

Can anyone help me understand what I need to do.

Thanks.

Solution

  • The relationship between Tickets and Bookings is many to one. It would make more sense to have a field bookingid in the ticket Table rather than having a ticketId field in Booking table:

    Ticket table:

    ticketId, ticketName, bookingId

    Booking Table:

    bookingId, bookingReference

    SELECT * FROM Ticket WHERE bookingid = foo
SELECT * FROM Ticket AS T INNER JOIN Booking AS B on T.bookingid = B.bookingid

    etc