Jump to content

Handling Plan Versions


SaranacLake

Recommended Posts

I am trying to model "Plans" and "Entitlements" for my website, and could use some help on adding "Versions"...

For my website, "Members" can purchase various "Plans" which in turn give them different levels of access.  And now I want a way to track how a "Plan" might evolve over time.

Here is what I have so far...

PLAN -||------|<- PLAN_VERSION -||------|<- ENTITLEMENT ->-------||- WEBPAGE

 

PLAN

- Silver

- Gold

- Platinum

 

PLAN_VERSION

- Gold, 1/1/2019, 12/31/2019, v1

- Gold, 1/1/2020, 6/30/2020, v2

- Gold, 7/1/2020, 9/30/2020, v3

- Gold, 10/1/2020, 12/31/2020, v4

 

ENTITLEMENT

Gold v1 Feature-01

Gold v1 Feature-02

Gold v2 Feature-01

Gold v2 Feature-02

Gold v2 Feature-03

Gold v2 Feature-04

**I added the "version" above to show what I am trying to do, but as you can see from my question below, I would actually use that for the key, and would prefer using a Start and/or End-Date.

 

Questions:

1.) What should I use for my Primary Key in the PLAN_VERSION table?

Plan-Name + Plan-Start-Date ?

Plan-Name + Plan-Start-Date + Plan-End-Date ?

Something else?

 

2.) What can I do to prevent the Plan-Start-Date and Plan-End-Date from getting mixed up between different records in PLAN_VERSION?

For example, you wouldn't want to allow this...

- Gold, 1/1/2019, 12/31/2019, v1

- Gold, 12/5/2019, 3/1/2020, v2

- Gold, 1/1/2020, 9/30/2020, v3

 

3.) Any other suggestions on setting this up?

 

Thanks!

 

Edited by SaranacLake
Link to comment
Share on other sites

You are definitely on the right track.

TABLE: plan
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| plan_id   | int(11)     | NO   | PRI |         |       |
| plan_name | varchar(45) | YES  |     |         |       |
+-----------+-------------+------+-----+---------+-------+

TABLE: plan_version
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| plan_id     | int(11) | NO   | PRI |         |       |
| version_no  | int(11) | NO   | PRI |         |       |
| valid_from  | date    | YES  |     |         |       |
| valid_until | date    | YES  |     |         |       |
+-------------+---------+------+-----+---------+-------+

TABLE: entitlement
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| plan_id    | int(11) | NO   | PRI |         |       |
| version_no | int(11) | NO   | PRI |         |       |
| feature_id | int(11) | NO   | PRI |         |       |
+------------+---------+------+-----+---------+-------+

TABLE: feature
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| feature_id | int(11)      | NO   | PRI |         |       |
| title      | varchar(45)  | YES  |     |         |       |
| page       | varchar(100) | YES  |     |         |       |
+------------+--------------+------+-----+---------+-------+

Suppose the data is...

TABLE: plan                                                        TABLE: entitlement
+---------+-----------+                                            +---------+------------+------------+
| plan_id | plan_name |                                            | plan_id | version_no | feature_id |
+---------+-----------+                                            +---------+------------+------------+
| 1       | Silver    |                                            | 1       | 1          | 1          |
| 2       | Gold      |                                            | 1       | 1          | 2          |
| 3       | Platinum  |                                            | 1       | 1          | 3          |
+---------+-----------+                                            | 1       | 2          | 1          |
                                                                   | 1       | 2          | 2          |
TABLE: plan_version                                                | 1       | 2          | 3          |
+---------+------------+------------+-------------+                | 1       | 2          | 4          |
| plan_id | version_no | valid_from | valid_until |                | 2       | 1          | 1          |
+---------+------------+------------+-------------+                | 2       | 1          | 2          |
| 1       | 1          | 2019-06-01 | 2019-12-31  |                | 2       | 1          | 3          |
| 1       | 2          | 2020-01-01 | 9999-12-31  |                | 2       | 1          | 4          |
| 2       | 1          | 2019-06-01 | 2019-10-31  |                | 2       | 1          | 5          |
| 2       | 2          | 2019-11-01 | 2020-02-29  |                | 2       | 1          | 6          |
| 2       | 3          | 2020-03-01 | 9999-12-31  |                | 2       | 2          | 1          |
| 3       | 1          | 2019-12-01 | 9999-12-31  |                | 2       | 2          | 2          |
+---------+------------+------------+-------------+                | 2       | 2          | 3          |
                                                                   | 2       | 2          | 4          |
