Online credit system table layout for external transaction addresses

Question

I am trying to figure out how to store foreign keys correctly when the key points to another table based on type.

I search but nothing I found seemed to help at all .

There is one table that will store most of the basics for loans:

user_accounting -------------------- user_accounting_id (PK) user_id (FK) amount type (+deposit, +credit, -transfer, -purchase) credit (bool, so I don't have to always check if amount is < or > 0) void (in case it was canceled and repaired with some other transaction) date details (notes) 

type s:

 deposit - for outsite money being put into the system. credit - are for money being transfered into their account from another transfer - for putting money into someone elses account purchase - for purchasing a site item 

So far so good. In this next installment, I'm a little stupefied. He will also need to keep where money is collected or arrived.

I would like to keep the foreign key for any type . So if this is a purchase, then it will store the FK for invoice_id, if it will keep a deposit, it will store the transaction from the seller, if it is a transfer, it will store the user_accounting_id of the loan, if it is a loan, save the user_accounting_id of the transfer.

I would be pleased to have one column that stores this:

 user_accounting (con't) ----------------------------- source_or_destination_id (FK) 

But I know that I cannot have one column, which is a foreign key associated with different tables based on type . So I could just save it as (int), but it would be a huge pain trying to do JOINs with this id with different tables based on type . Tried to do it a long time ago, a big mistake.

So instead, I could do this instead:

 user_accounting (con't) ----------------------------- invoice_id (FK) transaction_id (FK) credit_user_accounting_id (FK) transfer_user_accounting_id (FK) 

But again, that is a big problem, as I create an exclusive arc that is not good.

I could also use the many_to_many_through relation for the type , but the pivot table will still have the same problem of storing foreign keys in one place for multiple tables.

Perhaps I could just store the different types of transactions in different tables as a whole, I could:

 user_accounting_deposit, user_accounting_credit, user_accounting_transfer, user_accounting_purchase. 

And then foreign keys will not be a problem. Of course, in order to find out what account balance I have, I will need to do the joins and amounts from the heap of tables.

Perhaps there is a completely different best way to do this. I don't care how many tables are required. Most likely, I'm complicating something.

thanks

Answer 1

Accountants are engaged in the storage of where the money comes from and where the money goes for more than 500 years. That's why they came up with a double account. Otherwise, this is called dual registration.

You need to look at the data model for two-step accounting. This has been done thousands of times by the programmers who were in front of you.

If you need a sample model, visit the answers to the databases and look in the "Accounting Systems" section. You should be able to get a model diagram that covers your case.