title: Sales order item details storage, which way to go?
body: <p>I'm working on a type of inventory management application using the following mixture or technologies: MySql, C#, WPF, ADO.Net EntityFramework 4.</p>
  
  <p>I'm a little confused as to how I should store the details on the sales/purchase invoices in the database.</p>
  
  <p>This is how I had it before... I stored the details in a LongText field as an XML string.</p>
  
  <pre><code>+---------------------------------------------------+
  | SalesInvoices                                     |
  +---------------------------------------------------+
  | int InvoiceID: primary key, auto-incretement      |
  | int OrderID                                       |
  | int CustomerID                                    |
  | int CreatedByEmployeeID                           |
  | DateTime Date                                     |
  | DateTime? ShippingDate                            |
  | LongText ItemsData (details, stored as XML)       |
  | LongText TransactionData (details, stored as XML) |
  | Double Subtotal                                   |
  | Double Tax                                        |
  | Double Freight                                    |
  | Double FreightTax                                 |
  | Double Total                                      |
  +---------------------------------------------------+
  </code></pre>
  
  <p>However, I looked around on the web and most of the examples I've seen have a separate table <code>SalesInvoiceDetails</code>. So I'm wondering, is there something wrong with my approach, should I go through the trouble of switching over to the approach.</p>
  
  <p><strong>My Requirements:</strong> The search through the <code>SalesInvoice</code> table should be really fast. I do not need to search through the details of the invoice unless the user actually opens up an invoice, so It's okay if the user has to wait a second or two over here. </p>
  
  <p>The reason I chose my approach the first time was because I thought millions of details in one table will eventually make it really slow to open and I would have to worry about deleting and updating rows if a user decides to go back to change something. And I thought it would be a bit of a hassle to have sub items for detail lines and storing them as rows and keeping track of the parent/children relationship and etc.</p>
  
  <p>So yea, I'm wondering is there any good reason why I should abandon my approach and make another table for the details.</p>