TABLE: feature                                                     | 2       | 2          | 5          |
+------------+-------+------------+                                | 2       | 2          | 6          |
| feature_id | title | page       |                                | 2       | 2          | 7          |
+------------+-------+------------+                                | 2       | 3          | 1          |
| 1          | AAA   | Page1.php  |                                | 2       | 3          | 2          |
| 2          | BBB   | Page2.php  |                                | 2       | 3          | 3          |
| 3          | CCC   | Page3.php  |                                | 2       | 3          | 4          |
| 4          | DDD   | Page4.php  |                                | 2       | 3          | 5          |
| 5          | EEE   | Page5.php  |                                | 2       | 3          | 6          |
| 6          | FFF   | Page6.php  |                                | 2       | 3          | 7          |
| 7          | GGG   | Page7.php  |                                | 2       | 3          | 8          |
| 8          | HHH   | Page8.php  |                                | 3       | 1          | 1          |
| 9          | JJJ   | Page9.php  |                                | 3       | 1          | 2          |
| 10         | KKK   | Page10.php |                                | 3       | 1          | 3          |
+------------+-------+------------+                                | 3       | 1          | 4          |
                                                                   | 3       | 1          | 5          |
                                                                   | 3       | 1          | 6          |
                                                                   | 3       | 1          | 7          |
                                                                   | 3       | 1          | 8          |
                                                                   | 3       | 1          | 9          |
                                                                   | 3       | 1          | 10         |
                                                                   +---------+------------+------------+

to summarize...

SELECT p.plan_name
     , v.version_no
     , DATE_FORMAT(v.valid_from, '%m/%d/%Y') as `From`
     , DATE_FORMAT(v.valid_until, '%m/%d/%Y') as `Until`
     , GROUP_CONCAT(f.title ORDER BY title SEPARATOR ', ') as features
FROM plan p 
       JOIN
     plan_version v USING (plan_id)
       JOIN
     entitlement e USING (plan_id, version_no)
       JOIN
     feature f USING (feature_id)
GROUP BY plan_id, version_no;

+-----------+------------+------------+------------+--------------------------------------------------+
| plan_name | version_no | From       | Until      | features                                         |
+-----------+------------+------------+------------+--------------------------------------------------+
| Silver    |          1 | 06/01/2019 | 12/31/2019 | AAA, BBB, CCC                                    |
| Silver    |          2 | 01/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD                               |
| Gold      |          1 | 06/01/2019 | 10/31/2019 | AAA, BBB, CCC, DDD, EEE, FFF                     |
| Gold      |          2 | 11/01/2019 | 02/29/2020 | AAA, BBB, CCC, DDD, EEE, FFF, GGG                |
| Gold      |          3 | 03/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH           |
| Platinum  |          1 | 12/01/2019 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, JJJ, KKK |
+-----------+------------+------------+------------+--------------------------------------------------+ 

to get the ones in effect on a particular date, add a where clause to the above query

... WHERE CURDATE() BETWEEN v.valid_from AND v.valid_until ...

+-----------+------------+------------+------------+--------------------------------------------------+
| plan_name | version_no | From       | Until      | features                                         |
+-----------+------------+------------+------------+--------------------------------------------------+
| Silver    |          2 | 01/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD                               |
| Gold      |          2 | 11/01/2019 | 02/29/2020 | AAA, BBB, CCC, DDD, EEE, FFF, GGG                |
| Platinum  |          1 | 12/01/2019 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, JJJ, KKK |
+-----------+------------+------------+------------+--------------------------------------------------+

 

