title: optimizing database design for maximize redundancy of data and to increase accessibility of information
body: <p>I have the following tables
   - client Invoice.
   - Workshop invoice.
   - Owner invoice.
   - Owner withdraws money.
   - payment</p>
  
  <h2>Relationship with payment</h2>
  
  <ul>
  <li>client Invoice: one invoice can have many payments --> one to many relation.</li>
  <li>Workshop invoice: one invoice can have many payments --> one to many relation.</li>
  <li>Owner invoice: one invoice can have many payments  --> one to many relation.</li>
  <li>Owner withdraws money: one invoice can have one payment--> one to one relation.</li>
  </ul>
  
  <p>The payment table will include all data of payments (huge amounts of data). the problem with the current design is that when a payment is created for any type of invoice their will be 2 unused columns referencing the other invoice tables. 
  an obvious enhancement is removing all the FK keys from the payment table then add to the tables paymentID as an FK.  this will lead to huge repetition due to placing the FK in a "one to many relation" in the parent table.
  My current design is included below.</p>
  
  <h2>owner withdraw table</h2>
  
  <ul>
  <li>owner_Withdraw_ID Primary int(11)</li>
  <li>user_ID Primary Index int(11)</li>
  <li>payment_ID</li>
  <li>Primary Index int(11)</li>
  <li>Date_Added date</li>
  <li>amount   int(11)</li>
  <li>Description varchar(255)</li>
  </ul>
  
  <h2>client Invocie table</h2>
  
  <ul>
  <li>IDIndex  int(11)</li>
  <li>row_IDPrimary int(11) AUTO_INCREMENT</li>
  <li>product_ID Primary Index int(11)</li>
  <li>client_ID Primary Index  int(11)</li>
  <li>quantity_of_Product  int(11)</li>
  <li>Description  varchar(600)</li>
  <li>Date_Added date</li>
  <li>VAT                                          int(11)</li>
  <li>discount                                 int(11)</li>
  <li>total_Price                                  int(11)</li>
  </ul>
  
  <h2>invoice owner table</h2>
  
  <ul>
  <li>invoice_O_ID     Primary             int(11) AUTO_INCREMENT</li>
  <li>user_ID          Primary     Index   int(11)</li>
  <li>substance_ID     Primary     Index   int(11)</li>
  <li>direction            varchar(30)</li>
  <li>Invoice_Date     date</li>
  <li>Quantity         int(11)</li>
  <li>price                int(11)</li>
  <li>VAT                  int(11)</li>
  <li>Description          varchar(255)</li>
  </ul>
  
  <h2>invoice workshop</h2>
  
  <ul>
  <li>IDPrimary                                int(11)</li>
  <li>workshop_ID          PrimaryIndex    int(11)</li>
  <li>Description                              varchar(1000)   utf8_general_ci</li>
  <li>total_Price                              int(11)</li>
  <li>Date                                 date</li>
  <li>VAT                                      int(11)</li>
  <li>discount                             int(11)</li>
  </ul>
  
  <h2>payment table</h2>
  
  <ul>
  <li>payment_ID                                       int(11)</li>
  <li>row_ID                       Primary             int(11)         AUTO_INCREMENT</li>
  <li>workshop_Invoice_ID          Index               int(11)         FK from workshop Invoice</li>
  <li>client_Invoice_ID            Index               int(11)         FK from client Invoice</li>
  <li>owner_Invoice_ID         Index               int(11)         FK from owner Invoice</li>
  <li>owner_Withdraw_ID            Index               int(11)         FK from owner withdraw money</li>
  <li>payment_IN                                       tinyint(1)      if true money in the system else
  money out of system</li>
  <li>amount_Paid                                      int(11)</li>
  <li>statues                                          tinyint(1)      true when paid total money</li>
  <li>Date_Added                                       date</li>
  <li>Description  varchar(600)                        utf8_general_ci</li>
  </ul>