Leaderboard

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

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