** ** **

Now to address your question about preventing date overlaps.

Suppose that on March 1st you are adding a new feature 'LLL/page11.php' for Platinum plans and you want to introduce a new version.

  • Add new feature LLL
  • Select Platinum v1 record for editing
  • On the edit form, change date_from to "2020-03-01" and add LLL to feature list.
  • However, when you update the version table,
    • leave the "from" date as it is and change the "until" date to the date before the new one (2020-02-29), save
    • insert a new record for Platinum / v2 / 2020-03-01 / 9999-12-31
    • insert entitlement records for new version

 

  • Like 1
Link to comment
Share on other sites

@Barand,

You never cease to amaze!!

First off, how in the world do you get such nicely formatted (1970s) tables here in the forum???

It looks like you agreed with my high-level design, and from what I read, you were just nice enough to flesh out the columns and keys for me - thanks!

Now for some follow-up questions...

Q1.) I had envisioned using dates to form the primary key for PLAN_VERSION, but it looks like you decided to disregard intrinsic values and just go for an AutoNumber?

What are the pros and cons of your approach to what I had envisioned?

One benefit to my approach would be ensuring that dates made sense and weren't Nulls.

 

Q2.) What are my options with MySQL to ensure that dates entered are logical?

Let's say I have this real data...

- Gold, 1/1/2019, 12/31/2019, v1

- Gold, 1/1/2020, 2/29/2020, v2

- Gold, 3/1/2020, 9/30/2020, v3

 

Q2a.) Can I create a constraint in MySQL that says, "End_Date must be greater than Start_Date"?

 

Q2b.) Can I create a constraint in MySQL that says, "For a new record added, the new record Start_Date must be greater than the most most recent record's End_Date"?

 

Having such logic in my table would ensure that each PLAN_VERSION record has logical dates.

 

Q3.) Is there a problem having a PLAN -||------|< PLAN_VERSION design?

I want it so I can keep track of how things evolve as I add or remove features.

 

Q4.) Related to #3, would this design cause problems for MEMBERS as a PLAN evolves?

My thinking is this...

If I add a new FEATURE to my website (e.g. webpage, functionality) within a given PLAN, then I presumably want to offer any new Features to all Members of that plan, right?

If you say, "Yes", then that would mean that if a new PLAN_VERSION comes out, then I'd have to update every MEMBER record to reflect going from say "Gold v1" to "Gold v2", correct?

 

By contrast, for PRICING, I am thinking of offering "grandfathering", and if a person renews their membership, then I will keep them at the same price (e.g. $50/year), but for new people signing up, they will have to pay the inflated price (e.g. $55/year).

 

But back to my earlier question, it seems like while increasing prices on new members makes sense, making sure everyone gets the latest PLAN_VERSION also seems the way to go both from a fairness standpoint, but also from a maintenance standpoint.

Agree or disagree?  (This of course would impact my data model!)

 

Thanks!

 

 

Link to comment
Share on other sites

47 minutes ago, SaranacLake said:

Q1.) I had envisioned using dates to form the primary key for PLAN_VERSION, but it looks like you decided to disregard intrinsic values and just go for an AutoNumber?

I prefer to use ids as foreign keys and not data attribute values (such as dates, names etc)

 

48 minutes ago, SaranacLake said:

Having such logic in my table would ensure that each PLAN_VERSION record has logical dates.

So does NOT NULL

 

49 minutes ago, SaranacLake said:

Q2a.) Can I create a constraint in MySQL that says, "End_Date must be greater than Start_Date"?

Q2b.) Can I create a constraint in MySQL that says, "For a new record added, the new record Start_Date must be greater than the most most recent record's End_Date"?

You would validate end > start on processing the input. The method I proposed for entering new versions would ensure contiguous logical date sequences.

 

51 minutes ago, SaranacLake said:

I want it so I can keep track of how things evolve as I add or remove features.

The valid_from / valid_until dates give you a history. (see my data and query examples)

 

55 minutes ago, SaranacLake said:

