title: How can I model an optional "return ticket" in a database?
body: <p>Here is a general layout of my tables:</p>
  
  <ol>
  <li>I have an <code>order</code> table that contains one or more reservations.</li>
  <li>I have a <code>reservation</code> table that is connected to a <code>train</code> and one or more <code>tickets</code>.</li>
  <li>I have a <code>train</code> table which holds the information for a train, and a <code>ticket</code> table that does the same for tickets.</li>
  </ol>
  
  <p>What I want to add to the system is the concept of return tickets, where the user asks for a two-way ticket. Since these tickets have two seat and ticket numbers and such, I will have to create new tickets. The same holds true for a train; since the information is different for the return train, I will have to create two trains: one for the originating train, and the other for the return train.</p>
  
  <p>This leads me to conclude that these are, in fact, two different reservations (they need two calls to the underlying API, as well). So in other words, when someone buys a two-way ticket, I need to create two reservations, which are connected to two different trains, which connect to the same tickets as the originating reservation.</p>
  
  <p>The problem begins when I want to connect these two reservations together. I'm looking for a strategy here that would allow me to know what kind of reservation (origination versus return) this reservation is, and what the other reservation is. For example, if the current reservation has an id of <code>4</code> and is an origination reservation, I want to be able to find the reservation with the id of <code>5</code> which is the return reservation.</p>
  
  <p>Here are what I thought and the problem with each one.</p>
  
  <h2>Method One: <code>originating_reservation_id</code> and <code>return_reservation_id</code></h2>
  
  <p>This is the easiest, but I think the worst, solution. In this case I will add these two columns to the <code>reservation</code> table. If <code>originating_reservation_id</code> is filled, then this <code>reservation</code> is a <code>return</code> reservation, and otherwise this is the <code>origination</code> reservation. The obvious downside to this is the fact that you get a lot of <code>null</code> values for one-way tickets.</p>
  
  <h2>Method Two: a Middle Table</h2>
  
  <p>In this scenario, I will have a table that has a <code>type</code> field, a <code>first_reservation_id</code> and a <code>second_reservation_id</code>. The types could be <code>origination</code> which means <code>first_reservation_id</code> is the origination, and <code>second_reservation_id</code> is the return. I will have another row in this table too, which is the opposite, meaning for each two-way ticket, two rows are created in this table. Apart from the fact that I need two rows for each two-way reservation, the layout seems unintuitive.</p>
  
  <p>What am I missing here?</p>