CODEWITHSUNDEEP
sqllinqdatabase-designrdbms
maztt
maztt

Reputation: 12294

How can I integrate bill and invoice in seperate tables or into one single payment table or two?

I have a bills table. bill provided by the vendor

I have a invoice table. bill provided to the customer

How can i link it with payment? .Bill will be the record of the payment made to vendor whereas invoice will be the payment received from the customer.

option1 : Create one payment table with billid and invoice id
option2 : create two payment table paymentinvoicedetails and paymentbillsdetails with paymentmaster

What could be the better option or there can be a third option? The reason i am asking it because more or less there fields are almost same with two or three fields different

Upvotes: 1

Views: 364

Answers (2)

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

This is entirely based on your business requirements.

If both Vendor Payment and Customer Receipts are different properties/column or different child tables, or tax/discount related tables then my suggestion is, create separate table for each payment (paymentinvoicedetails ,paymentbillsdetails ). Then you can properly manage the Foreign Key relation to both the table as well as other related Master/Child tables also.

But, if the properties almost same and there is no different Foreign Key masters, then you can create a single payment table with relation to both bill (billid) and invoice (invoiceid) tables and a column payment_type (not mandatory, you can simply identify the payment mode by considering the billid/invoiceid is NULL) to mark the record as whether it is Vendor Payment/Customer Receipt.

Upvotes: 1

Carlos R
Carlos R

Reputation: 205

Creating one payment table with bill-id, invoice-id, the amount payed and a transaction type is enough.

Upvotes: 0

Related Questions