SaranacLake Posted February 17, 2020 Share Posted February 17, 2020 (edited) 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 February 17, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/ Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 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 1 Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574385 Share on other sites More sharing options...
SaranacLake Posted February 17, 2020 Author Share Posted February 17, 2020 @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! Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574418 Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 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) Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574422 Share on other sites More sharing options...
SaranacLake Posted February 17, 2020 Author Share Posted February 17, 2020 @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) Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574423 Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574427 Share on other sites More sharing options...
SaranacLake Posted February 17, 2020 Author Share Posted February 17, 2020 (edited) @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 February 17, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574430 Share on other sites More sharing options...
SaranacLake Posted February 17, 2020 Author Share Posted February 17, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574432 Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 14 minutes ago, SaranacLake said: Would it be a sin to use actual dates instead? Your soul would be in mortal peril. 14 minutes ago, SaranacLake said: Except that relies on a human not making an error. As with so many things Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574433 Share on other sites More sharing options...
SaranacLake Posted February 17, 2020 Author Share Posted February 17, 2020 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.) Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574434 Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 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) Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574436 Share on other sites More sharing options...
SaranacLake Posted February 17, 2020 Author Share Posted February 17, 2020 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? Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574438 Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 39 minutes ago, SaranacLake said: I guess you prefer concatenating keys on child tables? When that combination is required to be unique anyway you may as well make it the primary key instead of an artificial one. Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574440 Share on other sites More sharing options...
SaranacLake Posted February 17, 2020 Author Share Posted February 17, 2020 @Barand Could you respond to my Q1 - Q5 a couple of posts above? Maybe you missed it? (Admins: Would be nice if a given post had a timestamp of # on it so you can reference it!!!) Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574441 Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 Q1 - not necessarily the latest, you could have future plans on file. You want the one that is current. Q2 - I have already answered in previous posts. Only use blank dates if you want to over-complicate the logic in your queries Q3 - Don't know Q4 - Don't know Q5 - Yes, trigger Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574444 Share on other sites More sharing options...
Barand Posted February 17, 2020 Share Posted February 17, 2020 (edited) 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 Edited February 17, 2020 by Barand 1 Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574445 Share on other sites More sharing options...
SaranacLake Posted February 18, 2020 Author Share Posted February 18, 2020 (edited) @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 February 18, 2020 by SaranacLake Quote Link to comment https://forums.phpfreaks.com/topic/310051-handling-plan-versions/#findComment-1574460 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.