database 在数据库中设计包月系统的良好做法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23507200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Good practices for designing monthly subscription system in database
提问by David D.
I'd like to know how to design a monthly subscription software system in a database. These systems are broadly used all over internet, though I can't find lots of stuff about database design.
我想知道如何在数据库中设计包月软件系统。这些系统在互联网上广泛使用，但我找不到很多关于数据库设计的东西。
In my case, these elements (and maybe some others I forgot) have to be included:
就我而言，必须包括这些元素（也许还有一些我忘记了）：
- Client
- Plan (like 'basic'/'premium'). Each plan has a monthly price, and an amount of credits (for instance: basic plan provides 30 credits per month and premium plans unlimited credits).
- Credits are a virtual money that is spent within the application.
- Subscription/Unsubscriptions
- Payments (Note that the price actually paid could be different from the base price of the plan because of discount, etc.)
- ... ?
- 客户
- 计划（如“基本”/“高级”）。每个计划都有一个每月价格和一定数量的积分（例如：基本计划每月提供 30 个积分，而高级计划提供无限积分）。
- 积分是在应用程序中花费的虚拟货币。
- 订阅/取消订阅
- 付款（请注意，由于折扣等原因，实际支付的价格可能与计划的基本价格不同）
- ……？
In addition of database design, there could be triggers that need to be set to do this (?).
除了数据库设计之外，可能还需要设置触发器来执行此操作 (?)。
My pain points:
我的痛点：
- I can't see in a general manner what is the global design of this
- Which one should be a row in the DB: the month_susbscrition (ie 1 row per client per month) or the subscription itself (ie 1 row per client)?
- How would you handle the automatic renewal of the monthly subscription?
- How would you handle payments designing if you foresee to use services like Paypal to handle automatic monthly payments?
- 我无法笼统地看到这个的全局设计是什么
- 哪个应该是数据库中的一行：month_susbscrition（即每个客户每月 1 行）或订阅本身（即每个客户 1 行）？
- 您将如何处理每月订阅的自动续订？
- 如果您预见使用 Paypal 之类的服务来处理自动每月付款，您将如何处理付款设计？
Note
笔记
I voluntarily don't expose my needs in details because that way, the debate can stay generic and can be more useful to other people.
我自愿不公开我的需求细节，因为这样，辩论可以保持一般性并且对其他人更有用。
Thanks for help.
感谢帮助。
回答by Chambeur
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).
您的计划（您可以根据需要定义新计划）。如果客户一次性购买 12 个月，如果您想提出折扣，我会添加 Price_per_year（但这只是一个想法）。
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.
考虑到这个模型，我会在每个计划中为每个客户使用 1 行。
When a client subscribes to an offer like "Premium with 1st month free !", your database would look like this :
当客户订阅“Premium with 1st month free!”之类的优惠时，您的数据库将如下所示：
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).
当客户在 7 月 1 日取消订阅时，请仅使用月份和年份更新订阅表中的列结尾（因为您已经预先设置了日期和时间）。
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.
确保您不能同时为一个客户订阅 2 个订阅。
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.
第二个将允许您处理每月订阅。
回答by Gilbert Le Blanc
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)?
哪个应该是数据库中的一行：month_subscription（即每个客户每月 1 行）或订阅本身（即每个客户 1 行）？
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.
Netflix 每个月都会从我的 PayPal 账户中扣款。你可以用 PayPal 或信用卡来做同样的事情。如果您接受信用卡，则必须与您的银行、信用卡处理商或 PayPal 进行安排。
How would you handle payments designing if you foresee to use services like PayPal to handle automatic monthly payments?
如果您预见使用 PayPal 等服务来处理自动每月付款，您将如何处理付款设计？
Netflix just debits my PayPal account every month. You could do the same.
Netflix 每个月都会从我的 PayPal 账户中扣款。你也可以这样做。
回答by Kim Steinhaug
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.
如果需要，额外的表将确保您能够在任何给定时间重新创建任何订阅。
