Multiple tables for different payment transaction

database-designtransaction

I'm building an application that users can pay some fees to sign up for a yearly membership and courses they're interested in.

Currently, I'm planning to have two different tables: 'course_payment' and 'membership_payment'. The reason behind this is that they have very different fields although they share some same information.

For example, course_payment table will have course title, regular price, special price, # of accompanying guests, guest price… and etc. membership_payment table will have the name of membership, membership start date and expiry date, and etc…

Well, they have some common fields such as user_id(payer), subtotal, tax, tax_type and total. However, normalizing tables for payment transaction history seems a bit too much and cumbersome unless there's a very good reason for it.

Do you think I should have two different payment history tables for each? If storing them all in one table is a better approach, what should I do with all the info that are only needed for one or the other such as course name and membership name?

Thanks!

Best Answer

Always, start with the data model, you will get complete picture. I don't get your complete requirements, from my understanding, the following points are written.

Business Requirements:

  1. A user can subscribe more than one course.
  2. A course may subscribed by more than one user.
  3. A user can subscribe more than one membership.
  4. A membership can be subscribed by more than one user.
  5. A user can have more than User Payment.
  6. A user payment must contain atleast one User.

Data Model:

For the above requirements, the logical data model may look like this:

User Subscription Model

I think now you can keep your common payment info on UserPayment table. Also have a thought about future requirements, in that case, create separate entities for the following entities (examples).

  1. Tax (type, rate, etc).
  2. Price (type, value, etc).
  3. Membership type (fulltime, quaterly, etc).
  4. Course type (technical, design, etc).

Depends on the requirements, the model will change. First understand the requirements completely and start with the conceptual data model. It is a starting point, you will learn more, I hope this will give you an idea. Thanks!.

Related Question