Search code examples
databasedatabase-designdatabase-schema

Database: Schema Verification

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

alt text

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.

alt text

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.

Solution

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