发票和订阅的应用程序逻辑？
我们正处于为客户提供订阅的网络应用程序的规划阶段。订阅期各不相同，我们的客户可以无限期延长，但始终至少一个月（30 天）。
当客户注册时，客户信息（帐单地址、电话号码等）将存储在 customers 表中，并在 subscriptions 表中创建订阅
id | start_date | end_date | customer_id
--------------------------------------------------------
1 | 2010-12-31 | 2011-01-31 | 1
：每月，我们将循环访问 subscriptions 表（最好是 cronjob）并为过去的订阅周期创建发票，这些发票位于它们自己的表 - invoices 中。根据客户的不同，发票将手动打印并通过邮件发送，或仅通过电子邮件发送给客户。
由于我们的客户和产品的性质，我们需要提供各种不同的付款方式，包括电汇和银行卡付款，因此某些发票可能需要由我们的员工手动处理并登记为已付款。
每月 15 日，invoices 表将被循环访问，如果实际发票没有标记任何付款，则相应的订阅将被删除。如果注册了付款，subscriptions 表中的 end_date 会再增加 30 天（或者客户现在选择的期间）。
我们是否通过向前和向后增加日期来处理非付费客户和延长订阅来解决令人头痛的问题？当客户延长订阅时添加新订阅是否是一个更好的主意？
We're just in the planning stage of a web app that offers subscriptions to our customers. The subscription periods varies and can be prolonged indefinitely by our customers, but are always at least one month (30 days).
When a customer signs up, the customer information (billing address, phone number and so on) are stored in a customers table and a subscription is created in the subscriptions table:
id | start_date | end_date | customer_id
--------------------------------------------------------
1 | 2010-12-31 | 2011-01-31 | 1
Every month we'll loop through the subscriptions table (cronjob preferably) and create invoices for the past subscription period, which are housed in their own table - invoices. Depending on the customer, invoices are manually printed out and sent by mail, or just emailed to the customer.
Due to the nature of our customers and the product, we need to offer a variety of different payment alternatives including wire transfer and card payments, hence some invoices may need to be manually handled and registered as paid by our staff.
The 15th every month, the invoices table are looped through and if no payment has been marked for the actual invoice, the according subscription will be removed. If there's a payment registered, the end_date in the subscriptions table is incremented by another 30 days (or what now our period our customer has chosen).
Are we looking at headaches by incrementing dates forwards and backwards to handle non-paying customers and extending subscriptions? Would it be a better idea to add new subscriptions as customers extends their subscription?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我开发的一个应用程序遇到了这个问题，我们通过跟踪用户的订阅但不跟踪到期日期来解决这个问题。然后，我们跟踪他们应该在他们的帐户上计费的日期 - 因此，如果我们想查看某人正在进行哪些订阅，我们可以检索他们帐户的最新订阅记录，如果我们想查看下次向他们收费时，我们只需检查他们的
next_bill_date。通过这种方式，您可以跟踪用户的订阅并查看他们何时升级/降级，但您的计费代码保持简单 - 并且您永远不必担心重叠（因为订阅没有结束日期需要处理） 。
One of the applications that I worked on had this problem, and we solved it by keeping track of what subscription a user had, but not tracking the expiration date. Then, we kept track of the date they were supposed to be billed on on their account - so if we wanted to see what subscription someone was on, we could just retrieve the latest subscription record for their account, and if we wanted to see the next time they'd be billed, we'd just check their
next_bill_date.By doing it this way, you can track a user's subscriptions and see when they've upgraded/downgraded, but your billing code stays simple - and you never have to worry about overlaps (because subscriptions don't have end dates to deal with).
我认为只要您只有一种订阅类型，就没有必要为单个客户创建多个订阅记录。如果计费周期始终是每月固定价格，则更改订阅 end_date 就足够了。您需要知道的是他的订阅何时用完，这样您就可以停止开发票。因此，如果他延长订阅，您只需更新一条记录，下个月即可恢复计费。
另外，我认为标记未付费订阅而不是删除它们是一个更好的主意。如果客户错过每月付款，请将订阅标记为未付款，以便停止未来的发票（和服务）。当/如果他们确实付款时，您取消订阅标记，以便恢复服务/下个月的发票。
I don't think its necessary to create multiple subscription records for a single customer so long as you only have a single subscription type. If the billing period is always monthly at a fixed price, altering the subscription end_date will suffice. All you need to know is when his subscription runs out so you can stop invoicing. So, if he extends his subscription, you only need to update a single record so billing will resume next month.
Also, I think it would a better idea to flag unpaid subscriptions instead of deleting them. If a customer were to miss a monthly payment, flag the subscription as unpaid so future invoices (and service) is halted. When/if they do pay, you unflag the subscription so service/next months invoice is resumed.
我将使用订阅表仅跟踪订阅。
这意味着，当客户延长订阅时，会插入一条新记录。
此外，我会向客户表添加一个 subscription_end 日期列，以便在插入新订阅时通过触发器进行更新。
虽然这是一种非规范化，但这种方法将允许您的 Web 应用程序检查客户对服务的访问，而无需任何连接（减少数据库服务器负载）。
事实上，只有批处理才需要查询订阅的表。
很有用
与 。将变得难以维护，您可以决定通过以方便的格式导出来定期备份（我总是使用 xml，但我合作的一些企业更喜欢 csv）。
I would use the subscription table to keep track of subscriptions only.
This means that, when the customer extends his subscription, a new record is inserted.
Moreover I'd add to the customer table a subscription_end date column to be updated via trigger on insert of new subscriptions.
While this is a denormalization, such a method will allow your web app to check the customer access to the service without any join (reducing the db server load).
Indeed, only the batch will have to query the subscription's table.
Moreover, keeping the subscriptions history could be useful
When your user base and subscriptions history will become huge to mantain, you could decide to periodically backup, by exporting in a convenient format (I would always use xml, but some of the enterprise I worked with preferred csv).
为了跟踪单个客户的订阅历史记录，我认为最好添加新订阅。
您还可以省去弄清楚为什么 1 月 30 日订购的订阅会在 3 月到期的麻烦（您知道，2 月是最短的月份......）。
For tracking subscription history of a single customer I'd say it's better adding new subscriptions.
You'll also save yourself the headaches of figuring out, why a subscription ordered on January, 30th does expire in March (you know, February being the shortest month...).