title: Database schema design
body: <p>I'm quite new to database design and have some questions about best practices and would really like to learn.
  I am designing a database schema, I have a good idea of the requirements and now its a matter of getting it into black and white.</p>
  
  <p>In this pseudo-database-layout, I have a table of customers, table of orders and table of products.</p>
  
  <p><strong>TBL_PRODUCTS:</strong><BR>
  ID<BR>
  Description<BR>
  Details<BR></p>
  
  <p><strong>TBL_CUSTOMER:</strong><BR>
  ID<BR>
  Name<BR>
  Address<BR></p>
  
  <p><strong>TBL_ORDER:</strong><BR>
  ID<BR>
  TBL_CUSTOMER.ID<BR>
  prod1<BR>
  prod2<BR>
  prod3<BR>
  etc<BR></p>
  
  <p>Each 'order' has only one customer, but can have any number of 'products'.</p>
  
  <p>The problem is, in my case, the products for a given order can be any amount (hundreds for a single order) on top of that, each product for an order needs more than just a 'quantity' but can have values that span pages of text for a specific product for a specific order.
  My question is, how can I store that information?</p>
  
  <p>Assuming I can't store a variable length array as single field value, the other option is to have a string that is delimited somehow and split by code in the application.
  An order could have say 100 products, each product having either only a small int, or 5000 characters or free text (or anything in between), unique only to that order.</p>
  
  <p>On top of that, each order must have it's own audit trail as many things can happen to it throughout it's lifetime.
  An audit trail would contain the usual information - user, time/date, action and can be any length.
  Would I store an audit trail for a specific order in it's own table (as they could become quite lengthy) created as the order is created?</p>
  
  <p>Are there any places where I could learn more about techniques for database design?</p>