Sql-server – Logical problem with a database model

relational-theorysql server

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.

Best Answer

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.

Related Solutions

Sql-server – How should row-specific metadata be created handled for an outer join view

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the stored procedure in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET. In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the stored procedure can do some validation, such as checking the tickets all belong to the correct user.

Sql-server – Querying multiple tables

This will get you the Status Description:

SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s 
    INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
    INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t 
                        INNER JOIN SR_Board b
                            ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

To add the schedule, you'd need to add a key field from the schedule table to the SR_Service table.

Related Question