optimizing database design for maximize redundancy of data and to increase accessibility of information

Question

I have the following tables - client Invoice. - Workshop invoice. - Owner invoice. - Owner withdraws money. - payment

Relationship with payment

• client Invoice: one invoice can have many payments --> one to many relation.
• Workshop invoice: one invoice can have many payments --> one to many relation.
• Owner invoice: one invoice can have many payments --> one to many relation.
• Owner withdraws money: one invoice can have one payment--> one to one relation.

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.

owner withdraw table

• owner_Withdraw_ID Primary int(11)
• user_ID Primary Index int(11)
• payment_ID
• Primary Index int(11)
• Date_Added date
• amount int(11)
• Description varchar(255)

client Invocie table

• IDIndex int(11)
• row_IDPrimary int(11) AUTO_INCREMENT
• product_ID Primary Index int(11)
• client_ID Primary Index int(11)
• quantity_of_Product int(11)
• Description varchar(600)
• Date_Added date
• VAT int(11)
• discount int(11)
• total_Price int(11)

invoice owner table

• invoice_O_ID Primary int(11) AUTO_INCREMENT
• user_ID Primary Index int(11)
• substance_ID Primary Index int(11)
• direction varchar(30)
• Invoice_Date date
• Quantity int(11)
• price int(11)
• VAT int(11)
• Description varchar(255)

invoice workshop

• IDPrimary int(11)
• workshop_ID PrimaryIndex int(11)
• Description varchar(1000) utf8_general_ci
• total_Price int(11)
• Date date
• VAT int(11)
• discount int(11)

payment table

• payment_ID int(11)
• row_ID Primary int(11) AUTO_INCREMENT
• workshop_Invoice_ID Index int(11) FK from workshop Invoice
• client_Invoice_ID Index int(11) FK from client Invoice
• owner_Invoice_ID Index int(11) FK from owner Invoice
• owner_Withdraw_ID Index int(11) FK from owner withdraw money
• payment_IN tinyint(1) if true money in the system else money out of system
• amount_Paid int(11)
• statues tinyint(1) true when paid total money
• Date_Added date
• Description varchar(600) utf8_general_ci

Answer 1

Your scenario seems to describe an operational system - essentially a sales application.
Your central entity/table is a sale. Each sale has line items - with description, quantity, price and VAT.

An invoice is also an entity, but it is not the same as sale, an invoice is issued and presents the sale to different parties - workshop, client, owner. You need to keep track of the sale in a separate table - imagine controlling a refund across your invoices.

So, I would start with a high level sale table

date, 
sale_id, 
user_id (user who made the sale), 
discount,
total_vat,    
total_price.

Then, create a second table sale_item

date,
sale_id,
item_id,
quantity,
substance,
descritption,
vat,
cost,
price,
discount.

If a sale changes, say the client wants to refund something, then you would only need to change two tables to capture this event. I would then create a table for the invoice

 - date,
   *invoice_id, 
   sale_id,* --PRIMARY KEY on this table 
   invoice_type, --client,owner,workshop 
   user_id.

For the same sale_id in table invoice, you could have up to three invoice_ids.

This is how I would approach this situation - obviously add and remove columns you need to describe the entities and events. Essentially, an invoice describes a sale in different ways.