The two inline graphics do not appear in my browsers, so I am going by your text, and Ken's answer.
I do not believe this question has been dealt with fully.
Your desc of Membership_Type seems to me to be Subscription_Type
SubscriptionType holds generic info re pricing, terms, etc
Subscription holds info re the specific pricing, expiration dates, etc for a Member.
Yes, this is a classic case for Supertype-Subtypes or Orthogonal Design (commonly required but unfortunately not commonly understood)
Member is the Supertype; User and Business are Exclusive Subtypes. The Relational is 1::0-or-1 and one Subtype must exist for each Member
UserId and BusinessId are RoleNames for MemberId, implemented as Primary Keys in the Subtypes, which is also the Foreign Key to Member; there is no additional Id column in the Subtypes.
Easily implemented declaratively in SQL
This is pure Fifth Normal Form
Full Referential and Data Integrity is maintained in any Standard SQL (code in the Non-SQLs)
The Status of a Member is easily derived from the latest Subscription row MAX(Subcription.Date).
Any flag or boolean in Member for that purpose is duplicate data and will introduce an Update Anomaly (where the Normalised model has none).
The table design you have displayed seems "over-normalized" and does not model what you are describing. The key insight is that a member of any kind is recorded only once regardless of whether they are a business or a "user", and they retain their account forever even if it lapses and gets reinstated repeatedly. This means you are only tracking one thing: users=members=businesses. That means, so far, one table.
Your second table is a transaction history for each member/user/business. Note that a comp goes in as a payment with 0.00 dollars.
"The Membership_Types table will hold information in regards to whether or not a member is a paying member or a comp member or part of any group memberships."
OK, this is the third table, membership types, with details on pricing.
You would have to tell us more about the group memberships before I can say what to do with those.
As for most of the rest of these requirements, they are all about notifications, those come out of the transaction table.
I suggest you create a new supertype table for all the data common to both types of membership (type code, status, date, duration). As a rule, I think it would be better for those columns to appear in one table, not two. In fact there's a name for this rule: The Principle of Orthogonal Design.
发布评论
评论（3）
这两个内联图形不会出现在我的浏览器中，因此我将按照您的文本和肯的回答进行操作。
我认为这个问题还没有得到充分解决。
在我看来，您的 Membership_Type 描述是 Subscription_Type
SubscriptionType 包含有关定价、条款等的通用信息
订阅保存会员的具体定价、到期日期等信息。
是的，这是超类型-子类型或正交设计的经典案例（通常需要，但不幸的是不被普遍理解）
成员是超类型；用户和业务是专有子类型。关系是 1::0-or-1，每个成员必须存在一个子类型
UserId 和 BusinessId 是 MemberId 的角色名称，在子类型中作为主键实现，这也是成员的外键；子类型中没有额外的 Id 列。
在 SQL 中轻松以声明方式实现
这是纯粹的第五范式
在任何标准 SQL 中都保持完全引用和数据完整性（非 SQL 中的代码）
成员的状态可以轻松地从最新的订阅行中派生出来
MAX(订阅.日期)。▶会员实体关系图◀
不熟悉关系数据库建模标准的读者可能会发现▶IDEF1X 符号◀ 很有用。
如果您提供 Group::Member 信息，我可以对其进行建模。
The two inline graphics do not appear in my browsers, so I am going by your text, and Ken's answer.
I do not believe this question has been dealt with fully.
Your desc of Membership_Type seems to me to be Subscription_Type
SubscriptionType holds generic info re pricing, terms, etc
Subscription holds info re the specific pricing, expiration dates, etc for a Member.
Yes, this is a classic case for Supertype-Subtypes or Orthogonal Design (commonly required but unfortunately not commonly understood)
Member is the Supertype; User and Business are Exclusive Subtypes. The Relational is 1::0-or-1 and one Subtype must exist for each Member
UserId and BusinessId are RoleNames for MemberId, implemented as Primary Keys in the Subtypes, which is also the Foreign Key to Member; there is no additional Id column in the Subtypes.
Easily implemented declaratively in SQL
This is pure Fifth Normal Form
Full Referential and Data Integrity is maintained in any Standard SQL (code in the Non-SQLs)
The Status of a Member is easily derived from the latest Subscription row
MAX(Subcription.Date).▶Membership Entity Relation Diagram◀
Readers who are unfamiliar with the Standard for Modelling Relational Databases may find ▶IDEF1X Notational◀ useful.
If you provide the Group::Member info, I can model that.
“每个用户/企业只能有一个成员资格”
您显示的表格设计似乎“过度规范化”，并且没有模拟您所描述的内容。关键的见解是，任何类型的成员仅记录一次，无论他们是企业还是“用户”，并且即使帐户失效并反复恢复，他们也会永远保留其帐户。这意味着您只跟踪一件事：用户=成员=企业。这意味着，到目前为止，只有一张桌子。
您的第二个表是每个会员/用户/企业的交易历史记录。请注意，补偿以 0.00 美元的付款形式输入。
“Membership_Types 表将保存有关会员是否是付费会员、免费会员或任何团体会员资格的一部分的信息。”
好的，这是第三个表，会员类型，包含定价详细信息。
在我说出如何处理这些成员之前，您必须告诉我们更多有关群组成员身份的信息。
至于其余的大部分要求，它们都与通知有关，这些通知来自事务表。
"each user/business can only have one membership"
The table design you have displayed seems "over-normalized" and does not model what you are describing. The key insight is that a member of any kind is recorded only once regardless of whether they are a business or a "user", and they retain their account forever even if it lapses and gets reinstated repeatedly. This means you are only tracking one thing: users=members=businesses. That means, so far, one table.
Your second table is a transaction history for each member/user/business. Note that a comp goes in as a payment with 0.00 dollars.
"The Membership_Types table will hold information in regards to whether or not a member is a paying member or a comp member or part of any group memberships."
OK, this is the third table, membership types, with details on pricing.
You would have to tell us more about the group memberships before I can say what to do with those.
As for most of the rest of these requirements, they are all about notifications, those come out of the transaction table.
我建议您为两种类型的成员资格共有的所有数据（类型代码、状态、日期、持续时间）创建一个新的超类型表。一般来说，我认为这些列最好出现在一个表中，而不是两个表中。事实上，这条规则有一个名字：正交设计原理。
此模式可能对您也有用： http://www.tdan.com/view-articles/ 5014
I suggest you create a new supertype table for all the data common to both types of membership (type code, status, date, duration). As a rule, I think it would be better for those columns to appear in one table, not two. In fact there's a name for this rule: The Principle of Orthogonal Design.
This pattern might also be useful to you: http://www.tdan.com/view-articles/5014