1

considering an app for online consulation and managing patients. there are 3 types of transactions. 1- users/doctors can withdraw, or deposit money in their wallet 2-users can pay for online consulations . 3- doctors can pay for their account (account has different plans).

now I want to design a transaction table for saving details after payment. should I have the same table for all of these and make columns like transaction_type and transaction_user_type to understand who has paid (the doctor or user) and understand what transaction it is (for consulation , wallet, or account)? or should I have different tables for these?

6
  • All described payment types are the same entity - so one transactions table is reasonable. The type of the transaction is a property of this entity only.
    – Akina
    Jul 5, 2023 at 13:14
  • @Akina thanks. how about diffrent users ? one coulmn for distingushing if doctor or user has paid? Jul 5, 2023 at 13:15
  • how about diffrent users ? You'd decide this self. I'd prefer to consider that both doctor and user are the same people/customer entity. So I'd store them in one table. But I'd create two separate tables (for doctors and for users) which refers to customers table and stores the data specific for separate customer type.
    – Akina
    Jul 5, 2023 at 13:20
  • @Akina no i have different entities because there are different attributes. Jul 5, 2023 at 13:22
  • The doctor can be sometimes a patient, is it? will you create two separate records for the same man?
    – Akina
    Jul 5, 2023 at 13:24

2 Answers 2

0

You should have one transaction table. It will make it so much easier to do financial reports and stats.

Don't forget to have fields to process

  • refunds
  • advance payments
  • partial payments
  • afterpay type support (there are ones that pay right away, and others that pay periodically)

I handle refunds by storing negative quantities instead of positive. Thing to watch out for, you can make the qty and total price negative, but not the unit price. Otherwise, two negatives multiplied together give a positive. On one project, I put in a field (a flag) to indicate a refund. It just helped in reports to distinguish refunds for stats.

3
  • thanks, how do you handle refunds? Jul 5, 2023 at 13:20
  • and do you agree with transaction_user_type Jul 5, 2023 at 13:26
  • Yes, you need the transaction type, user_type and maybe a few more fields if you want to support the above. Jul 6, 2023 at 13:02
0

Look at it from OOP/OOD perspective. Some attributes will be common for all transaction types and some attributes will be specific.

As a result, you could do a mixed approach:

  • One table with the common attributes.
  • Separate tables for each transaction type for the specific attributes.
  • Each specific table should have a reference to the central table.

However, don't overdo OOP things in the database. E.g. avoid many levels of inheritance. This hybrid approach keeps things still relatively simple, while allowing you to easily scale for different transaction types with few drawbacks.

As for performance - do not worry. There are ways to avoid locking problems. We have many TB-size databases using this approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.