Subscription management modelisation

[DioFun]

Subscription management

Features

• [x] Calcul automatique du prix des abonnements en fonction des différentes contraintes
• [x] Possibilité d'ajouter des articles vendus à part
• [x] Création et stockage des factures
• [ ] Module de remboursement pour articles et abonnements
  • [ ] Création d'un avoir
  • [ ] Statuts du remboursement
• [ ] Module de trésorerie
  • [ ] Affichage des flux d'argent avec trie selon le type, moyen de paiement,...
  • [ ] Possibilité d'exporter un csv ces mêmes filtres

Database modelisation

erDiagram
    USER
    SUB

    SALE {
        int id PK
        int seller_id FK
        int client_id FK
        int payment_method FK
        int invoice_id FK
        int total_price
        date verified_at
        date created_at
        date updated_at
    }

    REFUND {
        int id PK
        int refunder_id FK
        int refund_method FK
        int invoice_id FK
        int total_price
        varchar(255) reason
        date verified_at
        date created_at
        date updated_at
    }

    ARTICLE {
        int id PK
        varchar(255) name
        int price 
        date created_at
        date deleted_at
    }

    ABONNEMENT {
        int id PK
        int duration
        int price
        date created_at
        date deleted_at
    }

    INVOICE {
        int id PK
        json generation_json
        date created_at
    }

    PAYMENT_METHOD {
        int id PK
        varchar(255) name
        boolean auto_verify
        date created_at
        date deleted_at 
    }

    SALE_ARTICLE_DETAIL {
        int id PK
        int sale_id FK
        int product_id FK
        int quantity
    }

    SALE_ABONNEMENT_DETAIL {
        int id PK
        int sale_id FK
        int abonnement_id FK
        int duration
    }

    REFUND_ARTICLE_DETAIL {
        int id PK
        int refund_id FK
        int product_id FK
        int quantity
    }

    REFUND_ABONNEMENT_DETAIL {
        int id PK
        int refund_id PK
        int abonnement_id FK
        int duration
    }

    USER ||--o{ SALE : has_many
    USER ||--o{ SUB : has_many
    SALE ||--o{ REFUND : can_have
    REFUND ||--o{ REFUND_ABONNEMENT_DETAIL : has_many
    REFUND ||--o{ REFUND_ARTICLE_DETAIL : has_many
    REFUND_ARTICLE_DETAIL ||--|| ARTICLE : has_one
    REFUND_ABONNEMENT_DETAIL ||--|| ABONNEMENT : has_one
    REFUND ||--|| INVOICE : has_one
    SALE ||--|| INVOICE : has_one
    SALE }o--|| PAYMENT_METHOD : has_one
    SALE ||--o{ SALE_ABONNEMENT_DETAIL : has_many
    SALE_ABONNEMENT_DETAIL ||--|| ABONNEMENT : has_one
    SALE ||--o{ SALE_ARTICLE_DETAIL : has_many
    SALE_ARTICLE_DETAIL ||--|| ARTICLE : has_one
    SALE ||--|| SUB : has_one
    REFUND ||--|| INVOICE : has_one

[Letiste]

Some questions I have reading this database schema, though I might be lacking some context so they might sound stupid:

1. Why is the payment method outside of the sale?
2. Why do we differentiate sale and refund details? Would it be possible/make sense to only have a single detail type used for both refund and sale?
3. Why do we differentiate abonnement and article? Can we consider abonnement to be an "article" that people could buy?
4. Why would a refund be linked to a sale?
5. Any idea on how we should handle data deletion? When a user is removed from the db, do we want to remove all the sales and refunds? If a sale is removed, do we keep invoice? ...
6. If the price of an article changes, would it impact previous sales/refunds?

I'm not requesting any changes to this proposition, it's just to try to understand it a bit more

[Letiste]

One more because I felt it was not enough:

7. Are we okay with coupling the pricing and duration of a subscription? Maybe the article could be 1 month subscription and you buy a quantity of this article. Might be overkill though

[DioFun]

I'll try to answer the questions with the different discussions we had during the creation of this model.

1. The payment_method database aims to list the different method of payment, the goal is to allow to add, remove method with views. So we link a sale to his payment method. A payment method can't be hard removed because a previous sale must keep the tracking of the method used.

2. The "details" table are many to many join tables with an additional field for the quantity.

3. We want to process articles and subscriptions differently. Articles are in a dropdown selector where the seller can choose wich article he is selling. On the other hand, we want to apply the different offers of subscription not directly on the view but in the data treatment process.

4. A refund would be linked to a sale because we do not want to refund something that has not been bought or refund many times the same thing.

5. We thought over this during the brainstorming and it's still under considerations but invoices has to be kept for 10 years at least legally. Maybe, the sales, refunds and other information must be deleted after about a year.

6. As it 's not written on the issue, article can't be edited, we can only soft delete it and recreate it if we want to change the price. As a consequence it doesn't impact refunds and sales.

7. I think 3 answers the question

If something is not clear, feel free to ask any questions.

[D0gmaDev]

(Cette conversation est sponsorisée par DeepL)

[DioFun]

Même pas

[Letiste]

Thanks for the clarification!

2. Looking at the schema, I don't see a difference in fields for a REFUND_ARTICLE_DETAIL and a SALE_ARTICLE_DETAIL (same for abonnement). My understanding is that for a refund, the total price will be negative (money going out) and for a sale, the total price will be positive (money going in). We could implement this logic in the service instead of the db:
   • someone buys an article that is priced at 5€ -> we create a sale with a total price of 5€
   • someone refunds an article that is priced at 5€ -> we create a refund with a total price of -5€
   • if we want to get all sold articles, we can join articles with sales I'm okay with keeping them separated though, it's just a matter of where we want to put the sale/refund logic
3. Would it work with people selling a router that was originally not bought from us?

[DioFun]

Il a été envisagé de fusionner les tables "sales" et "refunds" mais @nymous a conseillé de ne pas sur-simplifier le modèle avec comme illustration une entreprise (je ne sais plus laquelle) qui avait des problèmes suite à une telle simplification. Pour le point 4, nous ne rachetons ni ne vendons pas de routeurs.