Database: Schema Verification

Can I please get input on the following subset of a schema?

One of the goals of this database is to be able to store the membership info for two completely different types of members. In this schema I just named them Users and Businesses. I am far enough along in the design of this database and know that Users and Businesses will come from different tables as represented here. The concern is tracking their membership information.

Here are some knowns:

• Both types of members will be paying parties
• Memberships can lapse and it is important to check when memberships are due
• In tracking the status of a membership dates, subscription dates will need to be posted for the members to see and reminders sent out for renewal of membership
• Suspended members will still exist in the DB for reactivation but will not have access until then
• Each member, regardless of type, will have its own unique member id and each user/business can only have one membership

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.

In the User_Memberships and Business_Memberships tables I have identified a member_status attribute as I will need a quick look into the active state of a membership. Instead of using a boolean status here should I switch it out with a membership_suspended_date and perform a calculation off of that instead?

Any input into the good or bad of this design will be greatly appreciated. Thanks

EDIT

Attempt #2 trying to take into consideration input from dportas.

Since a there can only be a given unique instance of a member (user or business) I added membership_change_date to capture the history of a member if they are to switch from free to paid to free etc.

Any inputs here still considering the original criteria listed above.

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).

▶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.

"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.