Design for Plans and Offers and <whatever>?

Databases
1

My website offers various “membership plans” which give varying degrees of access to content.

For example, a “Guest” can read all News and Articles. And a “Silver” member can also read exclusive interviews. And a “Platinum” member also gets access to podcasts.

Each “membership plan” has a price associated with it as well. For example: Silver=$30/yr, Gold=$40/yr, Platinum=$50/yr

But then I started thinking that for a given “membership plan”, the prices or even features could change over time.

Maybe next year a Platinum member will cost $60 and also include free text message?

So I am trying to figure out how to model this in my database, so that as proces (and possibly features) change over time, that I can keep track of things, and not have a bunch of fragmented Platinum_v01, Platinum_v02, Platinum_v03 records.

Follow me?

I was thinking of maybe creating a relationship like this…

One PRODUCT can have many OFFER

Thoughts?

2

Ok the best way to think about this is by answering the question “What is an entity and what is an attribute of a relationship between entities?”. A member is an entity. A subscription plan is an entity. However the price and features between the member and their plan is an attribute of that relationship. You can have “MemberA” who is subscribed to “Plantinum” and is paying $50/yr.

But lets say you decide to up the price of that plan later. If you tie the cost to the plan itself, if you change the price you are going to change it for everyone on that plan. Not something you want to do. You may want people who have the same plan but came in at different times to continue paying what they have always paid at the time they signed up. You want to model the fact that MemberA is tied to Platinum and that relationship is $50/yr. While MemberB (who subscribed later) is also tied to Platinum and is paying $60/yr. You model it like this…

Member Table

Member_ID
Name
Email
…

Plans Table

Plan_ID
Name
…

Members_Plans Table

Member_Plan_ID
Member_ID
Plan_ID
Cost_They_Pay

See how we relate the member to the plan through this intermediate table? Ok, that means the cost they pay is related to member_id being associated with the plan_id. Other attributes you would put on this table to describe the relationship could be things like when_they_subscribed or a special note about their subscription.

So how about features of a plan? Same thing. But this time it would between this intermediate table and a table list of features (past, present and future).

Features Table

Feature_ID
Feature_Name

Now you have a table that links their plan “relationship” to the features that plan had AT THAT TIME.

Plan Features Table

ID
Member_Plan_ID
Feature_ID
Cost_Of_Extra_Feature
…

So putting this all together you have MemberA who subscribed to Platinum. They did so on Jan 2, 2018 (as specified in the Members_Plans table as an attribute). At that time the plan was associated with features 1, 2 and 4 (as shown in the Plan Features table). As things change you adjust the intermediate tables about the relationships between the entity tables, not the entities themselves. If they decide to add feature 5, then you would add a row to the plan features table. That features extra cost could then be added to the “base plan” as described in the members_plans table. You would not delete a plan or a feature unless you can see no user has that plan or that plan is linked to the feature you want to delete.

This might sound a little more complicated than it really is, but trust me this is not too hard and gives you tons of flexibility. If I want to know what features a user currently has, I can query the user, link it through the members_plans table and from there to the features table. This will work no matter how long they have had the plan or if you introduce new plans/features in the future.

I hope this helps.

P.S. You can add attributes onto the plan features table like “when did they get this feature?” and of course what that feature costed at that time.

1 Like
3

@Martyr2,

OMG!! You are beautiful!! :wink:

I’m so embarrassed that I didn’t see what you just typed out in pain-staking detail?!

(I have been away from coding for a couple of years, so my brain is rusty. Rather funny I got caught on this, since data modeling is my string point, but hey, we are all human!)

It’s super late and I gotta get to bed, but before Id o…

Basically what you described sounds similar to the classic ecommerce set up for Customer, Order, Order Details, Product. Specifically, you store the “unit price” in the Product table, but you also have a “sale price” in the order Details because that is the 8real* price that the Customer pays when they check out, right?

In the morning when I am fresh, i will draw out what you described on paper and think through this some more, but I get what you are saying and it is beautiful.

Thank goodness I asked for help on this one - you just saved me from a world of hurt down the road!!

:trophy:

4

@Martyr2,

