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)

  • Looks ok to me. Every BondDetail is related to two CreditDebits (one through Creator_CD_ID and another through Debtor_CD_ID). You can find the Creditor and Debtor through the respective Customer_ID. – ypercubeᵀᴹ Feb 10 at 10:31
  • creditdebit tavke would have lots of nulls which doesn't look right that is why I am asking – Hoshani Feb 10 at 11:16
  • Why would there be nulls? Will a BondDetail sometimes have a Creditor and sometimes a Debtor? It would help if you edited the question and added the relationships between the entities of the schema. E.g. a Bond has many BondDetails, a BonDetail has a single Creditor and a single Debtor (or sometimes no Debtor), etc... – ypercubeᵀᴹ Feb 10 at 12:43
  • I am talking about CreditDebit table – Hoshani Feb 10 at 13:03
  • OK. Why would there be nulls there? Which columns would be nullable? – ypercubeᵀᴹ Feb 10 at 13:06

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
    Could you clarify it in detail please – Hoshani Feb 10 at 11:24

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).

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.