CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-databaseschema
aziz
aziz

Reputation: 336

Multiple reference to one foreign key

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.

Upvotes: 2

Views: 123

Answers (1)

Korteby Farouk
Korteby Farouk

Reputation: 649

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

Option 1

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

Option 2

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;

Upvotes: 1

Related Questions