数据库月订阅系统设计的良好实践

我想知道如何设计一个数据库中的月度订阅软件系统。 这些系统在互联网上被广泛使用，尽管我找不到很多关于数据库设计的东西。

在我的案例中，这些元素(或许还有其他一些我忘记了的元素)必须包括在内:

  • 客户
  • 计划(如「基本」/「额外」)。每个计划有一个月的价格，和一个信用额度(例如: 基本计划提供30信用额度每月和保费计划无限信用)。
  • 信用是在应用程序中花费的虚拟货币。
  • 订阅/取消订阅
  • 支付(注意实际支付的价格可能因为折扣等因素而与计划的基准价格不同)
  • ... ?

除了数据库设计之外，还可能需要设置触发器来执行此操作(?)。

我的痛处:

  • 我不能一概而论地看出它的全局设计是什么
  • 数据库中哪一个应该是行: month _ susscrition (即每个客户机每月1行)还是订阅本身(即每个客户机1行) ？
  • 如何处理每月订阅的自动续订？
  • 如果你预见到使用贝宝这样的服务来处理自动月度支付，你会如何处理支付设计？

注意

我自愿不公开我的需求的细节，因为这样，辩论可以保持通用性，可以更有益于其他人。

谢谢你的帮助。

54889 次浏览
小开

I would use a relational table design and a relational database.

I would have a Client table.

Client
------
Client ID
Client Last Name
Client First name
...

I would have a Subscription table

Subscription
------------
Subscription ID
Client ID
Subscription Purchased Time stamp
Subscription Started Time stamp
Subscription Ends Time stamp

I would have a Purchase table

Purchase
--------
Purchase ID
Subscription ID
Payment method
...

Now to answer your questions. You should ask just one question at a time.

Which one should be a row in the DB: the month_subscription (ie 1 row per client per month) or the subscription itself (ie 1 row per client)?

One row per subscription per month.

How would you handle the automatic renewal of the monthly subscription?

Netflix just debits my PayPal account every month. You could do the same with PayPal or with a credit card. If you accept credit cards, you're going to have to make an arrangement with your bank, a credit card processor, or PayPal.

How would you handle payments designing if you foresee to use services like PayPal to handle automatic monthly payments?

Netflix just debits my PayPal account every month. You could do the same.

小开
最佳答案

I would use this model

Your clients

Client
------
Client ID
Name
...

Your plans (you can define new plans when you want). I add a Price_per_year if you want to propose a discount if the client buys 12 months in one shot (but it's only an idea).

Plan
------
Plan ID
Name
Credits_per_month
Price_per_month
(Price_per_year)

Your subscriptions

Subscriptions
------
Subscription ID
Client ID
Plan ID
Subscription_start_timestamp
Subscription_end_timestamp

Considering this model I would use 1 row per client per plan.

When a client subscribes to an offer like "Premium with 1st month free !", your database would look like this :

Client
------
ID: 1; LastName: Foo; ...


Plan
------
ID: 1; Name: Premium; Credits: -1 (unlimited); Price_per_month: 30
ID: 2; Name: Premium 1st month offer; Credits: -1; Price_per_month: 0


Subscription
------
ID: 1, Client ID: 1, Plan ID: 2, Start: 2014-05-07 08:00, End: 2014-06-06 07:59
ID: 1, Client ID: 1, Plan ID: 1, Start: 2014-06-07 08:00, End: 9999-12-06 07:59

When a client unsubscribe the 1st July, update the column end in your Subscription table with the month and the year only (because you have pre-set the day and the time).

Subscription
------
ID: 1, Client ID: 1, Plan ID: 2, Start: 2014-05-07 08:00, End: 2014-06-06 07:59
ID: 1, Client ID: 1, Plan ID: 1, Start: 2014-06-07 08:00, End: 2014-07-06 07:59

To know if a client is not unsubscribe, you could use this :

Select Count(client.*) From Client client
Inner Join Subscription sub On sub.client_id = client.id
Where DATE_TODAY Between sub.start And sub.end

Make sure that you can't have 2 subscriptions for a client at the same time.

This allow you to handle automatically the monthly subscription in your app, but not with your bank/paypal account.

But some banks offer you two services: - Unique debit - Periodic debit

The second one would allow you to handle the monthly subscription.

小开

Looking at the accepted answer I would add another table where all the changes or updates of a subscription or plan would be stored for back reference. This way you have a clear log on when and what plans were selected for what period.

I would make sure the subscription table would contain the variables which tells you if it is active or not, and to what date it is payed for and any other possible data you would require in daily operations.

The extra table will make sure of you being able to recreate any subscription at any given time if needed.


提交答案