0

There is a purchase_orders table, which holds purchase orders.

There is another table emp_payouts table, which holds payment to be paid to an employee and their details.

There is a payment_transactions table which holds payment transactions made to someone from the app, only outgoing payments.

Each entity (purchase order, employee payouts etc) can have multiple payment transactions.

How can I store transactions for both entities in that table?

I have two solutions in my mind but still I am in doubt.

  • Having purchase_order_id and emp_payout_id in payment_transactions table.

    In this case, one of the column will always hold null values

    payment_transactions
--------------------------
id    |    purchase_order_id   | emp_payout_id  | amount

  • Having pivot tables for both entities: purchase_order_transactions and emp_payout_transactions

    In this case, entities purchase orders and employee payouts are being associated with multiple transactions which is fine, but the same thing is also true for payment transactions. While, a payment transaction should only be related to one purchase order or employee payouts.

    purchase_order_transactions
--------------------------
id    |    purchase_order_id   | payment_transaction_id  


emp_payout_transactions
--------------------------
id    |    emp_payout_id   | payment_transaction_id
Tripti Muchhal
  • 94
  • 2
confusedWarrior
  • 938
  • 2
  • 14
  • 30
  • 2
    How about a single table of `payment_transactions` with columns like `id | payment_transaction_id | transactions_type | amount `? Is that workable for you? – FanoFN Jun 21 '21 at 04:32
  • @FaNo_FN So it will have a column `transaction_type`, but how they will be related to each entity? there will be `payment_transaction_id` in each table i.e. `purchase_orders` and `emp_payouts`? Or what? – confusedWarrior Jun 21 '21 at 04:43
  • 1
    I'm sorry but without the full table structure of `purchase_orders` and `emp_payouts` with a few sample data I'm afraid I'll misunderstand and suggest wrong things. Can you provide more details and maybe with a sample query that you want to work with? thanks – FanoFN Jun 21 '21 at 04:49
  • @FaNo_FN Any simple join query that will get transactions for particular entity. Two entity will be never joined together. It will be to fetch only transaction of particular entity at a time. `SELECT * FROM purchase_order t1 JOIN payment_transaction t2 ON t1.id =t2.purchase_order_id` something like this. – confusedWarrior Jun 21 '21 at 04:57
  • @FaNo_FN Your solution will work, but only I have this problem. *Each entity will have multiple transactions. How will I relate these transaction to concerned entity?* I can't put `purchase_order_id` `emp_payout_id` and `transaction_type` in `payment_transactions` again, one will be always `null`. I can't either put `payment_transaction_id` in each entity as they will hold multiple transactions. – confusedWarrior Jun 21 '21 at 05:03
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jun 21 '21 at 05:46
  • No it doesn't. How come it be a inheritance model? All the fields are same in both type of transactions. Only the `entity_id` is different. If purchase order transactions and employee payout transaction be two separate table which extends payment transactions, what values will they hold than their ids? – confusedWarrior Jun 21 '21 at 05:54
  • Ok, let's try this with a fiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=590c1943990c66f54972d4567e88a882 – FanoFN Jun 21 '21 at 06:32
  • @FaNo_FN That will work, thanks. Just a though, won't it be good to make `id` of the `payment_transaction` to be primary key of each entity rather than storing `transaction_id` . – confusedWarrior Jun 21 '21 at 14:46
  • Are you referring to `id` in the `payment_transactions`? Actually, I was thinking that `id` is a primary key while `payment_transaction_id` correspond to the `id` from both `purchase_order_id` and `emp_payout_id` tables. – FanoFN Jun 22 '21 at 00:44
  • Yes, but can it be good if `id` in the `payment_transactions` is the foreign key `purchase_order_id` and `emp_payout_id` ? – confusedWarrior Jun 23 '21 at 03:44

0 Answers0