title: How does Master/Detail work?
body: <p>I have found some tutorials, but they still leave me with questions.</p>
  
  <p>Let's take a classic example of 2 tables, one for customer details and one for order details.</p>
  
  <p>The <code>customers</code> table in the database has:  </p>
  
  <ul>
  <li>an autoincrementing integer <code>customer_id</code> as primary key  </li>
  <li>a text field for customer name  </li>
  <li>a text field for contact details  </li>
  </ul>
  
  <p>And the <code>orders</code> table has:  </p>
  
  <ul>
  <li>an integer <code>customer_id</code> which is a foreign key referencing the <code>customers</code> table</li>
  <li>some other stuff, such a reference to a bunch of item numbers</li>
  <li>an integer <code>order_value</code> to store the cash value of the order</li>
  </ul>
  
  <p>I need two dataset components, two queries and a connection.</p>
  
  <p>So far, so good? Or did I miss something already?</p>
  
  <p>Now, the tutorials say that I have to set the MasterSource of the  of the datasource which coresponds to the DB grid showing the <code>orders</code> table to be the datasource which corresponds to the <code>customers</code> table and the <code>MasterFields</code>, in this case, to <code>customer_id</code>.</p>
  
  <p>Anything else? Should I for instance set the <code>Detailfields</code> of the query of the datasource which corresponds to the <code>customers</code> table to <code>customer_id</code>?</p>
  
  <p>Should I use the properties, or <a href="http://docwiki.embarcadero.com/RADStudio/XE3/en/Establishing_Master-detail_Relationships_Using_Parameters" rel="nofollow">a paramaterized query</a>?</p>
  
  <p>Ok, at this point, we have followed the classic tutorials and can scroll through the <code>customers</code> DB grid and see all orders for the current customer shown in the <code>orders</code> DB grid. When the user clicks the <code>customers</code> DB grid I have to Close(); then Open(); the <code>orders</code> query to refresh its corresponding DB grid.</p>
  
  <p>However, those tutorials always seem to posit a static database with existing contents which never change.</p>
  
  <p>When I asked anothter question, I gave an example where I was using a Command to <code>INSERT INTO orders...</code> and was told that that is A Bad Thing` and I should:  </p>
  
  <ul>
  <li>OrdersQuery.Append();</li>
  <li>OrdersQuery.FieldByName('customer_id') := [some value]';  </li>
  <li>OrdersQuery.FieldByName('item_numbers') := [some value]';</li>
  <li>OrdersQuery.FieldByName('order_value') := [some value]';</li>
  <li>OrdersQuery.Post();</li>
  </ul>
  
  <p>Is that correct? </p>
  
  <p>I ask because it seems to me that a Command puts data <strong>in</strong> and a query should only take it <strong>out</strong>, but I can see that a command doesn't have linkage to the DB grid via a datasource's query.</p>
  
  <p>Is this a matter of choice, or <em>must</em> the query be used?</p>
  
  <p>If so, it seems that I can't use even simple SQL functions such as SUM, MIN&lt; AVG, MAX in the query and have to move those into my code.</p>
  
  <p>If I must use the query, how do I implement SQL <code>UPDATE</code> and <code>DROP</code>?</p>
  
  <p>And, finally, can I have a Master/Detail/Detail relationship?</p>
  
  <p>Let's say I want a 3rd DB grid, which shows the total and average of all orders for a customer. It gets its data from the <code>orders</code> table (but can't use SUM and AVG) which is updated each time the user selects a different customer, thus giving a Master/Detail/Detail relationship. DO I just set that up as two Master/Detail relationships? I.E, the DB grid, datasource, query for the total and average orders refers only to <code>orders</code> and has no reference to <code>customers</code>, even if it does use <code>customer_id</code>?</p>
  
  <p>Thanks in advance for any help and clarification. I hope that this question will become a reference for others in the future (so, feel free to edit it).</p>