Multiple reference to one foreign key

Question

In my project, there is income and expense. I have many business and assets. Every business and assets has income and expense. Beside this i have some general income like salary, bonus etc. also. So finally I make 6 table,

1. business, 2. assets, 3. catagory, 4. income, 5. expense, 6. accounts

+----------+---------+    +--------+---------+  +----------+---------+ +----------+---------+ 
| Business | Name    |    | Assets | Name    |  | catagory | Name    | | account  | Name    |
+----------+---------+    +--------+---------+  +----------+---------+ +----------+---------+
| 1        |Business1|    | 1      | Land1   |  | 1        | profit  | | 1        | Bank1   |
| 2        |Business2|    | 2      | Land2   |  | 2        | salary  | | 2        | Bank2   |
| 3        |Business3|    | 3      | Land3   |  | 3        | Rent    | | 3        | Cash    |   
+----------+---------+    +--------+---------+  +----------+---------+ +----------+---------+

The financial Transaction could be happen from either business, or asset or general and each have category, or may transfer balance from account to account. like below

+--------+---------+---------+---------+---------+                             
| Income | From    | Catagory| Account | Amount  |             
+--------+---------+---------+---------+---------+                         
| 1      |Business1|Profit   |Bank1    |  1000   |                 
| 2      |Land1    |Rent     |Bank2    |  500    |                 
| 3      |General  |Salary   |Cash     |  700    |
| 4      |Transfer |Null     |Bank2    |  500    |
+--------+---------+---------+---------+---------+

and so expenses

+--------+---------+---------+---------+---------+                             
| Expense| From    | Catagory| Account | Amount  |             
+--------+---------+---------+---------+---------+                         
| 1      |Transfer | Null    | Bank1   |  500    |                 
| 2      |Land1    | Mainta..| Bank2   |  200    |                 
| 3      |General  | Food    | Cash    |  700    |
| 4      |Assets   | Invest  | Bank1   |  1000   |
+--------+---------+---------+---------+---------+

This Design have some serious problem, After insertion I can't track 'from' column from income or expense table. This field became ambiguous.

I have to prepare income statement,balance sheet, account summary, business and asset wise report and i can't figure it out how to deal with this database or how to fix those tables.

Please give any suggestion or idea to fix those table structure to get prepare my income statement and balance sheet.

Korteby Farouk · Accepted Answer · 2018-03-08T07:24:44.703

1

Option 1 (easy way) : Create a From field to differentiate the Assets and business

Option 2 (correct way): Create a From table to differentiate the Assets and business

The final query for option 2:

SELECT 
    IncomeID,
    IF(
        (SELECT COUNT(assets.assetsID) FROM assets WHERE Income.FromId = assets.FromId) > 0,
        (SELECT assets.name FROM assets WHERE Income.FromId = assets.FromId),
        (SELECT Business.name FROM Business WHERE Income.FromId = Business.FromId),
        ) AS From,
    Catagory.name,
    Account.name,
    Income.amount
FROM 
    Income, Catagory, Account 
WHERE 
    Income.CatagoryId = Catagory.CatagoryId
AND 
    Account.AccountId = Account.AccountId
ORDER BY 
    IncomeID, Income.amount;

edited Mar 08 '18 at 07:24

answered Mar 08 '18 at 06:37

Korteby Farouk

629
7
14

How could we reference one column to three different table? how can i find a income from which section business, or assets or general from income table? – aziz Mar 08 '18 at 06:57
If you talk about the second option : **Query : select sum of Incomes for 'Land1'** `SELECT SUM(amount) FROM Income, assets WHERE Income.FromId = assets.FromId AND assets.name = 'Land1'` – Korteby Farouk Mar 08 '18 at 07:07
In fact the name i choose is not well, you have to think that **From** table is like a parent for the two entities assets and business. – Korteby Farouk Mar 08 '18 at 07:10
You didn't got me. After insertion an entry on income table, if I need to edit that, how could i retrieve data for that particular income, i don't know is it asset or business or general. – aziz Mar 08 '18 at 07:13
1

Ah ok so in this case you can search the FromId in tables business, or assets i'll update the answer with the query – Korteby Farouk Mar 08 '18 at 07:24
Thanks Bro, hope your answer will solve the problem and I could accept it. – aziz Mar 08 '18 at 07:26
It should work inshallah ;) !! you can use this service we can collaborate on it : [sqlfiddle](http://sqlfiddle.com/) – Korteby Farouk Mar 08 '18 at 07:30
Brother, will you please look this discussion, i didn't got it out properly but may be this is the solution https://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables – aziz Mar 08 '18 at 09:25
His solution (his choice) is some what i suggested to you create an entity that acts as a base for both Business and Assets for my answer if was **From** table. – Korteby Farouk Mar 08 '18 at 09:40

Multiple reference to one foreign key

1 Answers1