对于用户可以是member 或admin 的系统，具有member 角色的用户必须通过定期订阅付费，或者获得免费访问权。
我当前的方法：
- 用户有一个
user数据库表。
-
订阅表包含用户的记录（如果他们有订阅）。
-
subscription_event 表记录每次计费或失败的付款。我可以查询这个以查看最后一个事件是否确实是成功的付款。
但是，如果用户获得“免费”访问权限，我应该如何记录？
- 是否有另一个表
complimentary_subscription 以用户 ID 作为外键？
- 在
订阅中为他们记录一个特殊的“订阅”？
- 或者向其用户行添加另一列，以获取诸如
is_complimentary 和 complimentary_expires_date 之类的列？
- 向用户行添加更通用的
expires 列？
For a system where a user can be a member or admin, users with the member role must either pay for it with a recurring subscription, or be given a complimentary access.
My current approach:
- Users have a
user database table.
- A
subscription table includes a record for a user if they have a subscription.
- A
subscription_event table records each billing or failed payment. I can query this to see if the last event was indeed a successful payment.
But how should I record if a user is given "complimentary" access?
- Have another table
complimentary_subscription with the user ID as the foreign key?
- Record a special "subscription" for them in
subscription?
- Or add another column to their user row for columns like
is_complimentary and complimentary_expires_date?
- Add a more general
expires column to the user row?
发布评论
评论（2）
问题审查
正如 @leanne 所说，您正在建模一个
Subscription，其专业化是MonthlySubscription和ComplimentarySubscription（给他们这个答案的名字）。您知道订阅可能会过期：
MonthlySubscription，当用户未支付当月的订阅费用时就会发生这种情况。ComplimentarySubscription，过期日期是在用户未支付当月订阅费用时指定的。分配给用户如您所见，
ExpirationDate是任何Subscription的基本属性，但在每种情况下存储它的方式都不同。如果是第一种情况，您必须根据最后一个事件来计算它，而在后者中，您可以直接检索它。处理数据库中的继承
因此，要将此示例模型映射到数据库模式，您可以使用 类表继承 模式href="https://rads.stackoverflow.com/amzn/click/com/0321127420" rel="noreferrer">企业应用程序架构模式一书。这是它的意图：
基本上，您将拥有一个表，其中包含类之间共享的公共属性，并且您将在单独的表中存储特定于每个类的属性。
记住这一点，让我们回顾一下您提出的选项：
有一个单独的表来存储
ComplimentarySubscription特定详细信息听起来不错，但如果您不将此表与subscription表关联起来，您最终可能会得到一个同时拥有这两个表的用户每月订阅和免费订阅。它的外键应该指向subscription表，该表告诉您用户是否有订阅（并且您必须为每个用户强制执行最多一个订阅）。是的，您必须记录用户有按月订阅或免费订阅。但是，如果您正在考虑记录金额为零的特殊订阅之类的事情，那么您正在寻找与您当前设计相匹配的解决方案，而不是为其寻找正确的模型（可能也可能不是）。
就我个人而言，我不喜欢这个，因为你把信息放在不属于它的地方。考虑到这一点，您将在哪里存储免费订阅的到期日期（请记住，对于每月订阅，您正在计算它，而不是存储到期日期）？似乎所有这些信息都在呼唤自己的“家”。
此外，如果稍后您需要添加新类型的订阅，该表将开始变得混乱。
如果您这样做，则每次
subscription_event发生更改时（如果是按月订阅），您都必须处理数据同步。一般来说，我会尽量避免这种数据重复的情况。示例解决方案
在添加新类型的订阅时，为了支持可扩展性，我会做的就是让
subscription表来存储MonthlySubscripton和MonthlySubscripton之间的共享详细信息code>ComplimentarySubscription，添加一个type列键，让您区分某行与哪种类型的订阅相关。然后，将特定于每种订阅类型的详细信息存储在其自己的表中，并引用父
subscription行。为了检索数据，您需要一个对象，负责在给定
subscription行的type列值的情况下实例化正确的Subscription类型。您可以查看《企业应用程序架构模式》一书中的模式，以获取有关如何定义
type列值、如何使用映射器对象进行订阅 的进一步帮助实例化等等。2012 年 1 月 3 日更新：定义和处理
type列的替代方案以下更新澄清了 @enoinoc 在评论中发布的以下问题：
只要该信息不是不需要编辑的静态信息，就可以将这些信息放在
Plans表中。如果是这种情况，请不要过度概括您的解决方案，并将这些知识放入相应的Subscription子类中。关于外键方法，我可以想到这种方式的一些缺点：
Subscription的哪个子类用于Plans表中的每一行。如果您得到的只是一个外键值（例如，一个整数），您将必须编写代码来将该值与要使用的类进行映射。这意味着您需要做额外的工作:)建议的解决方案
我要做的就是将其放入
Plans表中的type列中。该列将保存知道如何从特定行构建正确的订阅的类的名称。但是：为什么我们需要一个对象来构建每种类型的订阅？因为您将使用来自不同表（
subscription_event和complimentary_subscription）的信息来构建每种类型的对象，并且隔离和封装该行为总是好的。让我们看看
Plans表的样子：-- Plans Table --
Id |名称 |类型 |其他专栏...
1 |每月 |
MonthlySubscriptionMapper|2 |免费|
ComplimentarySubscriptionMapper|每个
SubscriptionMapper都可以定义一个方法Subscription MapFrom(Row aRow)，该方法从数据库中获取一行，并为您提供Subscription子类的正确实例（示例中为MonthlySubscription或ComplimentarySubscription）。最后，要获取
type列中指定的映射器实例（不使用令人讨厌的if或case语句），您可以获取类名从列的值，并通过使用反射，创建该类的实例。Question Review
As @leanne said, you're modeling a
Subscriptionwhose specializations are, say,MonthlySubscriptionandComplimentarySubscription(to give them a name for this answer).You know that a subscription can expire:
MonthlySubscription, that happens when a user didn't pay the current month's subscriptionComplimentarySubscription, the expiration date is assigned when it’s assigned to the userAs you can see an
ExpirationDateis an essential attribute of anySubscription, but the way you store it is different in each case. If the first case you'll have to calculate it based on the last event, in the latter you can retrieve it directly.Dealing with Inheritance in the Data Base
So, to map this sample model to a database schema, you could go with the Class Table Inheritance pattern described in Martin Fowler's Patterns of Enterprise Application Architecture book. Here is its intent:
Basically you'll have a table with the attributes shared in common between the classes, and you'll be storing attributes specific to each class in a separate table.
Keeping this in mind, let's review the options you proposed:
Having a separate table for storing
ComplimentarySubscriptionspecific details sound good, but if you don't relate this one with thesubscriptiontable, you can end up with an user that has both aMonthlySubscriptionand aComplimentarySubscription. Its foreign key should point to thesubscriptiontable, which is the one that tells you if a user has a subscription or not (and you'll have to enforce up to one subscription per user).Yes, you'll have to record that a user has a subscription either monthly or complimentary. But if you're thinking something like recording a special subscription whose amount is zero, you’re looking for a solution that matches your current design instead of searching for the right model for it (it might and it might be not).
Personally I don't like this one because you're putting information where it doesn't belong. Taking that into account, where you will be storing the expiration date for complimentary subscriptions (remember that for monthly ones you are calculating it, and not storing the expiration date)? It seems that all that information is crying for its own "home".
Also, if later you need to add a new type of subscription that table will begin to clutter.
If you do this, you'll have to deal with data synchronization each time the
subscription_eventgets changed (in the case of a monthly subscription). Generally I try to avoid this data-duplication situation.Sample Solution
What I would do to favor extensibility when adding new types of subscription is to have the
subscriptiontable to store shared details betweenMonthlySubscriptonandComplimentarySubscription, adding atypecolumn key that'll let you differentiate which kind of subscription a row is related to.Then, store details specific to each subscription type in its own table, referencing the parent
subscriptionrow.For retrieving data, you'll need an object in charge of instantiating the right type of
Subscriptiongiven thetypecolumn value for asubscriptionrow.You can take a look at the pattern in the "Patterns of Enterprise Application Architecture" book for further assistance on how to define the
typecolumn values, how to use a mapper object to do theSubscriptioninstantiation and so on.01/03/2012 Update: Alternatives for defining and handling the
typecolumnHere's an update to clarify the following question posted by @enoinoc in the comments:
It's ok to have that information in the
Planstable, as long it´s not static information that doesn´t need to be edited. If that's the case, don't over-generalize your solution and put that knowledge in the correspondingSubscriptionsubclass.About the foreign key approach, I can think of some drawbacks going that way:
Subscriptionto use for each row in thePlanstable. If all you got is a foreign key value (say, an integer) you'll have to write code to map that value with the class to use. That means extra work for you :)Proposed Solution
What I'd do is to put into the
typecolumn in thePlanstable. That column will hold the name of the class that knows how to build the rightSubscriptionfrom a particular row.But: why do we need an object to build each type of
Subscription? Because you'll be using information from different tables (subscription_eventandcomplimentary_subscription) for building each type of object, and it´s always good to isolate and encapsulate that behavior.Let's see how the
Planstable might look like:-- Plans Table --
Id | Name | Type | Other Columns...
1 | Monthly |
MonthlySubscriptionMapper|2 | Complimentary |
ComplimentarySubscriptionMapper|Each
SubscriptionMappercan define a methodSubscription MapFrom(Row aRow)that takes a row from the database and gives you the right instance of theSubscriptionsubclass (MonthlySubscriptionorComplimentarySubscriptionin the example).Finally to get an instance of the mapper specified in the
typecolumn (without using a nastyiforcasestatements) you can take the class name from the column's value and, by using reflection, create an instance of that class.尽管如此，免费订阅毕竟是订阅。其到期付款金额和认购价格将为零。其到期日期将是您设置的免费订阅与常规订阅的到期日期。
如果您当前的设置不允许您根据这些因素成功查询免费订阅/用户，则只需要单独的列或表。
注意：我在这里假设您当前将用户与订阅价格相关联。无论如何，您都需要知道这一点，以防将来订阅价格发生变化，或者您还想为经常性或促销用户启用折扣。
A complimentary subscription is a subscription, nonetheless. Its payment amount due and price for subscription would be zero. Its expiration date would be whatever you set as an expiration date for complimentary subscriptions versus regular subscriptions.
You would only need a separate column or table if your current setup wouldn't allow you to successfully query for complimentary subscriptions/users based on those factors.
Note: I'm assuming here that you currently associate a user with a subscription price. You would need to know that anyway in case the subscription price changes in the future, or if you wanted to also enable discounts, say, for recurring or promotional users.