I skimmed what you said above because it is after 2:00am, so maybe I missed this, but wanted to comment while it was in my mind.

Seems like in speed-reading what you siad, that you broke out Member_Plan separately from Plan_Feature.

I think to model the real world, you need a triumvirate of Member_Plan_Feature so if I come back with questions like, “Which Plan does Mary have and how much is she paying for it and which Features does it include?” you can provide an answer, right?

if you don’t do that, then you can answer “Which plan does Mary have and how much is she paying?” and you can separately answer “Which Plan does Mary have and which Features are in it?” but not both things at once?

Just saying, but maybe you addressed that and I missed it?

I will take my time and pour over this in the morning - or at least by early afternoon! :wink:

5

Yeah look through what I said again. I did break the features away from the plans because again the features may change from the plan. You want to capture what features they had at the time they made the relationship (aka they subscribed). In other words, you want to “freeze” what features they had whaen they subscribed. If you try to link the features to plans (even through a joining table) then when you change the relationship between a plan and its features, it will still change it for everyone related to that plan. Again, something you probably don’t want to do.

In other words, features here can be thought of as extended attributes of the relationship between the member and their plan. When Mary signed up for the platinum plan, features 1, 2 and 4 were included. Each of those features costed 10 dollars extra. But then John came along when feature 5 was introduced on the platinum plan. He will have a relationship to platinum and that relationship includes feature 5 which is also an extra 10 dollars. It was not available, and should not be added to Mary’s plan because she doesn’t want to pay the extra. If at a later time she opts into feature five, then you add the feature relationship to her plan in the Plan Features table. If she never opts for it, it never gets put in her relationship and she never pays for it.

Again, lots of flexibility doing it this way. So check it over again and hopefully what I said makes more sense. :slight_smile:

P.S. You are exactly right, this is classic ecommerce setup and their relationships in order details etc. Because think about it, Mary is buying a subscription (aka order). Her features are the order details. Her details are going to be at the time of purchase (purchase order). But unlike other ecommerce systems where an order stays stagnate, her subscription will change through time so the relationship has to change. It is like a morphing order. Then each month you take a snapshot of the current relationship and bill accordingly (her monthly statements). Again if I morph the details related to an order it will change all her orders through time and again probably not what you want to do.

6

@Martyr2,

I have had a chance to re-read what you posted above and ponder things.

On the first part of your solution, I agree. But on the second part, while I can see what you are saying, I think there is a better way to model things.

Below are my thoughts…

Note:
a.) I have tweaked the naming convention of the tables.
b.) Below each table is some sample data.
c.) For simplicity, I used “natural keys” for the the sample data. (In practice, IDs would be used.)

MEMBER
- id (pk)
- name

Bob
John
Amy
Larry

OFFER
- id (pk)
- member_id (fk)
- membership_plan_id (fk)
- offer_price
- start_date
- end_date

Amy, Platinum, $50, 2/15/19, 2/15/20
Larry, Platinum, $60, 3/3/19, 3/3/20

MEMBERSHIP_PLAN
- id (pk)
- name
- unit_price  (aka current price)
- status (current)

Silver, $30
Gold, $40
Platinum, $60

MEMBERSHIP_PLAN_DETAILS
- id (pk)
- membership_plan_id (fk)
- plan_feature_id (fk)
- start_date
- end_date

Platinum, News, 2/1/19, -
Platinum, Articles, 2/1/19, -
Platinum, Interviews, 2/1/19, -
Platinum, Archives, 2/1/19, -
Platinum, Podcasts, 3/1/19, -

PLAN_FEATURE
- id (pk)
- name
- status (current)

News
Articles
Interviews
Archives
Podcasts



Here are two additional optional tables…

PLAN_PRICE_HISTORY
- id (pk)
- membership_plan_id (fk)
- price
- effective_date
- end_date

Platinum, $50, 2/1/19, 2/28/19
Platinum, $60, 3/1/19, -

PLAN_FEATURE_HISTORY
- id (pk)
- plan_feature_id (fk)
- effective_date
- end_date

News, 1/1/19, -
Articles, 1/1/19, -
Interviews, 1/1/19, -
Archives, 1/1/19, -
Podcasts, 3/1/19, -



