I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every month. How should I store this recurring item?
We thought just add in 12 new entries so it atleast covers the year (The users will want to look a few months into the future) but then the problem is that if the cost changes, we have to update all following entries
Our other option was to create another table which would store the key of the original with the dates or day of the month that it needs to repeat on?
Is there a better solution than these?
Best Answer
You shouldn't try to do too many things with one data store.
If you have a table for transactions, then just keep transactions in that table. Don't mix in scheduled future transactions with current transactions. Actual (historical) transactions will have different attributes (columns) than future, scheduled transactions.
Keep the recurring/scheduled transactions (like rent) in a separate table. When the rent is paid, create a new record in the (actual) transaction table.
The scheduled transaction table could have a totally different structure. Instead of keeping a record for each expected transaction, you could keep one record with a date range and recurrence fields. This would save having to update multiple records when the rent price changes, for example. Consider something like this for your scheduled/recurring transaction table:
Scheduled_Transaction:=