How to properly design a database structure?

Question

MySQL

I use MS SQL 2014
There is a theme - Sale of railway tickets
Tickets are sold in this way. The client requests the availability of tickets for a specific date and direction, from the starting to the final station, additionally indicating the desired type of carriage (compartment, reserved seat, and so on). The cashier searches the database for the specified attributes and informs the client of the possible options, which are blocked until the end of the operation. The client chooses a ticket from the possible options, after which the cashier enters the data into the database and receives money for the ticket.
The data entered by the cashier enters the database in the central office, which makes it impossible to sell the same ticket to different passengers.
If the ticket is returned, the cashier returns the money to the client (with the calculation of a certain amount), and takes the issued ticket, after which he marks this ticket as unoccupied.
The question is that it is assumed that each train (in the table of trains) corresponds to a route. If the direction requested by the client (starting and ending station) is in the route of a certain train, but is not the first and last station of this route, this train must still be offered to the client and it must be taken into account that the same stations may also be in the route of another train , but the train goes backwards from the final to the initial requested by the client, in this case, this train should not be offered. In addition, it is necessary to calculate the cost of the ticket, depending on the number of stations that the client passes.
Please tell me how to correctly write down different routes for trains and how to calculate the amount.

Answer 1

well, everything looks simple in the forehead - for each train, store the route (at least from the nodal points), the train search will consist in selecting all trains whose start is earlier or equal and the finish is further or equal to the ordered route. Do I understand correctly that the train "there" and "back" are 2 different trains? and different routes (albeit mirror ones)?
waypoints between nodes can be stored separately and the
cost of the ticket can be joined on the go - as an option, this is the sum of the costs for each of the "hauls". although the scale is usually not linear.

Answer 2

build a graph with analytics: station , station from which you can get to this (parent), fare from the parent , train number, and analytics con / not con .... and just recursively join .. sum the cost and get the path + cost

Answer 3

1. Each segment of the route must have a fare. So count.
2. Easier than ever, - boolean value. There and back - two meanings.