If you say, "Yes", then that would mean that if a new PLAN_VERSION comes out, then I'd have to update every MEMBER record to reflect going from say "Gold v1" to "Gold v2", correct?

That is up to you. If you assign a member to a particular plan/version then you get that problem. Alternatively assign them to a plan and the use the "current" version.

 

57 minutes ago, SaranacLake said:

By contrast, for PRICING, I am thinking of offering "grandfathering", and if a person renews their membership, then I will keep them at the same price (e.g. $50/year), but for new people signing up, they will have to pay the inflated price (e.g. $55/year).

Prices for each plan would also have valid_from / valid_to dates.

For new members, get the current price. (SELECT price FROM plan_price WHERE CURDATE() BETWEEN valid_from and valid_to)

For renewals (grandfathering) get the price that applied on the date_joined (SELECT price FROM plan_price WHERE date_joined BETWEEN valid_from and valid_to)

Link to comment
Share on other sites

@Barand,

 

12 minutes ago, Barand said:

I prefer to use ids as foreign keys and not data attribute values (such as dates, names etc)

Why is that?

 

12 minutes ago, Barand said:

So does NOT NULL

All NOT NULL does is ensure you have a date value, it doesn't ensure that overlapping dates will not occur.

 

12 minutes ago, Barand said:

You would validate end > start on processing the input. The method I proposed for entering new versions would ensure contiguous logical date sequences.

But is there a way in MySQL to add a "constraint" to prohibit invalid dates?  (Last I knew, MySQL did not offer Constraints, but I thought they were going to be introduced at some point.)

Or could I create a Trigger that checked to make sure End_Date > Start_Date and a second trigger to make sure Start_Date > End_Date on the previous record?

 

 

12 minutes ago, Barand said:

The valid_from / valid_until dates give you a history. (see my data and query examples)

Right, and I was asking f you think that is a good idea.  (To me, having a history of something is why databases exist?!)

 

12 minutes ago, Barand said:

That is up to you. If you assign a member to a particular plan/version then you get that problem. Alternatively assign them to a plan and the use the "current" version.

That is what i was thinking.

So I was thinking of...

MEMBER >|-------||- MEMBER_PLAN -||-------|< PLAN_VERSIONS -||-------|< ENTITLEMENT >|------||- FEATURE

And, as you were saying, when I need to determine which Features/Webpages to display for a given MEMBER, I would just query the latest PLAN_VERSION for whichever PLAN they were assigned, correct?

 

12 minutes ago, Barand said:

Prices for each plan would also have valid_from / valid_to dates.

If I allow grandfathering on prices, then I would want to leave End_Date either blank or make it some insanely high value like 9999-12-31, correct?

Actually I am thinking it might be harder than that, since I am dealing with subscriptions that need to be renewed, so I think I need to consider that more as I get back into finishing a design for subscription...

 

12 minutes ago, Barand said:

For new members, get the current price. (SELECT price FROM plan_price WHERE CURDATE() BETWEEN valid_from and valid_to)

For renewals (grandfathering) get the price that applied on the date_joined (SELECT price FROM plan_price WHERE date_joined BETWEEN valid_from and valid_to)

 

Link to comment
Share on other sites

18 minutes ago, SaranacLake said:

Why is that?

That's what ids are for (row identification)

10 minutes ago, SaranacLake said:

But is there a way in MySQL to add a "constraint" to prohibit invalid dates?  (Last I knew, MySQL did not offer Constraints, but I thought they were going to be introduced at some point.)

"Some point" is version 8. https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

Quote

Right, and I was asking f you think that is a good idea.  (To me, having a history of something is why databases exist?!)

Yes, I agree.

 

 

Link to comment
Share on other sites

@Barand,

Asked earlier...

 

Q1.) So I was thinking of...

MEMBER >|-------||- MEMBER_PLAN -||-------|< PLAN_VERSIONS -||-------|< ENTITLEMENT >|------||- FEATURE

