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
然后我有不同的表格用于各种操作:发票,银行操作,Cashdesk操作。我需要将付款与发票配对。付款不是发票的确切金额,但可以在每个发票数量上进行拆分。此外,发票可以分多次付款。付款可以来自银行或现金支付业务
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.
我最初的方法是有一个表,PaymentRelations,外键InvoiceID,BankOpID,CashOpID和Amount,并且对于它们之间的任何支付,我创建一个只有两个外国ID填写的记录,以及相应的金额。这种方式在任何时候我都可以知道每项操作(发票或付款)支付了多少。
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).
还有RI要求,因此如果文档涉及支付关系,则无法删除(或者有级联删除,因此如果删除了发票文档的付款,则删除相关的PaymentRelations记录,因此释放对应的操作 - 他们不再参与支付关系,因此他们的金额可以充分用于其他支付关系)。
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).
但出现了另一种情况。由于合作伙伴既可以是客户也可以是供应商,因此可以在同一合作伙伴的客户和供应商方面进行相同类型的操作(例如,合作伙伴既是客户又是供应商,他作为100的供应商开具发票并收到发票作为150的客户,50在收到的和发送的发票之间得到补偿,其余的通过一次或多次付款操作支付。这也可能发生在其他操作中(例如,他通过银行操作100支付,他通过另一个银行操作200接收,并且50需要在这两个操作之间得到补偿;同样适用于caskdesk操作)。
What approach would you use to model this kind of relations?
你会用什么方法来模拟这种关系?
1 个解决方案
#1
3
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.
Bitfields是识别客户和供应商的错误方式。这个SO答案应该让您了解客户和供应商的问题。
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.
如果我必须设计一个会计系统,我想我会从一个电子表格开始。我在该电子表格中设计了一个交易表,因此我可以了解某些交易是如何相似的,以及其他交易是如何不同的。在这个阶段,我不会担心NULL,重复组,传递依赖关系或其他任何类似的东西。
Having developed a working(ish) model in the spreadsheet, I'd then try to normalize it to 5NF.
在电子表格中开发了一个工作(ish)模型后,我会尝试将其标准化为5NF。
#1
3
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.
Bitfields是识别客户和供应商的错误方式。这个SO答案应该让您了解客户和供应商的问题。
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.
如果我必须设计一个会计系统,我想我会从一个电子表格开始。我在该电子表格中设计了一个交易表,因此我可以了解某些交易是如何相似的,以及其他交易是如何不同的。在这个阶段,我不会担心NULL,重复组,传递依赖关系或其他任何类似的东西。
Having developed a working(ish) model in the spreadsheet, I'd then try to normalize it to 5NF.
在电子表格中开发了一个工作(ish)模型后,我会尝试将其标准化为5NF。