Table structure doesn't allow for needed functionality
CURRENT FUNCTIONALITY: In my financial/budgeting app, the current functionality allows a user to add a recurring budget category that will appear in each month's budget. It does this by allowing a user to select a "recurring" option from a dropdown, which makes 2 additional dropdowns appear, one with a beginning month and one with an ending month. When the category is recurring, it writes the beginning month and the ending month to the database (diagram below).
DESIRED FUNCTIONALITY: The problem with the current design is that I want the user to be able to delete one (or more) month from the recurring months. So lets say I set up a recurring category in January for the entire year. Then I get to May and I want that category not to appear for that month. If I delete the category, it currently deletes it for all months, past and present. I need it to have the option to only delete only the desired month(s).
I'm not sure how to accomplish this, and I thought maybe someone has a good idea. Maybe my database structure is off? In the interest of normalization, I didn't want to just add a column for each month or anything like that.
Rather than storing the recurrence formula, IMHO it would be best to store the actual months in a many-to-many relationship:
categories
----------
id
name
months
------
id
category_months
---------------
cat_id
month_id
When a user enters a recurrence formula, it will create the relationships. Then they will be freely able to remove months one at a time if necessary.