And, as you were saying, when I need to determine which Features/Webpages to display for a given MEMBER, I would just query the latest PLAN_VERSION for whichever PLAN they were assigned, correct?

 

Q2.) If I allow grandfathering on prices, then I would want to leave End_Date either blank or make it some insanely high value like 9999-12-31, correct?

Actually I am thinking it might be harder than that, since I am dealing with subscriptions that need to be renewed, so I think I need to consider that more as I get back into finishing a design for subscription...

 

Q3.) What are your thoughts on MySQL 8's check constraints?

Are they very mature compared to other databases like SQL Server and Oracle?

 

Q4.) Is MySQL v8.0 out yet?  And if so, is it stable?

 

Q5.) What database object/component/whatever would I need to ensure that when adding a new record that the Start-Date > End_date of the previous record?

I'm think that would require a Trigger and some coding...

 

 

Edited by SaranacLake
Link to comment
Share on other sites

1 hour ago, Barand said:

I prefer to use ids as foreign keys and not data attribute values (such as dates, names etc)

Would it be a sin to use actual dates instead?

 

1 hour ago, Barand said:

The method I proposed for entering new versions would ensure contiguous logical date sequences.

Except that relies on a human not making an error.

 

 

Link to comment
Share on other sites

Just now, Barand said:

Your soul would be in mortal peril.

A-ha.  So I guess you would support my general requirement of every table starting with an AutoNumber ID column, eh?

 

Just now, Barand said:

As with so many things

But if you could automate things and let the database check integrity for you, why not?

(Just checked with my website host and cPanel is stuck on MySQL 5.7 so I'll have to wait on true Check constraints.)

 

 

Link to comment
Share on other sites

9 minutes ago, SaranacLake said:

A-ha.  So I guess you would support my general requirement of every table starting with an AutoNumber ID column, eh?

No, but every table should have a unique primary key, but not necessarily auto_incremented (for example the plan_version table above, where the primary key is the (plan_id, version_no) combination)

Link to comment
Share on other sites

Just now, Barand said:

No, but every table should have a unique primary key, but not necessarily auto_incremented (for example the plan_version table above, where the primary key is the (plan_id, version_no) combination)

I forgot that question...

So historically, I would do...

PLAN

- id (PK)

 

PLAN_VERSION

- id (PK)

- plan_id (FK)

 

I guess you prefer concatenating keys on child tables?

 

Link to comment
Share on other sites

4 hours ago, SaranacLake said:

First off, how in the world do you get such nicely formatted (1970s) tables here in the forum???

I have a wonderful rectangular device on my desk. When i press on the area marked "+", a "+" appears on the screen. Similarly, pressing "-" area gives a "-". Works with letters, the long narrow area at the bottom and numbers too. You should get one.

Use a monospace font like courier. Use spaces, never tabs, and paste into a code area in the forum so you get a monospaced font.

For query output you can use the mysql command line interface

image.png.c55e87c8cc47cdbb0331c0cdf64ccfd4.png

Edited by Barand
  • Like 1
Link to comment
Share on other sites

@Barand,

I have to say that this one of the prettiest tables that I've seen!  👍

    +-----------+------------+------------+------------+--------------------------------------------------+ 
    | plan_name | version_no | From       | Until      | features                                         |
    +-----------+------------+------------+------------+--------------------------------------------------+ 
    | Silver    | 1          | 06/01/2019 | 12/31/2019 | AAA, BBB, CCC                                    | 
    | Silver    | 2          | 01/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD                               | 
    | Gold      | 1          | 06/01/2019 | 10/31/2019 | AAA, BBB, CCC, DDD, EEE, FFF                     | 
    | Gold      | 2          | 11/01/2019 | 02/29/2020 | AAA, BBB, CCC, DDD, EEE, FFF, GGG                | 
    | Gold      | 3          | 03/01/2020 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH           | 
    | Platinum  | 1          | 12/01/2019 | 12/31/9999 | AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, JJJ, KKK | 
    +-----------+------------+------------+------------+--------------------------------------------------+
	

 

Edited by SaranacLake
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.