Here are the relationships visually…

MEMBER -||----|<- OFFER

OFFER ->|----||- MEMBERSHIP_PLAN

MEMBERSHIP_PLAN -||----|<- MEMBERSHIP_PLAN_DETAILS

MEMBERSHIP_PLAN_DETAILS ->|----||- PLAN_FEATURE


MEMBERSHIP_PLAN -||----|<- PLAN_PRICE_HISTORY

PLAN_FEATURE -||----|<- PLAN_FEATURE_HISTORY


Above, you suggested joining my MEMBER,MEMBERSHIP_PLAN, PLAN_FEATURE together via my OFFER table.

While that would work, I would argue that approach is more of a hinderance than a help, and it doesn’t accurately model real life.

By taking this approach, you would end up with results like this…

Amy, Platinum, $50, 2/15/19, News
Amy, Platinum, $50, 2/15/19, Articles
Amy, Platinum, $50, 2/15/19, Interviews
Amy, Platinum, $50, 2/15/19, Archives
Amy, Platinum, $50, 2/15/19, Podcasts

While this does accurately show that Amy paid $50 on 2/15/19 and which Features she got when signing up, it creates redundant data in the offer_price and start_date.

Using my approach, which is more streamlined, you could run a query on MEMBERSHIP_PLAN, MEMBERSHIP_PLAN_DETAILS, PLAN_FEATURE for a given date to determine which features Amy had when she signed up, as well as how the Features may have changed during her subscription.

MEMBERSHIP_PLAN_DETAILS is really a function of MEMBERSHIP_PLAN and PLAN_FEATURE and has nothing to do with the MEMBER.

Yes, a Member signs up for certain Features, and those Features can change during the subscription, but I would argue my approach is better, more accurate, and just flexible if not slightly more so.

But in fairness, each of our approaches has pros and cons.

What do you think?


(BTW, this has turned out to be A LOT more involved than what I had wanted, but it is a good mental exercise nonetheless!!) :wink:
7

You know, you are right. I have only been doing this sort of stuff for 20 years. You asked for help, so I offered some help. Model it however works for you.

2 Likes
8

Consider how you’re going to write a query for looking up that plan info for plans that are currently active (IE: Have not got an end date.) while also correctly finding one that is not active. Not impossible, just a little clunky.

Your offer table isnt an offer table, it’s an accepted_plan table.

There would be no need for artificial ID’s in plan_price_history or plan_feature_history, as they are not used as foreign keys elsewhere and serve no purpose beyond being foreign keys.

What’s the purpose of plan_price_history? You seem to be storing all the relevant data from it in other tables.

10

That sounds sarcastic…

Care to explain why?

11

Sorta my point with what @Martyr2 suggested in his solution… Why would I even need that capability at all?

Once a person signs up and I get there money, there is no pondering or negotiations on which Features they get/got for a given Offer. You get what you bought.

I am not ding a-la-cart.

Either approach works, I just felt that I am more interested in a “linear” record in the junction table which summarizes the Members, Plan, sale_price, start and end dates.

For my purposes, having a snapshot of the Features at a given moment in time isn’t that crucial on a per Member basis, which is my point.

The way I modeled things, you could get that if you really need it, but I don’t think you would.

Fair enough.

Just a convention I have adopted so that every table has a unique numeric ID that you can easily reference in additional to traditional pk/fk combos.

Using my terms above, OFFER.offer_price would keep a snapshot of the price on a per MEMBER basis, but that does not guarantee it keeps track of the entire price history.

Without the PRICE_PLAN_HISTORY table, if Fred bought a plan for $10 in January, that is captured in OFFER.offer_price, but then if the price increases to $20 in Feb and no one buys anything, then in March when I increase the price to $30, then MEMBERSHIP_PLAN.unit_price won’t retain the $20, it will show $30 so you lose sight that the price went from $10 n Jan to $20 in Feb to $30 in Mar.

See?

12

I see, i’m just not sure I understand the logic of why you care, if noone bought it…

13

Those tables were just after-thoughts. Maybe they don’t matter.

Eitehr way, I’m much more interested in the main tables, and how I tweaked things from what @Martyr2 suggested.

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.