CODEWITHSUNDEEP
sql-serverdatabase-designmodel
bzamfir
bzamfir

Reputation: 4906

Suggestion on database design - multiple tables involved into a relation

My application needs to implement a one to one relation between multiple tables. I have a table which store companies (which can be customers and suppliers, or both). There are twi Bit fields, Customer and Supplier.

Then I have different tables for various operations: Invoices, Bank operations, Cashdesk operations. An I need to pair payments with invoices. A payment is not exact amount of an invoice, but it can be split over each number of invoices. Also, an invoice can be split over multiple payments. Payments can be from both bank or cashdesk operations

My original approach was to have a table, PaymentRelations, with Foreign Keys InvoiceID, BankOpID, CashOpID and Amount, and for any payment between between them, I create a record with only two foreign ID's filled, and the corresponding amount. This way in any moment I can know for each operation (invoice or payment) how much was paid.

Also there are RI requirements, so if a document is involved in payment relation, it cannot be deleted (or there is cascade delete, so if a payment of invoice document is deleted, the related PaymentRelations records are deleted, so the counterpart operations are freed - they are no longer involved into payment relations so their amount can be fully used into other payment relations).

But appeared another situation. Since partners can be both customers and suppliers, it is possible to compensate between same type of operation on customer and supplier side of the same partner (e.g. a partner is both customer and supplier, he made an invoice as supplier for 100 and received an invoice as customer for 150, 50 was compensated between the received and the sent invoice and the rest of each is paid through one or multiple payment operations). This can also happen for the other operations (e.g. he paid through a bank operation 100, he received through another bank operation 200, and 50 needs to be compensated between those two operations; same apply for caskdesk operations).

What approach would you use to model this kind of relations?

Upvotes: 2

Views: 553

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95751

I would buy accounting software instead of writing it. Some wheels are worth reinventing; this isn't one of them.

But if you must . . .

Bitfields are the wrong way to identify customers and suppliers. This SO answer should get you over the issues with customers and suppliers.

If I had to design an accounting system, I think I'd start with a spreadsheet. I'd design a table of transactions in that spreadsheet, so I could get the feel of how certain transactions were alike, and how others were different. At this stage, I wouldn't worry about NULLs, about repeating groups, about transitive dependencies, or anything else like that.

Having developed a working(ish) model in the spreadsheet, I'd then try to normalize it to 5NF.

Upvotes: 3

Related Questions