0

I am trying to make a small accounting db and I have hit a dead end, my search bared no results as of how to make this idea work.

In the picture below you can see that I am trying to link multiple tables to one table CreditDebit in this case, so I can use CreditDebit's ID (CD_ID) in the BondDetails table as the Creditor_CD_ID and Debtor_CD_ID enter image description here

what I am trying to achieve is to have correct structure so that I could create a query that would tell me which table the creditor or debtor belong to based on their id and show their name (box_name, emp_name, customer_name)

NOTE: CreditDebit table would have only one of the three ids (box_name, emp_name, customer_name)

1
  • An example would be something like this: CD_ID = 1 is a safebox, it doesn't have an employee id or customer id. CD_ID = 2 is a customer, they doe't have an employee id or safebox id.
    – Hoshani
    Feb 10 '19 at 13:30
1

If you are concerned about multiple nulls in your CreditDebit, you may consider change the table to the following

create table CreditDebit (CD_ID int primary key, ref_id int, ref_entity varchar(20));

Here ref_id can refer to box_id/emp_id/customer_id depending on ref_entity column, which can have values like 'box', 'employee', 'customer'. The disadvantage of this design is there is no way to create the foreign key relationship between CreditDebit` and the other three tables. However, you can implement the data integrity in the application layer (if you do not want to use trigger).

0

You need only make transactions have two different owners: Creditor and Debtor. It has other uses. You can walk the transactions owned by any creditor to find out who owes them money and you can walk the transactions under any debtor and find the other owner to reveal all the creditors they owe money to.

1
  • 1
    Could you clarify it in detail please
    – Hoshani
    Feb 10 '19 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.