Database Design help

Hello All,

What we currently have:

users, places, products

each user can have 1 or more places.
Each place can have one or more product
Some products are recurring (N days - 7,12,15,30 etc) some are fixed days long and one off

Some product are shared between places that user has and have limit to how many places it can apply to

We need to design payment schema which will support the structure on top, as well payment gateway integration like stripe, pay_pal and direct banking

Some use cases schema should be able to handle

  1. Adding removing places.
    Customers buys 3 places product which can be serving 3 location for price X, for duration N days.
    After M days M < N they want to add one more location (this meant they are now 4 places product with price Y), this means that they have to pay (N - M) * Y until next billing next month bill.
    Customer removes one of the places - there is no change until next billing period, and from new period it will charge lower price
  2. Bad customer
    Customer pays for the service, after few days he goes to the bank and cancels transaction without notifying us. Bank will send us letter with transaction id to handle in our side and give them refund. The refund needs to be somewhere stored
  3. Customer want refund before 7 days
  4. Customer want to cancel next billing cycle
  5. Customer want to add one time product for one of the places and they gonna do X number where we will charge them flat fee plus % of X

With your current questions, we don’t have enough context to know what you already know or don’t know, so it is impossible to guide you without just telling you the answer (which we won’t do).

Please provide some example of what you’ve tried and I’m sure you’ll get more help.

Happy coding :slight_smile:

Here is one try I did. I have never worked or build payment system db side not sure what am I missing.

Just posted what I already have tried

Not sure if I am missing something or doing right way