Sql-server – multiple ids table for accounting schema

database-designMySQLsql server

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)

Best Answer

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

Related Question