title: Database schema for dynamic pricing and many variables - Booking system
body: <p>I got stuck with the following problem:
  For my booking system I want to save the prices for all possible combinations at night, so I don't have to calculate them on the fly.
  I choose for this solution to improve performance and for the fact that these prices only change one time per day, at night.
  My first try was going to be to save all possible combinations to the DB.
  But I quickly found out that this isn't efficient.</p>
  
  <p>A booking can vary from 1 adult to 9 adults and 9 children with each child having a varying age between 0 and 17.</p>
  
  <p>When a user searches for 1 adult I need to be able to return a price and also when a user searches for 2 adults and 3 children with the age of 2,10 and 15 I need to be able to return a price.</p>
  
  <p>This was my first approach, without considering the age of the children:</p>
  
  <pre><code>//Amount adults
          for($a = 1; $a &lt;= static::MAX_ADULTS; $a++) {
              //Amount children
              for($c = 0; $c &lt;= static::MAX_CHILDREN; $c++) {
                  $cheapestPriceObj = null;
                  //Add 1 day to arrival per iteration
                      for($i = 0; $i &lt; $dDiff-&gt;days; $i++) {
                          $arrival = strtotime(" + $i days",$this-&gt;_arrival);
                          $travelCompany = array(array('amount_adults' =&gt; $a, 'amount_children' =&gt; $c));
                          $priceInfo = $this-&gt;_curAcco-&gt;getListerPriceInfo($arrival, static::DEFAULT_DURATION,$travelCompany);
                          if($priceInfo) {
                              if(is_null($cheapestPriceObj)) {
                                  $cheapestPriceObj = $priceInfo;
                              } else if($priceInfo['price'] &lt; $cheapestPriceObj['price']) {
                                  $cheapestPriceObj = $priceInfo;
                              }
                          }   
                      }
                      //Save cheapest price and date for travelcompany
                      if($cheapestPriceObj &amp;&amp; $cheapestPriceObj['price'] &gt; 0) {
                          $default = new AccommodationRoomDefaultPrice;
                          $default-&gt;fkItemRoom = $cheapestPriceObj['rooms'][0]['itemRoom'];
                          $default-&gt;arrival_date = $cheapestPriceObj['arrival_date'];
                          $default-&gt;duration = $cheapestPriceObj['duration'];
                          $default-&gt;amount_adults = $a;
                          $default-&gt;amount_children = $c;
                          $default-&gt;price = $cheapestPriceObj['price'];
                          $default-&gt;save();
                      }
              }
          }
  </code></pre>
  
  <p>The Database schema is as follows:</p>
  
  <pre><code>id-fkItemRoom-arrival_date-duration-amount_adults-amount_children-price
  </code></pre>
  
  <p>But this misses the "age" part. And prices are also depending on the age.</p>
  
  <p>How do I solve this problem? I'm not just looking for an "answer". I would like to educate myself on how I solve big data challenges like this in general.</p>