2

Beforehand the table names and columns are in Spanish, my native language is Spanish.
(table names have been now edited to English)

I've got a database to manage the tickets sales of a trip that is made by a bus, the problem is that when I related the tables I didn't reason that when setting a trip to a bus the seats of this will be the amount of tickets of the trip, and for each trip this tickets will be different. Also the seat state has to change from available to unavailable when someone buys the ticket of that seat in each trip.

Here's the db model - only the tables involved in this problem :

enter image description here

EDIT: I renamed the table names to English language for be easy to read.

The only way that I imagine to do this is to create another table where I will insert the number tickets of a trip depending on the number of seats that a bus has, and when showing the available stalls in the app I'll set unavailable the seats of the bus where the number of seat is equal to the number of ticket that is already bought. But I don't feel this way leads to a normalized database.

1

You could reverse the relationship between passenger and ticket so that the ticket has a nullable FK to the passenger. When you create a new trip, create ticket entries for the number of passengers in the trip / seats on the bus. Then relate a passenger to each ticket as the ticket is sold. When a trip has no more tickets with an empty passenger id that trip is sold out.

3
  • did you check the database model i upload as image?, reverse the relationship between passenger and ticket does not resolve the problem, because one of them will be related to seat (ASIENTO), this seat to a section(SECCION) of a bus, and this bus (BUS) to a trip. right now with this model i have to create a new bus each time i assign a trip to a bus. – Jonathan Solorzano Aug 17 '14 at 1:21
  • 1
    Reversing the relationship allows you to create a ticket when you schedule the trip, not when you sell the ticket. This allows you to query and control seat "availability" by the existence of unsold tickets. – SMS Aug 18 '14 at 14:05
  • 1
    You certainly do not want to create a new bus row for every trip, only a new trip_bus row. You should also tie ticket directly to trip and bus, or to trip_bus. – SMS Aug 18 '14 at 16:55
0

As shown in your diagram, I think you have number of seats available per bus on BUS table

let say in table TRIP_BUS you have column like, where for every trip you need to make entry with BUS_ID and TRIP_ID

PK_TRIP_BUS -- make it auto incremented
BUS_ID
TRIP_ID
...Other column

In table TICKET_SELL let say

PK_TICKET_ID
FK_TRIP_BUS -- PK of TRIP_BUS table
..Other columns

Now you can make a count of ticket sold for a trip with

SELECT COUNT(*) 
FROM TRIP_BUS TB 
INNER JOIN TICKET_SELL TS 
ON TS.FK_TRIP_BUS = TB.PK_TRIP_BUS
WHERE TB.TRIP_ID =@TripId

Now for showing available seats you can deduct above count from total seats available in BUS table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.