Database design: self referencing vs separate tables

database-design

So, i have 1 table which contain payments with different type

id| reference_id | amount | type
1 | null         | 200    | debit
2 | 1            | 25     | refund
3 | 1            | 175    | refund
4 | null         | 0      | regist
5 | 4            | 100    | debit

as you can see, this is one entity named payment, each row MAY has relation to another row,

There is another way to represent this data by separating each type to their own tables. I will need debit_payment, refund_payment, regist_payment, and so on.

Which one is the best for this ? especially when we need to show list of the payment, and we also need to get their relation.

Best Answer

but there is another suggestion that i should separate each type to their own tables, so i will need debit_payment, refund_payment, regist_payment, and so on

You should use two tables because in this case, the "payment" is a transaction. And though the transaction includes a ledger entry for credit and debit those are two separate things. Further there isn't anything below (more atomic or subordinate to) the ledger entry.

  • A transaction is a batch of ledger entries in which the sum of those entries is 0. It requires no other information.
  • A ledger entry is attached to an transaction and one account, and exists only inside a transaction.

Here is an example,

CREATE TABLE transaction ( id int PRIMARY KEY );

CREATE TABLE ledger (
  ledger_id int PRIMARY KEY
  txn_id    int REFERENCES transaction,
  account   int REFERENCES account,
  amount    numeric(16,2)
);
Related Question