title: Database Design with regard to my business logic
body: <p>i am building an invoicing application consisting of following business logic.</p>
  
  <blockquote>
    <p><strong>a)</strong> Place a new order for customer. (an order is a group of three
    related components, estimate, invoice and purchaseorder)</p>
    
    <p><strong>b)</strong> After placing an order a new estimate can be generated. an order
    will have only one estimate.(An estimate consist of item details)</p>
    
    <p><strong>c)</strong> With reference to an estimate of the order. an invoice can be
    generated. an invoice qualify for the discount of price. apart from
    item details, an invoice consist of some expenses.an order can contain
    only one invoice</p>
    
    <p><strong>d)</strong> with reference to an invoice of the order, PurchaseOrder can be
    generated. PurchaseOrder consist of item information about vendor
    purchase. an order can contain multiple PurchaseOrder.</p>
  </blockquote>
  
  <p>here is the database table design i have come up with.</p>
  
  <p><img src="https://i.stack.imgur.com/4aDiW.png" alt="enter image description here"></p>
  
  <p>while all look good, i am having difficulty deciding where to store the items list that belongs to particular estimate, invoice or purchaseorder of the order.</p>
  
  <p>i had thought of several solution.</p>
  
  <blockquote>
    <p><strong>Approach A :</strong> create different tables for item list for each. (estimate, invoice and purchase order)<br/>
    <strong>table :</strong> <code>estimate_item</code> , <code>invoice_item</code> , <code>purchaseorder_item</code>.(this tables contains columns similar to that of
    order_item in above image).<br/>
    <strong>problem:</strong> problem with this approach is all the three tables consist of identical columns storing identical information, the only
    difference is foreign key that will be stored.</p>
    
    <p><strong>Approach B:</strong> create one item list table <code>order_item</code><br/>
    <strong>tablename:</strong> <code>order_item</code><br/>
    <strong>problem:</strong> not sure what to store as foreign key in this table since the foreign key can be from three different table. i thought of few
    way of handling foreign keys in this table as follows.</p>
    
    <p><strong>1)foreignKey table reference column:</strong> type (example values: estimate, invoice, purchaseorder) foreignKey column: type_id(consist
    of foreignKey of any of three tables)<br/> <strong>problem:</strong> i am using naming
    convention for column names for example column name ending with <code>tablename_id</code> defines the foreign key. and this method violate the rules.</p>
    
    <p><strong>2) foreignKeyColumn:</strong> <code>order_id</code> , <code>estimate_id</code> , <code>invoice_id</code> ,
    <code>purchaseorder_id</code>.<br/> <strong>problem:</strong> unnecessary foreign key columns
    defined.</p>
  </blockquote>
  
  <p>i want to know how should i store the foreign key in <code>order_item</code> table so that it identifies the order and estimate/invoice/purchaseorder it belongs too.</p>
  
  <p>the relationship for tables are:</p>
  
  <blockquote>
    <p><code>id</code> is the primary key for all the tables</p>
  </blockquote>
  
  <pre><code>table name: order relates to (contact, estimate, invoice, shipment) tables.
  column name: contact_id (foreign key(referring to id column of the contact table)).
  column name: estimate_id (foreign key(referring to id column of the estimate table)).
  column name: invoice_id (foreign key(referring to id column of the invoice table)).
  column name: shipment_id (foreign key(referring to id column of the shipment table)).
  
  tablename: purchaseorder (this have one to many relationship with order table)
  column name: order_id (foreign key(referring to id column of the order table)).
  column name: contact_id (foreign key(referring to id column of the contact table)).
  </code></pre>
  
  <p>the question is about how to go with the storing of foreign keys in order_item table.</p>
  
  <p>Thank you.</p>
  
  <p><strong>Update 1:</strong></p>
  
  <p>Please note that each table <code>estimate</code> , <code>invoice</code>  and <code>purchaseorder</code> will have item of it's own and having no relation to each other.</p>