axtg
1 year ago

Bridging users, companies and transactions

Posted 1 year ago by axtg

Okay, so I've tried my best to search the web for a proper answer, but have not been able - due to a lack of the proper terms I assume - to find the best solution. Here is my situation, I hope someone can help me in the right direction.

I have users, companies, transactions and balances tables. Users can make transactions to companies or to others users.

Ideally therefore I'd like to have a transactions table that looks something like:

id, entity_id, amount, type(credit, debit) and timestamp
1,1,100,"C",2018-01-01
2,1,20,"D",2018-02-01
3,2,20,"C",2018-02-01
4,1,10,"D",2018-03-01
5,3,10,"C",2018-03-01

Here:

  • Entity_id 1 top-ups (Credit) 100 on 01-01-2018
  • Entity_id 1 transfers (Debit) 20 on 01-02-2018
  • Entity_id 2 receives (Credit) 20 on 01-02-2018
  • Entity_id 1 transfers (Debit) 10 on 01-03-2018
  • Entity_id 3 receives (Credit) 10 on 01-03-2018

Where entity_id refers to (e.g.) an entities table that looks like:

id, user_id, company_id
1,1,NULL
2,NULL,1
3,2,NULL

Now my questions:

  1. The entities table feels "weird" to me (because of the NULLs), is this the best way?
  2. I am unsure how to get the Collection of all transactions for user_id=1 (e.g. App\User::find(1)->transactions->get())
  3. However 2 is done, could I then still chain a transactions_details table if present?

Perhaps 2 and 3 is what the "Polymorphic Relations" are for, but the documented example of Posts, Users, Comments and Tags didn't speak to me for my situation.

Thank you, Xander.

Please sign in or create an account to participate in this conversation.